SQL Statement Hints – A Summary See Note 35934.1 for CBO issues.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
General:
– Syntax: /*+ HINT HINT … */ (In PLSQL the space between the ‘+’ and
the first letter of the hint is vital as
otherwise the hint may be ignored
so /*+ ALL_ROWS */ is fine
but /*+ALL_ROWS */ will cause problems
)
– Hints always force the use of the cost based optimizer (Except RULE).
– Hints must reference the table alias, if aliases are in use. Eg:
Wrong:
SELECT /*+ FULL ( emp ) */ empno FROM emp myalias WHERE empno > 10;
Correct:
SELECT /*+ FULL ( myalias ) */ empno FROM emp myalias WHERE empno > 10;
– Hints should not reference the schema name.
Eg: SELECT /*+ index(scott.emp emp1) */ … should not be used.
Alias the table name instead and use the alias in the hint.
– Invalid hints cause the hint to be ignored without warning
– Invalid hints may not be immediately obvious.
Eg: FIRST_ROWS as a hint when the statement has an ORDER BY clause.
(since the data has to be ordered prior to the first row being
returned the first_rows hint may not have the desired effect).
– The access path to be HINTed must be an available access path.
For example an index hint referencing a non-existant index with fail
silently.
– If third party tools do not contain the logic to support hints, then
a potential workaround is to embed hints in a view and then reference that
view.
Hint Reference:
– Sub-Queries/views:
PUSH_SUBQ Causes all subqueries in a query block to be
executed at the earliest possible time.
Normally subqueries are executed as the last
is applied is outerjoined or remote or joined
with a merge join. (>=7.2)
NO_MERGE(v) Use this hint in a VIEW to PREVENT it
being merged into the parent query. (>=7.2)
or use NO_MERGE(v) in parent query block
to prevent view V being merged
MERGE(v) Do merge view V
MERGE_AJ(v) } Put hint in a NOT IN subquery to perform sort-merge
HASH_AJ(v) } anti-join or hash anti-join or
NL_AJ(v) } nested loops antijoin (>=7.3)
Eg: SELECT .. WHERE deptno is not null
AND deptno NOT IN
(SELECT /*+ HASH_AJ */ deptno …)
HASH_SJ(v) } Transform EXISTS subquery into HASH or MERGE
MERGE_SJ(v) } or nested loops semi-join to access “v”
NL_SJ(v) }
PUSH_JOIN_PRED(v) Push join predicates into view V
NO_PUSH_JOIN_PRED(v) Do NOT push join predicates
– Access:
FULL(tab) Use FTS on tab
CACHE(tab) If table within
treat as if it had the CACHE option set.
See
applies if FTS used.
NOCACHE(tab) Do not cache table even if it has CACHE option
set. Only relevant for FTS.
ROWID(tab) Access tab by ROWID directly
SELECT /*+ ROWID( table ) */ …
FROM tab WHERE ROWID between ‘&1’ and ‘&2’;
CLUSTER(tab) Use cluster scan to access ‘tab’
HASH(tab) Use hash scan to access ‘tab’
INDEX(tab [ind]) Use ‘ind’ scan to access ‘tab’ – Disables index_ffs
NO_INDEX (tab [ind]) Do no use ‘ind’ to access ‘tab’
INDEX_ASC(tab [ind]) Use ‘ind’ to access ‘tab’ for range scan.
INDEX_DESC(tab {ind]) Use descending index range scan
(Join problems pre 7.3)
INDEX_FFS(tab [ind]) Index fast full scan – rather than FTS.
INDEX_RRS(tab [ind]) Index Rowid Range scan
INDEX_COMBINE( tab i1.. i5 )
Try to use some boolean combination of
bitmap index/s i1,i2 etc
INDEX_SS(tab [ind]) Use ‘ind’ to access ‘tab’ with an
index skip scan
AND_EQUAL(tab i1.. i5 ) Merge scans of 2 to 5 single column indexes.
USE_CONCAT Use concatenation (Union All) for OR (or IN)
statements. (>=7.2). See Note 17214.1
(7.2 requires
NO_EXPAND Do not perform OR-expansion (Ie: Do not use
Concatenation).
DRIVING_SITE(table) Forces query execution to be done at the
site where “table” resides
– Joining:
USE_NL(tab) Use table ‘tab’ as the driving table in a
Nested Loops join. If the driving row source
is a combination of tables name one of the
tables in the inner join and the NL should
drive off the entire row-source.
Does not work unless accompanied by an ORDERED
hint.
USE_MERGE(tab..) Use ‘tab’ as the driving table in a sort-merge
join.
Does not work unless accompanied by an ORDERED
hint.
USE_HASH(tab1 tab2) Join each specified table with another row
source with a hash join. ‘tab1’ is joined to
previous row source using a hash join. (>=7.3)
STAR Force a star query plan if possible. A star
plan has the largest table in the query last
in the join order and joins it with a nested
loops join on a concatenated index. The STAR
hint applies when there are at least 3 tables
and the large table’s concatenated index has
at least 3 columns and there are no conflicting
access or join method hints. (>=7.3)
STAR_TRANSFORMATION Use best plan containing a STAR transformation
(if there is one)
ORDERED Access tables in the order of the FROM clause
LEADING This hint specifies only the driving table. From there
CBO is free to investigate multiple join orders
– Parallel Query Option:
PARALLEL ( table,
Use parallel degree / instances as specified
PARALLEL_INDEX(table, [ index, [ degree [,instances] ] ] )
Parallel range scan for partitioned index
PQ_DISTRIBUTE(tab,out,in) How to distribute rows from tab in a PQ
(out/in may be HASH/NONE/BROADCAST/PARTITION)
NOPARALLEL(table) No parallel on “table”
NO_PARALLEL(table) Starting from 10g this syntax should be used
NOPARALLEL_INDEX(table [,index])
Opposite to PARALLEL_INDEX
NO_PARALLEL_INDEX(table [,index])
Starting from 10g this syntax should be used
– Miscellaneous
APPEND Only valid for INSERT .. SELECT.
Allows INSERT to work like direct load
or to perform parallel insert. See Note 50592.1
NOAPPEND Do not use INSERT APPEND functionality
REWRITE(v1[,v2]) 8.1+ With a view list use eligible materialized view
Without view list use any eligible MV
NOREWRITE 8.1+ Do not rewrite the query
NO_REWRITE Starting from 10g this syntax should be used
NO_UNNEST Add to a subquery to prevent it from being unnested
UNNEST Unnests specified subquery block if possible
SWAP_JOIN_INPUTS Allows the user to switch the inputs of a join.
See Note 171940.1
CARDINALITY(t1 [,..],n) Makes the CBO to use different assumptions about
cardinality at the table level
ORDERED_PREDICATES Forces optimizer to apply predicates as they appear
in the WHERE clause, except for predicates used
as index keys
– Optimizer Mode:
FIRST_ROWS, ALL_ROWS Force CBO first rows or all rows.
RULE Force Rule if possible
NOTE: Starting from Oracle version 10.1.0.2 the following hints have been deprecated and
should not be used: AND_EQUAL, HASH_AJ, MERGE_AJ, NL_AJ, HASH_SJ, MERGE_SJ, NL_SJ,
ORDERED_PREDICATES, ROWID, STAR.
Also, many new hints have been introduced in Oracle 10g which are not listed in this
note. Some of those will be added at a later stage.