Oracle10g, Oracle11g hints (+ undocumented)

Summary
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 based on Oracle version. At the end of page there are the Undocumented hints. For more information about hints refer to Metalink Note:221970.1

Oracle9i hints

Optimizer Approaches(3)	Access Path Hints (13)	Other (36)	Join Operations (7)
ALL_ROWS FIRST_ROWS(n) RULE	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	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 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	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
Join Order (2)	Query Transformation (11)	Cluster Only Access (2)	Parallel Execution (5)
ORDERED LEADING	FACT NO_EXPAND NO_FACT NO_QUERY_TRANSFORMATION NO_REWRITE NO_STAR_TRANSFORMATION NO_UNNEST REWRITE STAR_TRANSFORMATION UNNEST USE_CONCAT	CLUSTER HASH	PARALLEL NO_PARALLEL PQ_DISTRIBUTE PARALLEL_INDEX NO_PARALLEL_INDEX

Oracle10g hints

Optimizer Approaches(2)	Access Path Hints (15)	Other (13)	Join Operation (7)
ALL_ROWS FIRST_ROWS(n)	FULL CLUSTER HASH INDEX NO_INDEX INDEX_ASC INDEX_COMBINE INDEX_JOIN INDEX_DESC INDEX_FFS NO_INDEX_FFS INDEX_SS INDEX_SS_ASC INDEX_SS_DESC NO_INDEX_SS	APPEND NOAPPEND CACHE NOCACHE PUSH_PRED NO_PUSH_PRED PUSH_SUBQ NO_PUSH_SUBQ QB_NAME CURSOR_SHARING_EXACT DRIVING_SITE DYNAMIC_SAMPLING MODEL_MIN_ANALYSIS	USE_NL NO_USE_NL USE_NL_WITH_INDEX USE_MERGE NO_USE_MERGE USE_HASH NO_USE_HASH
Join Order (2)	Query Transformation (13)	Cluster Only Access (2)	Parallel Execution (4)
ORDERED LEADING	NO_QUERY_TRANSFORMATION USE_CONCAT NO_EXPAND REWRITE NO_REWRITE MERGE NO_MERGE STAR_TRANSFORMATION NO_STAR_TRANSFORMATION FACT NO_FACT UNNEST NO_UNNEST	CLUSTER HASH	PARALLEL PQ_DISTRIBUTE PARALLEL_INDEX NO_PARALLEL_INDEX

Oracle11g hints

Optimizer Approaches(2)	Access Path Hints (17)	Other (20)	Join Operation (7)
ALL_ROWS FIRST_ROWS RULE	CLUSTER FULL HASH INDEX NO_INDEX INDEX_ASC INDEX_DESC INDEX_COMBINE INDEX_JOIN INDEX_FFS INDEX_SS INDEX_SS_ASC INDEX_SS_DESC NATIVE_FULL_OUTER_JOIN NO_NATIVE_FULL_OUTER_JOIN NO_INDEX_FFS NO_INDEX_SS	APPEND NOAPPEND CACHE NOCACHE CURSOR_SHARING_EXACT DRIVING_SITE DYNAMIC_SAMPLING MODEL_MIN_ANALYSIS MONITOR NO_MONITOR OPT_PARAM PUSH_PRED NO_PUSH_PRED PUSH_SUBQ NO_PUSH_SUBQ PX_JOIN_FILTER NO_PX_JOIN_FILTER QB_NAME RESULT_CACHE NO_RESULT_CACHE	USE_HASH NO_USE_HASH USE_MERGE NO_USE_MERGE USE_NL USE_NL_WITH_INDEX NO_USE_NL
Join Order (2)	Query Transformation (13)	XML (2)	Parallel Execution (5)
ORDERED LEADING	FACT NO_FACT MERGE NO_MERGE NO_EXPAND USE_CONCAT REWRITE NO_REWRITE NOREWRITE* UNNEST NO_UNNEST STAR_TRANSFORMATION NO_STAR_TRANSFORMATION NO_QUERY_TRANSFORMATION	NO_XMLINDEX_REWRITE NO_XML_QUERY_REWRITE	PARALLEL NOPARALLEL* NO_PARALLEL PARALLEL_INDEX NO_PARALLEL_INDEX NOPARALLEL_INDEX* PQ_DISTRIBUTE

Undocumented hints

Oracle 9i ()	Oracle 10g ()	Oracle 11g ()
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	BYPASS_RECURSIVE_CHECK BYPASS_UJVC CACHE_CB CACHE_TEMP_TABLE CIV_GB COLLECTIONS_GET_REFS CUBE_GB CURSOR_SHARING_EXACT DEREF_NO_REWRITE DML_UPDATE DOMAIN_INDEX_NO_SORT DOMAIN_INDEX_SORT DYNAMIC_SAMPLING 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 INDEX_SS INDEX_SS_ASC INDEX_SS_DESC LIKE_EXPAND LOCAL_INDEXES MV_MERGE NESTED_TABLE_GET_REFS NESTED_TABLE_SET_REFS NESTED_TABLE_SET_SETID NO_EXPAND_GSET_TO_UNION NO_FACT NO_FILTERING NO_ORDER_ROLLUPS NO_PRUNE_GSETS NO_STATS_GSETS NO_UNNEST NOCPU_COSTING OVERFLOW_NOMOVE PIV_GB PIV_SSF PQ_MAP PQ_NOMAP REMOTE_MAPPED RESTORE_AS_INTERVALS SAVE_AS_INTERVALS SCN_ASCENDING SKIP_EXT_OPTIMIZER SQLLDR SYS_DL_CURSOR SYS_PARALLEL_TXN SYS_RID_ORDER TIV_GB TIV_SSF UNNEST USE_TTT_FOR_GSETS	BYPASS_RECURSIVE_CHECK BYPASS_UJVC CACHE_CB CACHE_TEMP_TABLE CIV_GB COLLECTIONS_GET_REFS CUBE_GB CURSOR_SHARING_EXACT DEREF_NO_REWRITE DML_UPDATE DOMAIN_INDEX_NO_SORT DOMAIN_INDEX_SORT DYNAMIC_SAMPLING 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 INDEX_SS INDEX_SS_ASC INDEX_SS_DESC LIKE_EXPAND LOCAL_INDEXES MV_MERGE NESTED_TABLE_GET_REFS NESTED_TABLE_SET_REFS NESTED_TABLE_SET_SETID NO_EXPAND_GSET_TO_UNION NO_FACT NO_FILTERING NO_ORDER_ROLLUPS NO_PRUNE_GSETS NO_STATS_GSETS NO_UNNEST NOCPU_COSTING OVERFLOW_NOMOVE PIV_GB PIV_SSF PQ_MAP PQ_NOMAP REMOTE_MAPPED RESTORE_AS_INTERVALS SAVE_AS_INTERVALS SCN_ASCENDING SKIP_EXT_OPTIMIZER SQLLDR SYS_DL_CURSOR SYS_PARALLEL_TXN SYS_RID_ORDER TIV_GB TIV_SSF UNNEST USE_TTT_FOR_GSETS