Hints

Real World Scenario:

1.  Direct select from database tables performed in 8sec,while linked select(I mean select from linked database) needed much more time to be performed...The problem was that first select was using nested loop and another one hash join (execution plan can be seen by pressing F5).For solving this problem there exists hint for select,in this case the hint is to use not hash join but nested loop,syntax is the following: /*use_nl(table1,table2,table3...)*/

select /*+use_nl(table1,table2,table3...)*/ .........
from table1,table2,table3........



2. Hints to use "indx_name" index. on "table1 " table.
select /*+index(table1 indx_name) ...*/ .........
from table1,table2,table3........


3. The ordered hint requests that the tables should be joined in the order that they are specified in the from clause
select /*+ ordered */ .........
from table1,table2,table3........


4.The rule cancels any other hints specified for this statement.
select /*+ rule */ .........
from table1,table2,table3........


5.The full(table) tells the optimizer to do a full scan of the specified table.
select /*+ full(table1)*/ .........
from table1,table2,table3........


6.cache specifies that the blocks retrieved for the table in the hint are placed at the most recently used end of the LRU list when the table is full table scanned.
select /*+ cache */ .........

from table1,table2,table3........

7.no_cache specifies that the blocks retrieved for the table in the hint are placed at the least recently used end of the LRU list when the table is full table scanned.

select /*+ no_cache*/ .........

from table1,table2,table3........

8.first_rows use the cost based approach for best response time.

select /*+ first_rows */ .........

from table1,table2,table3........

9.all_rows use the cost based approach for best throughput.

select /*+ all_rows */ .........

from table1,table2,table3........


10.append , noappend Specifies that data is simply appended (or not) to a table; existing free space is not used. Use these hints only following the INSERT keyword.

select /*+ append*/ .........

from table1,table2,table3.......