How to use Optimizer hints

Last updated on November 23rd, 2015 at 09:52 am

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 Optimizer Hint ?
Optimizer Hints are clause that  used in sql queries to force the optimizer to take particular route like using index,nested loop join.Syntax to use hint

/*+ < Hint name>*/

Some of commonly used hint

SELECT /*+ ALL_ROWS */ empid, last_name, sal FROM emp;
SELECT /*+ FIRST_20_ROWS */ * FROM emp;
SELECT /*+ FIRST_ROWS(100) */ empid, last_name, sal FROM emp;

/*+INDEX(Index name)*/
/*+INDEX_JOIN(emp index1, index2)*/ /*+INDEX_COMBINE(emp bitmap1, bitmap2)*/ /*+AND_EQUAL(emp index1, index2,…)*/
/*+ dynamic_sampling(A 3) */
USE_NL(table table)
USE_MERGE(table table)
USE_HASH(table table)

Leave a Reply