Passing Hints to the Optimizer

You can pass hints to the optimizer. A hint is an optimizer directive that influences the optimizer s choice of execution plan. The correct hint may improve the performance of a SQL statement. You can check the effectiveness of a hint by comparing the cost in the execution plan of a SQL statement with and without the hint.

Note  

The optimizer will typically pick the best execution plan for you without the use of hints. With the introduction of Oracle10 g and the automatic gathering of statistics, my opinion is that the use of hints will diminish. I ve included this section to show you one useful hint, and to give you a basic introduction to the subject of hints.

In this section, you ll see an example query that uses one of the more useful hints: the FIRST _ROWS( n ) hint. The FIRST_ROWS( n ) hint tells the optimizer to generate an execution plan that will minimize the time taken to return the first n rows in a query. This hint can be useful when you don t want to wait around too long before getting some rows back from your query, but you still want to see all the rows.

For example, the following statement uses FIRST_ROWS(2) in a query that retrieves rows from the customer table; 2 indicates the first two rows are to be returned as soon as possible. Notice that the hint is placed within the strings /*+ and */ :

 EXPLAIN PLAN SET STATEMENT_ID = 'HINT' FOR SELECT /*+ FIRST_ROWS(2) */ p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id = pt. product_type_id; 

Caution  

Your hint must use the exact syntax shown ” otherwise , the hint will be ignored. The syntax is: /*+ followed by one space, the hint, followed by one space, and */ .

There are many hints that you can use, and this section has merely given you a taste of the subject. For a comprehensive list of hints, you can read the Oracle10 g Database Performance Tuning Guide (Oracle Corporation).