Syntax
/*+ HINT HINT … */ – In PLSQL the space between the ‘+’ and the first letter of the hint is vital so /*+ ALL_ROWS */ is fine but /*+ALL_ROWS */ will cause problems.
Optimizer Mode
FIRST_ROWS, ALL_ROWS – Force CBO first rows or all rows
RULE – Force Rule if possible
ORDERED – Access tables in the order of the FROM clause
ORDERED_PREDICATES – Use in the WHERE clause to apply predicates in the order that they appear. This does not apply predicate evaluation on index keys
Sub-Queries/views
PUSH_SUBQ – Causes all subqueries in a query block to be executed at the earliest possible time (>=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 blockto prevent view V being merged
MERGE(v) – Do merge view V
MERGE_AJ(v) – Put hint in a NOT IN subquery to perform (>=7.3)
HASH_AJ(v) – SMJ anti-join or hash anti-join. (>=7.3) e.g. 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) – semi-join to access “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 <Parameter:CACHE_SIZE_THRESHOLD> treat as if it had the CACHE option set – only 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 e.g. 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’ 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_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)
NO_EXPAND – Do not perform OR-expansion i.e. 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 nestedloops 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)
Parallel Query Option
PARALLEL(table,<degree>[,<instances>]) – 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”
NOPARALLEL_INDEX(table [,index]) – No parallel on “index”
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_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
Philip Howe's Oracle Blog 