[Untitled]‎ > ‎

SQL Hint Docs


Oracle

LIST OF HINTS  
starting in 11g there is a view V$SQL_HINT with the hints listed. There are a lot of hints but only a few are commonly used in manual SQL tuning.
Charles Hooper has queried V$SQL_HINT to give you the full list
Popular Hints (from feedback to poll asked on Oracle-L) 
Undocumented Hints Blog entry by Jonathan Lewis: 
http://jonathanlewis.wordpress.com/2009/06/14/undocumented-hints/
          Hints used in DB Optimizer

DYNAMIC_SAMPLING
DYNAMIC_SAMPLING(4) will cause the optimizer to look for correlated columns if there are multiple columns in predicate filters on a table
Can also be used on a specific table in a query

/*+ dynamic_sampling(table 4) */ 

This can be achieved in 11g by using extended statistics

dbms_stats.create_extended_stats(ownname=>  ‘owner’,
                                 tabname => ‘table’,
                                 extension => ‘(field1,field2)’)

see Troubleshooting Oracle Performance p131

FULL


GATHER_PLAN_STATISTICS
Starting in 10g this hint will collect extended row source statistics available in v$SQL_PLAN_STATISTICS(_ALL)

INDEX / NO_INDEX
INDEX(table_alias index_name)

INDEX_COMBINE

from Jonathan Lewis : 

depends on _b_tree_bitmap_plans being set to true (the default from 9i).

 Used for bitmap operators (although you can reference  b-tree indexes in the hint to indicate to Oracle that the  index is a candidate for 'rowid conversion to bitmap').     Oracle acquires bitmaps from each index, and uses the     AND, OR, NOT, or MERGE operators to produce     a result bit string that can then be converted into rowids     for visiting the table.

 

INDEX_JOIN
SELECT department_id
FROM employees e
WHERE manager_id < 110
AND department_id < 50;

---------------------------------------------------------------------
| Id | Operation                    | Name              | Cost(%CPU)|
---------------------------------------------------------------------
|  0 | SELECT STATEMENT             |                   |    2  (0) |
|* 1 |  TABLE ACCESS BY INDEX ROWID | EMPLOYEES         |    2  (0) |
|* 2 |   INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |    1  (0) |
---------------------------------------------------------------------

SELECT /*+ INDEX_JOIN(e emp_manager_ix emp_department_ix) */ department_id
FROM employees e
WHERE manager_id < 110
AND department_id < 50;

-------------------------------------------------------------
| Id | Operation           | Name              | Cost(%CPU) |
-------------------------------------------------------------
|  0 | SELECT STATEMENT    |                   |    3 (34)  |
|* 1 |  VIEW               | index$_join$_001  |    3 (34)  |
|* 2 |   HASH JOIN         |                   |            |
|* 3 |    INDEX RANGE SCAN | EMP_DEPARTMENT_IX |    2 (50)  |
|* 4 |    INDEX RANGE SCAN | EMP_MANAGER_IX    |    2 (50)  |
-------------------------------------------------------------

INDEX_RS / NO_INDEX_RS
index range scan

INDEX_SS / NO_INDEX_SS
index skip scan

JOIN_ELIMINATION / NO_JOIN_ELIMINATION
simply put if Oracle can avoid a join say by using the implications of a FK PK in a query that only returns data from one of the two then we can skip the join

LEADING(table_alias)  9i
LEADING(table_alias, table_alias, ...) 10g
         9i - the table to start the join order with
         10g - can give  list of tables and the optimizer follows that order


SELECT /*+
        leading (t1, t3, t2, t4)
        use_nl(t3)
        use_hash(t2) swap_join_inputs(t2)
        use_nl(t4) */
...
FROM    tab1, tab2, tab3, tab4
WHERE
        tab1.id = tab3.id
AND     tab3.id = tab2.id
AND     tab2.id = tab4.id
AND     {filter clauses};

        force the optimizer to first join TAB1 and TAB3 with a nested loop, then join this result to TAB2 using a hash, then join to TAB4 with nested loops

The leading() hint allows you to specify the join order, and the use_nl(t3) ensures we get a nested loop from t1 to t3. We then have to join to t2 because of the leading() hint, but the  swap_join_inputs() would make t2 the build (first) table and the intermediate result the probe (second) table in the hash join. Finally we specify a nested loop for the join to t4. (For 9i, just change the leading() hint to an ordered() hint, in this case and swap the order of t2 and t3 in the from clause))

But even in 10g there are probably cases where you would have  to do things like introduce partial order through in-line views with no_merge hints, or subquery factoring with materialize hints to get the plan you want.

Unfortunately, one of the difficulties of exercising total control is that Oracle’s optimizer works very hard to produce “left-deep” trees; and sometimes we know that the best plan comes from a “bushy” tree – and it can be hard to produce a legal execution plan which generates partial results and still joins those results efficiently.

END JONATHAN

What Jonathan means by BUSHY is that Oracle does not execute the join of 2 or more tables and  then join that result to 2 other tables (or more) to achieve that affect you have that with Common Table Expressions (CTE) known as Subquery Factoring in Oracle (ie the "with" clause)

Oracle only joins one table table at a time to the current result set. In order to make Oracle join the results sets from table A and B to the result set from tables C and D, you have to use subqueries and the NO_MERGE hint. Here is an example

http://sites.google.com/site/embtdbo/sql-tuning-1#TOC-Query-3

BUSHY explanation from Jonathan at : http://jonathanlewis.wordpress.com/2007/01/24/left-deep-trees

MATERIALIZE
AFAIK used in Common Table Expressions (CTE) ie the "with" clause to force Oracle to materialize, or  execute, the subquery and not try and merge it into the main query.

MERGE / NO_MERGE
NO_MERGE - Don't try to merge subqueries and views but execute them as is. Example

SELECT * FROM 
(
   SELECT /*+  NO_MERGE */ b.apples, b.oranges, a.harvest_size
   FROM a,   b
   WHERE
      a.planted_date = TO_DATE ('02/10/2008', 'dd/mm/yyyy') AND
      a.pears = b.pears AND
) X,
    SELECT /*+  NO_MERGE */ d.apples, d.oranges, d.harvest_size
    FROM  d,  c
    WHERE
    d.planted_date = TO_DATE ('02/10/2008', 'dd/mm/yyyy') AND
    c.apples = d.apples AND
    c.pears = 'D' AND
) Y
WHERE
X.harvest_size = Y.harvest_size;

Force Oracle to JOIN  A and B  together into result set X
                        JOIN C and D  together into result set Y
then join X and Y
Otherwise Oracle would have merged the subqueries and only joined one table at  a time

MONITOR / NO_MONITOR
check v$sql_monitor and v$sql_plan_monitor in 11g

NLJ_BATCHING

NO_QUERY_TRANSFORMATION
NO UNNEST, PUSH_SUBQ, MERGE or EXPAND (expands or lists ) 
Randolf Geist says:
use the "NO_QUERY_TRANSFORMATION" hint to disable most of the transformations ... although some transformations still seem to take place, e.g. IN seems to be always transformed into EXISTS

OPT_PARAM
set parameters
OPT_PARAM('optimizer_index_cost_adj' value)
OPT_PARAM('optimizer_secure_view_merging', 'value')
OPT_PARAM('start_transformation_enabled','value') 
opt_param('_optimizer_join_sel_sanity_check', 'false') -- http://oracle-randolf.blogspot.com/2009/10/multi-column-joins.html
 
OPTIMIZER_FEATURES_ENABLE(version)
get versions from

select value from v$parameter where name='optimizer_features_enable';

or (works on versions before 10g)

Alter session set optimizer_features_enable= 'dummy'

outputs a list of valid versions (see Troubleshooting Oracle Performance p175)

example

/*+optimizer_features_enable('9.2.0') */

ORDERED
         Follow the order of tables in the from clause, not needed starting in 10g because leading will accept more than one table

PUSH_PRED / NO_PUSH_PRED
push a predicate in outer query to subquery or view ( or NOT do  it)

PUSH_SUBQ / NO_PUSH_SUBQ
Execute the subquery early (or not with NO)
IN 10g, hint goes in subquery, before 10g it went into the main query

QB_NAME 
give the subqueries names - great for tracking the output in V$SQL_PLAN 
especially useful if the same table gets used in multiple spots in the query
I think this name can also be used in LEADING hint to make a subquery run in a certain order

SWAP_JOIN_INPUTS
useful for making HASH or NESTED LOOPS work in the order you want
more info 
http://dioncho.wordpress.com/2009/03/03/right-join-the-secret-of-swapping-join-input/

UNNEST / NO_UNNEST
for semi-joins and anti-joins (see Troubleshooting Oracle Performance p449 )
UNNEST - try to unnest the semi-join or anti-join subqueries
NO_UNNEST - don't mess with the semi-join or anti-join subqueries 

Manual unnest, take
from (http://www.jlcomp.demon.co.uk/hinted_sql.html with discussion of MERGE and  scalar subquery caching)

select
       outer.*
from   emp outer
where  outer.sal > (
              select avg(inner.sal)
              from   emp inner
              where  inner.dept_no = outer.dept_no
       );

change to

select
       outer.*
from   emp outer
       (
              select dept_no, avg(inner.sal) avg_sal
              from   emp
              group by
dept_no
       )      inner
Where
       outer.dept_no = inner.dept_no
and    outer.sal > inner.avg_sal;

USE_HASH(table_alias)

Awesome example of why hash joins are good and how they work: 
http://jonathanlewis.wordpress.com/2010/08/10/joins-hj/

Select /*+ leading (A) use_hash(B) */  * from A,B where A.f1=B.f1

Select /*+ ordered use_hash (B) */ * from A,B where A.f1=B.f1

hash A  loop up B in A's Hash
First table is the BUILD table and second is the PROBE

Tanel:
_optimizer_transitivity_retain parameter, which changed to true in 10.2.0.1.
MERGE JOIN CARTESIAN, the join is really just a NESTED LOOP join as cartesiam merge join is internally implemented as nested loop join without filtering
Jonathan:
important difference between the (Cartesian) Merge and the Nested Loop – the second data set is always copied into a local buffer by the “buffer sort” operation before the “nested loop” bit begins.

USE_MERGE
never used this but here is good link by Jonathan: http://jonathanlewis.wordpress.com/2010/08/15/joins-mj/

USE_NL(table_alias)

Select /*+ leading(A) use_nl(B) */ * from A,B where A.f1=B.f1

Select /*+ ordered use_nl(B) */ * from A,B where A.f1=B.f1

Addendum
Jonathan Lewis blogs on Hints
http://jonathanlewis.wordpress.com/2007/06/17/hints-again
http://jonathanlewis.wordpress.com/2007/02/21/ignoring-hints
http://jonathanlewis.wordpress.com/2006/10/28/taking-a-hint
create table t1 ( id number, n1 number, n2 number); -- ORDER LINES
create unique index t1_pk on t1(id);
create table t2 ( id number, n1 number, n2 number); -- delivery notes
create unique index t2_pk on t2(id);
create table t3 ( id number, n1 number, n2 number); --  ORDERS
create unique index t3_pk on t3(id);
create table t4 ( id number, n1 number, n2 number);
create unique index t4_pk on t4(id);

Other stuff
         http://www.oracle-developer.net/display.php?id=427 - cardinality on pipelined fuctions;
Subquery rewrites
MERGE / NO_MERGE
NO_QUERY_TRANSFORMATION
PUSH_PRED / NO_PUSH_PRED
PUSH_SUBQ / NO_PUSH_SUBQ
UNNEST / NO_UNNEST