Oracle 10g Release 2 Hint List

Getting a list of Oracle hints for SQL tuning is often difficult. The Oracle hint list is inside the Oracle executable and you can extract the Oracle hint list easily with UNIX commands.

You can use grep and strings to get them directly from the Oracle executable:

strings $ORACLE_HOME/bin/oracle > hints.lst

Here are categorized hint list :
Optimizer Approaches

• ALL_ROWS
• FIRST_ROWS(n)
• RULE

General

• APPEND
• CACHE
• CURSOR_SHARING_EXACT
• DRIVING_SITE
• DYNAMIC_SAMPLING
• MODEL_MIN_ANALYSIS
• NOAPPEND
• NO_CACHE
• NO_PUSH_PRED
• NO_PUSH_SUBQ
• NO_PX_JOIN_FILTER
• NO_XML_QUERY_REWRITE
• PUSH_PRED
• PUSH_SUBQ
• PX_JOIN_FILTER
• QB_NAME

Access Method Hints

• FULL
• INDEX
• INDEX_ASC
• INDEX_COMBINE
• INDEX_DESC
• INDEX_FFS
• INDEX_JOIN
• INDEX_SS
• INDEX_SS_ASC
• INDEX_SS_DESC
• NO_INDEX
• NO_INDEX_FFS
• NO_INDEX_SS

Cluster Only Access Method Hints

• CLUSTER
• HASH

Join Order

• LEADING
• ORDERED

Join Operation

• NO_USE_HASH (table1 table2)
• NO_USE_MERGE (table1 table2)
• NO_USE_NL (table1 table2)
• USE_HASH (table1 table2)
• USE_MERGE (table1 table2)
• USE_NL (table1 table2)
• USE_NL_WITH_INDEX

Parallel Execution

• PARALLEL
• NO_PARALLEL
• PQ_DISTRIBUTE
• PARALLEL_INDEX
• NO_PARALLEL_INDEX

Query Transformation

• FACT
• NO_EXPAND
• NO_FACT
• NO_QUERY_TRANSFORMATION
• NO_REWRITE
• NO_STAR_TRANSFORMATION
• NO_UNNEST
• REWRITE
• STAR_TRANSFORMATION
• UNNEST
• USE_CONCAT

Others

• ANTIJOIN
• BITMAP
• BUFFER
• CARDINALITY
• HASH_AJ
• INLINE
• MATERIALIZE
• MERGE
• NO_ACCESS
• NO_BUFFER
• NO_MERGE
• NO_MONITORING
• NO_PUSH_GSETS
• NO_PUSH_JOIN_PRED
• NO_QKN_BUFF
• NO_SEMIJOIN
• OR_EXPAND
• PUSH_JOIN_PRED
• SEMIJOIN
• SEMIJOIN_DRIVER
• SWAP_JOIN_INPUTS
• USE_ANTI
• USE_SEMI

Undocumented Optimizer Hints

• BYPASS_RECURSIVE_CHECK
• BYPASS_UJVC
• CACHE_CB
• CACHE_TEMP_TABLE
• CIV_GB
• COLLECTIONS_GET_REFS
• CPU_COSTING
• CUBE_GB
• DEREF_NO_REWRITE
• DML_UPDATE
• DOMAIN_INDEX_NO_SORT
• DOMAIN_INDEX_SORT
• DYNAMIC_SAMPLING_EST_CDN
• EXPAND_GSET_TO_UNION
• FORCE_SAMPLE_BLOCK
• GBY_CONC_ROLLUP
• GLOBAL_TABLE_HINTS
• HWM_BROKERED
• IGNORE_ON_CLAUSE
• IGNORE_WHERE_CLAUSE
• INDEX_RRS
• LIKE_EXPAND
• LOCAL_INDEXES
• MV_MERGE
• NESTED_TABLE_GET_REFS
• NESTED_TABLE_SET_REFS
• NESTED_TABLE_SET_SETID
• NO_ELIMINATE
• NO_EXPAND_GSET_TO_UNION
• NO_FILTERING
• NO_ORDER_ROLLUPS
• NO_PRUNE_GSETS
• NO_STATS_GSETS
• NOCPU_COSTING
• OB_NAME
• OVERFLOW_NOMOVE
• PIV_GB
• PIV_SSF
• PQ_MAP
• PQ_NOMAP
• REMOTE_MAPPED
• RESTORE_AS_INTERVALS
• SAVE_AS_INTERVALS
• SCN_ASCENDING
• SELECTIVITY
• SKIP_EXT_OPTIMIZER
• SQLLDR
• SYS_DL_CURSOR
• SYS_PARALLEL_TXN
• SYS_RID_ORDER
• TIV_GB
• TIV_SSF
• USE_TTT_FOR_GSETS