Oracle Optimizer Hints process

                                       A hint is an instruction to the optimizer. When writing SQL, you may know information about the data unknown to the optimizer. Hints enable you to make decisions normally made by the optimizer, sometimes causing the optimizer to select a plan that it sees as higher cost.
                                         In a test or development environments, hints are useful for testing the performance of a specific access path. For example, you may know that a certain index is more selective for certain queries. In this case, you may use hints to instruct the optimizer to use a better execution plan.
                                        The disadvantage of hints is the extra code that must be managed, checked, and controlled. Changes in the database and host environment can make hints obsolete or even have negative consequences. For this reason, test by means of hints, but use other techniques to manage the SQL execution plans, such as SQL Tuning advisor and SQL Plan Baselines.
                                           Oracle Database supports more than 60 hints, each of which may have zero or more parameters. A statement block can have only one comment containing hints, and that comment must follow the SELECT, UPDATE, INSERT, MERGE, or DELETE keyword. For example, the following hint directs the optimizer to pick the query plan that produces the first 10 rows from the employees table at the lowest cost:

SELECT /*+ optimizer_features_enable('11.1.0.6') */ employee_id, last_name
FROM    employees
ORDER BY employee_id;

Types of Hints

Hints can be of the following general types:

• Single-table
  Single-table hints are specified on one table or view. INDEX and USE_NL are examples of single-table hints.
  
• Multi-table
  Multi-table hints are like single-table hints, except that the hint can specify one or more tables or views. LEADING is an example of a multi-table hint. Note that USE_NL(table1 table2) is not considered a multi-table hint because it is a shortcut for USE_NL(table1) and USE_NL(table2).
  
• Query block
  Query block hints operate on single query blocks. STAR_TRANSFORMATION and UNNEST are examples of query block hints.
  
• Statement
  Statement hints apply to the entire SQL statement. ALL_ROWS is an example of a statement hint.
  

Step 1 :
Open the TOAD and go to




Step 2:

Go to Code Snippets and click automatically open the window  like this









Select the SQL Optimizer Hint






 List of SQL Optimizer Hint