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.
Optimizer Hints are clause that used in sql queries to force the optimizer to take particular route like using index,nested loop join.
You can find hint name from the view v$sql_hint
Some of commonly used hint
SELECT /*+ ALL_ROWS */ empid, last_name, sal FROM emp;
This hints the optimizer to choose the plan that most effectively returns the resultset at the minimum cost
SELECT /*+ FIRST_ROWS */ * FROM emp;
This hint instructs the optimizer to select a plan that returns the first rows most efficiently..
SELECT /*+ FIRST_20_ROWS */ * FROM emp;
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 hint instructs the optimizer to select a plan that returns the first 100 rows most efficiently..
INDEX(<table_name> < index_name>): Instructs the optimizer to specifically use the named index in determining a plan.
/*+ NO_INDEX((Index name) */
NO_INDEX(<table_name> < index_name>): Instructs the optimizer to specifically not use the named index in determining a plan.
/*+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 join chain that starts with this table.
This hint optimizer to use Nested Loop join the specified tables
This hint optimizer to use Merge join the specified tables
This hint optimizer to use hash join the specified tables
PARALLEL (table n): 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 optimizer
- 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
Related performance Tuning Articles