Re: Hash join order

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: 30 Mar 2003 08:03:51 GMT
Message-ID: <b668d7$1utvu$1@ID-82536.news.dfncis.de>

> Jonathan Lewis wrote:

><snip>
>> then you need to include the hint
>>     swap_join_inputs(table_a)
>> in the select statement (you may also need to
>> include an index(table_c) for the test case you
>> published).

>
> Thank you! That worked. Should I be worried that I can't find this
> hint in the documentation?

For some reasons, Oracle corp chose not to document every hint available. But it seems possible to extract possible hints out of the oracle binary with the unix command strings and they should be these (ora 9.2):

RULE
CHOOSE
ORDERED
ORDERED_PREDICATES

DOMAIN_INDEX_SORT
DOMAIN_INDEX_NO_SORT
GLOBAL_TABLE_HINTS

SKIP_EXT_OPTIMIZER
FIRST_ROWS
ALL_ROWS
CPU_COSTING
NOCPU_COSTING
NO_MERGE
PUSH_SUBQ
NO_PUSH_SUBQ
USE_CONCAT
NO_EXPAND
SEMIJOIN_DRIVER
ANTIJOIN
MERGE_AJ
HASH_AJ
NL_AJ
SEMIJOIN
NO_SEMIJOIN
MERGE_SJ
HASH_SJ
NL_SJ
STAR
REMOTE_MAPPED
STAR_TRANSFORMATION
NOREWRITE
REWRITE
BYPASS_UJVC
UNNEST
NO_UNNEST
LOCAL_INDEXES
HWM_BROKERED
IGNORE_ON_CLAUSE
OVERFLOW_NOMOVE
SYS_PARALLEL_TXN
SYS_RID_ORDER
BYPASS_RECURSIVE_CHECK
MATERIALIZE
INLINE
NO_FILTERING
FULL
PARALLEL
NOPARALLEL
CACHE_CB
CACHE
NOCACHE
CACHE_TEMP_TABLE
DRIVING_SITE
LEADING
MERGE
NO_PUSH_PRED
PUSH_PRED

USE_NL
USE_MERGE
USE_HASH
USE_ANTI
USE_SEMI

PQ_DISTRIBUTE
LIKE_EXPAND

OR_EXPAND
PQ_MAP
PQ_NOMAP

AND_EQUAL
NO_INDEX

INDEX_ASC
INDEX_DESC
INDEX_SS
INDEX_SS_ASC
INDEX_SS_DESC
INDEX_COMBINE
INDEX_JOIN
INDEX_FFS
INDEX_RRS
PIV_SSF
TIV_SSF
PIV_GB

TIV_GB
CIV_GB
CUBE_GB
SAVE_AS_INTERVALS
RESTORE_AS_INTERVALS
BITMAP
SCN_ASCENDING
APPEND
NOAPPEND
HASH
PARALLEL_INDEX
NOPARALLEL_INDEX

NESTED_TABLE_GET_REFS
NESTED_TABLE_SET_REFS
NESTED_TABLE_SET_SETID

COLLECTIONS_GET_REFS
NO_QKN_BUFF
SWAP_JOIN_INPUTS
FACT
NO_FACT
DEREF_NO_REWRITE
BUFFER
NO_BUFFER
NO_ACCESS
FORCE_SAMPLE_BLOCK
CURSOR_SHARING_EXACT
MV_MERGE
EXPAND_GSET_TO_UNION
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
DYNAMIC_SAMPLING_EST_CDN
DYNAMIC_SAMPLING
NO_MONITORING
SYS_DL_CURSOR
SQLLDR
DML_UPDATE Rene Nyffenegger

-- 
  Projektleitung und Entwicklung in Oracle/C++/C# Projekten
  http://www.adp-gmbh.ch/cv.html

Received on Sun Mar 30 2003 - 02:03:51 CST