Re: Hash join order

From: Telemachus <telemachus_at_ulysseswillreturn.net>
Date: Mon, 31 Mar 2003 11:57:19 +0100
Message-ID: <kaVha.6034$pK2.5997@news.indigo.ie>

I've just compared Rene's list to that in the 9.2 Designing and Tuning for performance and we find the following :

Rene's list does not have the following hints :

CLUSTER
ROWID
INDEX (although all the subsidiary index hints are there)

The following hints ARE documented

RULE
CHOOSE
ORDERED
ORDERED_PREDICATES
FIRST_ROWS
ALL_ROWS
NO_MERGE
PUSH_SUBQ
NO_PUSH_SUBQ
USE_CONCAT
NO_EXPAND
MERGE_AJ
HASH_AJ
NL_AJ
MERGE_SJ
HASH_SJ
NL_SJ
STAR
STAR_TRANSFORMATION
NOREWRITE
REWRITE
UNNEST
NO_UNNEST
FULL
PARALLEL
NOPARALLEL
CACHE
NOCACHE
DRIVING_SITE
LEADING
MERGE
NO_PUSH_PRED
PUSH_PRED

USE_NL
USE_MERGE
USE_HASH

PQ_DISTRIBUTE
AND_EQUAL
NO_INDEX

INDEX_ASC
INDEX_DESC
INDEX_COMBINE
INDEX_JOIN
INDEX_FFS

APPEND
NOAPPEND
HASH
PARALLEL_INDEX
NOPARALLEL_INDEX
FACT
NO_FACT
CURSOR_SHARING_EXACT
EXPAND_GSET_TO_UNION
DYNAMIC_SAMPLING_EST_CDN
DYNAMIC_SAMPLING And these ones are not ( at least not in the hints section of that manual)

DOMAIN_INDEX_SORT
DOMAIN_INDEX_NO_SORT
GLOBAL_TABLE_HINTS

SKIP_EXT_OPTIMIZER
CPU_COSTING
NOCPU_COSTING
SEMIJOIN_DRIVER
ANTIJOIN
SEMIJOIN
NO_SEMIJOIN
REMOTE_MAPPED
BYPASS_UJVC
LOCAL_INDEXES
HWM_BROKERED
IGNORE_ON_CLAUSE
OVERFLOW_NOMOVE
SYS_PARALLEL_TXN
SYS_RID_ORDER
BYPASS_RECURSIVE_CHECK
MATERIALIZE
INLINE
NO_FILTERING
CACHE_CB
CACHE_TEMP_TABLE
USE_ANTI
USE_SEMI
LIKE_EXPAND

OR_EXPAND
PQ_MAP
PQ_NOMAP
INDEX_SS
INDEX_SS_ASC

INDEX_SS_DESC
INDEX_RRS

PIV_SSF
TIV_SSF
PIV_GB
TIV_GB
CIV_GB

CUBE_GB
SAVE_AS_INTERVALS
RESTORE_AS_INTERVALS
BITMAP
SCN_ASCENDING

NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID

COLLECTIONS_GET_REFS
NO_QKN_BUFF
SWAP_JOIN_INPUTS
DEREF_NO_REWRITE
BUFFER
NO_BUFFER
NO_ACCESS
FORCE_SAMPLE_BLOCK

MV_MERGE
NO_EXPAND_GSET_TO_UNION
NO_PRUNE_GSETS

USE_TTT_FOR_GSETS

GBY_CONC_ROLLUP
NO_STATS_GSETS
NO_ORDER_ROLLUPS

CARDINALITY
SELECTIVITY
IGNORE_WHERE_CLAUSE
NO_MONITORING
SYS_DL_CURSOR
SQLLDR
DML_UPDATE. Note this is probably not a definitive list; new patches to oracle may introduce more new and undocumented hints. Also, this may not be all the hints there either. YMMV.
Also the list of documented hints may also shrink as the optimizer gets better and better at figuring stuff out.

Basically I'm saying don't rely on this ! Received on Mon Mar 31 2003 - 04:57:19 CST