Re: Where can I get a complete list of all SQL Hints?

From: Franklin <member29243_at_dbforums.com>
Date: Sat, 14 Jun 2003 03:59:26 +0000
Message-ID: <3002068.1055563166@dbforums.com>

You can use grap and strings to get them directly from the Orale executable:

strings $ORACLE_HOME/bin/oracle> hints.lst

Oracle9i r2 documented hints:

ALL_ROWS
AND_EQUAL
ANTIJOIN
APPEND
BITMAP
BUFFER
BYPASS_RECURSIVE_CHECK
BYPASS_UJVC
CACHE
CACHE_CB
CACHE_TEMP_TABLE
CARDINALITY
CHOOSE
CIV_GB
COLLECTIONS_GET_REFS
CPU_COSTING
CUBE_GB
CURSOR_SHARING_EXACT
DEREF_NO_REWRITE
DML_UPDATE
DOMAIN_INDEX_NO_SORT
DOMAIN_INDEX_SORT

DRIVING_SITE
DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN

EXPAND_GSET_TO_UNION
FACT
FIRST_ROWS
FORCE_SAMPLE_BLOCK
FULL
GBY_CONC_ROLLUP
GLOBAL_TABLE_HINTS
HASH
HASH_AJ
HASH_SJ
HWM_BROKERED
IGNORE_ON_CLAUSE
IGNORE_WHERE_CLAUSE

INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
INDEX_JOIN
INDEX_RRS
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC

INLINE
LEADING
LIKE_EXPAND
LOCAL_INDEXES
MATERIALIZE
MERGE
MERGE_AJ
MERGE_SJ
MV_MERGE

NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID
NL_AJ
NL_SJ
NO_ACCESS
NO_BUFFER
NO_EXPAND
NO_EXPAND_GSET_TO_UNION
NO_FACT
NO_FILTERING
NO_INDEX
NO_MERGE
NO_MONITORING
NO_ORDER_ROLLUPS
NO_PRUNE_GSETS
NO_PUSH_PRED
NO_PUSH_SUBQ
NO_QKN_BUFF
NO_SEMIJOIN

NO_STATS_GSETS
NO_UNNEST
NOAPPEND
NOCACHE
NOCPU_COSTING
NOPARALLEL
NOPARALLEL_INDEX
NOREWRITE
OR_EXPAND
ORDERED
ORDERED_PREDICATES
OVERFLOW_NOMOVE
PARALLEL
PARALLEL_INDEX
PIV_GB
PIV_SSF

PQ_DISTRIBUTE
PQ_MAP
PQ_NOMAP

PUSH_PRED
PUSH_SUBQ
REMOTE_MAPPED
RESTORE_AS_INTERVALS
REWRITE
RULE
SAVE_AS_INTERVALS
SCN_ASCENDING
SELECTIVITY
SEMIJOIN
SEMIJOIN_DRIVER
SKIP_EXT_OPTIMIZER
SQLLDR
STAR
STAR_TRANSFORMATION
SWAP_JOIN_INPUTS

SYS_DL_CURSOR
SYS_PARALLEL_TXN
SYS_RID_ORDER
TIV_GB
TIV_SSF

UNNEST

USE_ANTI
USE_CONCAT
USE_HASH
USE_MERGE
USE_NL
USE_SEMI
USE_TTT_FOR_GSETS

Undocumented (under-documented) hints:

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

--
Posted via http://dbforums.com

Received on Fri Jun 13 2003 - 22:59:26 CDT