What is Optimizer?
- When a query is submitted in Oracle server. optimizer attempts to generate the best execution plan for a SQL statement. The best execution plan is defined as the plan with the lowest cost among all considered candidate plans. The cost computation accounts for factors of query execution such as I/O, CPU, and communication.
- The best method of execution depends on myriad conditions including how the query is written, the size of the data set, the layout of the data, and which access structures exist. The optimizer determines the best plan for a SQL statement by examining multiple access methods, such as full table scan or index scans, and different join methods such as nested loops and hash joins
What is a Oracle Optimizer Hint?
Oracle Optimizer Hints are clauses that are used in sql queries to force the optimizer to take a particular route like using the index, nested loop join.
Syntax to use the hint
/*+ <Hint name>*/
You can find hint name from the view v$sql_hint
Some of the commonly used hint
SELECT /*+ ALL_ROWS */ empid, last_name, sal FROM emp; Example SELECT /*+ ALL_ROWS */ employee_id, last_name, salary, job_id FROM emp WHERE employee_id = 12;
This is a statement-level hint. This hints the optimizer to choose the plan that most effectively returns the result set at the minimum cost
SELECT /*+ FIRST_ROWS */ * FROM emp; Example SELECT /*+ FIRST_ROWS(10) */ employee_id, last_name, salary FROM emp WHERE department_id = 50;
This is a statement-level hint. This hint instructs the optimizer to select a plan that returns the first rows most efficiently.
SELECT /*+ FIRST_20_ROWS */ * FROM emp;
This is a statement-level hint. This hint instructs the optimizer to select a plan that returns the first 20 rows most efficiently.
SELECT /*+ FIRST_ROWS(100) */ empid, last_name, sal FROM emp;
This is again a statement-level hint. This hint instructs the optimizer to select a plan that returns the first 100 rows most efficiently.
/*+INDEX(Index name) */
INDEX(<table name>.< index_name>): Instructs the optimizer to specifically use the named index in determining a plan.
This is an access path Hint
/*+ NO_INDEX((Index name) */
NO_INDEX(<table_name> < index_name>): Instructs the optimizer to specifically not use the named index in determining a plan.
Other Index related hints are
/*+INDEX_JOIN(emp index1, index2)*/ /*+INDEX_COMBINE(emp bitmap1, bitmap2)*/ /*+AND_EQUAL(emp index1, index2,…)*/ /*+ dynamic_sampling(A 3) */
/*+ LEADING(table_name)*/
This hint tells Oracle to use the take table as the first in the join order. The optimizer will consequently select a joined chain that starts with this table. This is a join order hint
/*+ USE_NL(table table) */ SELECT /*+ USE_NL(l h) */ h.customer_id, l.unit_price * l.quantity FROM orders h ,order_items l WHERE l.order_id = h.order_id;
This hints optimizer to use Nested Loop join the specified tables. This is join operation hint
/*+ USE_MERGE(table table) */
This hints optimizer to use Merge join the specified tables. This is join operation hint
/*+ USE_HASH(table table) */ SELECT /*+ USE_HASH(l h) */ * FROM orders h, order_items l WHERE l.order_id = h.order_id AND l.order_id > 3500;
This hints optimizer to use hash join the specified tables. This is join operation hint
PARALLEL (table n) SELECT /*+ FULL(a) PARALLEL(a, 2) */ last_name FROM employees a;
This hint tells the optimizer to use n concurrent servers for a parallel operation.
APPEND
This hint instructs the optimizer to carry out a direct-path insert. This may make INSERT … SELECT statements faster because inserted data is simply appended to the end of the table and any referential constraints are ignored.
RULE
This hint basically turns off the cost optimizer
Important points about HINT usage
- It is no recommended to use Hints as such and it should be avoided as with patches and upgrade, hint may generate sub-optimal plan and degrade the performance
- Hint syntax should be used carefully else this will be ignored
- Oracle ignores hints containing syntax errors, but considers other correctly specified hints within the same comment.
Hope you like this article on oracle optimizer hints and it helps you in oracle tuning. Please do provide the feedback
Also Reads
Oracle explain plan
how to enable level 12 trace in oracle
Automatic Workload Repository
https://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF94955
