hint의 종류  
+ ALL_ROWS
- /*+ ALL_ROWS */
- 예
SELECT /*+ ALL_ROWS */ empno, ename, sal, job
FROM emp
WHERE empno = 7566;

+ FIRST_ROWS
- /*+ FIRST_ROWS */

+ CHOOSE
- /*+ CHOOSE */

+ RULE
- /*+ RULE */


** Hints for Access Methods

+ FULL
- /*+ FULL(table) */
- 예
SELECT /*+ FULL(a) Don't use the index on ACCNO */ accno, bal
FROM accounts a
WHERE accno = 7086854;

+ ROWID
- /*+ ROWID(table) */

+ CLUSTER
- /*+ CLUSTER(table) */
- 예
SELECT --+ CLUSTER emp ename, deptno
FROM emp, dept
WHERE dept.deptno = 10 AND
emp.deptno = dept.deptno;

+ HASH
- /*+ HASH(table) */
- CLUSTER 에 저장된 TABLE에만 적용됨.

+ HASH_AJ
- /*+ HASH_AJ */
- a NOT IN subquery -> a hash anti-join 으로 transformation.

+ HASH_SJ
- /*+ HASH_SJ */
- a correlated EXISTS subquery -> a hash semi-join 으로 transformation.

+ INDEX
- /*+ INDEX(table index1 index2 index3 ...) */
- in list predicat에 대해서도 가능.
- Multi-column inlists 는 index 를 사용할 수 없다.

+ INDEX_ASC
- /*+ INDEX_ASC(table index1 index2 index3 ...) */
- INDEX HINT 와 동일

+ INDEX_DESC
- /*+ INDEX_DESC(table index1 index2 index3 ...) */

+ INDEX_FFS
- /*+ INDEX_FFS(table index1 index2 index3 ...) */
- fast full index scan

+ MERGE_AJ
- /*+ MERGE_AJ */
- a NOT IN subquery -> a merge anti-join 으로 transformation.

+ MERGE_SJ
- /*+ MERGE_SJ */
- a correlated EXISTS subquery -> a merge semi-join 으로 transformation.

+ AND_EQUAL
- /*+ AND_EQUAL(table index1 index2 ... index5) */
- 적어도 2개 이상의 index를 지정해야 하고, max로 5개까지 지정 가능.

+ USE_CONCAT
- /*+ USE_CONCAT */
- combined OR conditions -> a compound query using the UNION ALL set operator 로
transformation 한다.
- 일반적으로, 이 transformation은 다음과 같은 상황에서 발생
if concatenations을 사용하는 query의 cost가 그렇지 않는 cost보다 더 작을 때.
- inlists processing과 OR-expands all disjunctions를 turns off한다.

** Hints for Join Orders

+ ORDERED
- /*+ ORDERED */

+ STAR
- /*+ STAR */

** Hints for Join Operations

+ USE_NL
- /*+ USE_NL(table1 table2 ...) */
- 지정된 table이 inner table이 된다. ( inner table <-> driving(outer) table )
- 흔히 ORDERED Hint와 함께 쓴다.

cf) Tuning reference guide 에 의하면 use_nl과 use_merge 는 모두 ordered hint 와
함께 사용되어져야 하며, 오라클은 이 hint 가 inner table 인 경우 사용되어진다고
언급하고 있습니다.


+ USE_MERGE
- /*+ USE_MERGE(table1 table2 ...) */

+ USE_HASH
- /*+ USE_HASH(table1 table2 ...) */

+ DRIVING_SITE
-
- query execution이 행해진다.
- rule/cost-based 모두 가능.
- 예
SELECT /*+DRIVING_SITE(dept)*/ * FROM emp, dept@rsite
WHERE emp.deptno = dept.deptno;

** Additional Hints

+ CACHE
- /*+ CACHE(table) */
- full table scan시 retrieve된 block을 LRU list에서 most recently used end에 놓는다.
즉, memory에 오래 존재하게 한다.
- 예
SELECT /*+ FULL (scott_emp) CACHE(scott_emp) */ ename
FROM scott.emp scott_emp;

+ NOCACHE
- /*+ NOCACHE(table) */
- full table scan시 retrieve된 block을 LRU list에서 least recently used end에 놓는다.
즉, memory에서 금방 내려가게 한다.
- 예
SELECT /*+ FULL (scott_emp) NOCACHE(scott_emp) */ ename
FROM scott.emp scott_emp;

+ MERGE
- /*+ MERGE(view) */
- COMPLEX_VIEW_MERGING = FALSE 로 되어 있을 때
- view 또는 subquery의 내용을 merge가능.

+ NOMERGE
- /*+ NOMERGE(view) */
- COMPLEX_VIEW_MERGING = TRUE 로 되어 있을 때 사용
- view 또는 subquery의 내용을 merge불가능.
- view 또는 subquery자체의 query문에 의한 영향을 많이 받게됨.

+ PUSH_JOIN_PRED


+ NO_PUSH_JOIN_PRED


+ PUSH_SUBQ
- /*+ PUSH_SUBQ */
- nomerged subqueries가 execution plan에서 가능한 가장 빠른 위치에서 evaluation되도록 한다.
- 일반적으로, merge되지 않은 subqueries는 execution plan에서 마지막 step으로써 수행된다.
subqueries가 상대적으로 inexpensive하고 rows의 수를 줄일 수 있다면, subqueries를 더 일찍
evaluation하는 것이 performance를 향상시킬 것이다.
- subquery가 remote table에 적용되거나, merge join을 사용하는 join된 table에 적용된다면
이 hint는 적용되지 않는다.

+ STAR_TRANSFORMATION
- /*+ START TRANSFORMATION */


Session Level:
ALTER SESSION SET optimizer_goal= rule | first_rows | all_rows | choose ;

Hints:
- Hints always force the use of the cost based optimizer (Except RULE).
- Use ALIASES for the tablenames in the hints.
- Ensure tables are analyzed.

- 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
@ see [BUG:697121]
)

- 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.
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.
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 (>=7.3)
HASH_AJ(v) } SMJ anti-join or hash anti-join. (>=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) } 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
treat as if it had the CACHE option set.
See . 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
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 index ) Use 'index' to access 'tab'
INDEX_ASC( tab index ) Use 'index' to access 'tab' for range scan.
INDEX_DESC( tab index ) Use descending index range scan
(Join problems pre 7.3)
INDEX_FFS( tab index) 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

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 , 7.3 no hint req)

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)

- 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"
NOPARALLEL_INDEX(table [,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