本文書に関する指摘については、原文著者のTanel Poderさんではなく、渡部まで御連絡お願いいたします。
注意事項
- 自動翻訳に手を加えたレベルの翻訳と理解してください
- 意味をとりやすくするために追加した箇所については、《…》で囲っています。
- 訳がわからない、または、自信がない箇所は★をつけています。
日本語訳：渡部 亮太 (WR at Csus4 dot net)
履歴
- 2013-04-09: 初稿up
V$SQL_FEATUREビューを使用してヒントの効果を理解する
あなたはすでにOracle 11gのV$SQL_HINTビューを使用したことがあるかもしれません – そのビューはすべての有効なヒント（文書化された文書化されていないものの両方）を表示します。例えば、使っているOracleのバージョンで利用可能なヒント：
SQL> @hint merge
NAME VERSION VERSION_OUTLINE INVERSE
\---------------------------------------------------------------- ------------------------- ------------------------- ----------------------------------------------------------------
MERGE_CONST_ON 8.0.0
MERGE_AJ 8.1.0 8.1.7
MERGE_SJ 8.1.0 8.1.7
MV_MERGE 9.0.0
MERGE 8.1.0 10.1.0 NO_MERGE
NO_MERGE 8.0.0 10.1.0 MERGE
USE_MERGE_CARTESIAN 11.1.0.6 11.1.0.6
USE_MERGE 8.1.0 8.1.7 NO_USE_MERGE
NO_USE_MERGE 10.1.0.3 10.1.0.3 USE_MERGE
しかし、それだけではありません。V$SQL_FEATUREとV$SQL_FEATURE_HIERARCHYのような（なかば）文書化されていないビューは、これらのヒントが何に関連するかについてより多くの情報を与えてくれます。例えば、なぜMERGEヒントとUSE_MERGEヒントがあるのか、あなたが今まで疑問に思っていたかもしれません。私のhinth.sql （Hint Hierachy）スクリプトを使用して、これらのヒントが何を制御するかを確認できます：
SQL> @hinth MERGE
Display Hint feature hierarchy for hints like MERGE
NAME PATH
\---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
MERGE ALL -> COMPILATION -> CBO -> CBQT -> CVM
MERGE ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> CVM
MERGE ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> CVM
だから、MERGEヒントはCBOの問合せ変換コードに影響を与えるように見えます。- （CBQTはコストベースのクエリ変換を意味し、CVMは複雑なビューのマージを意味しますが、その詳細に??ついてはあとで）。
SQL> @hinth USE_MERGE
Display Hint feature hierarchy for hints like USE_MERGE
NAME PATH
\---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
USE_MERGE ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE
そして、USE_MERGEヒントは結合方法の使用の制御についてのものです – ソートマージ?ジョイン。
NLが含まれるすべてのヒントの一覧を表示してみましょう：
SQL> @hinth %NL%
Display Hint feature hierarchy for hints like %NL%
NAME PATH
\---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
INLINE_XMLTYPE_NT ALL
NL_SJ ALL -> COMPILATION -> CBO
NL_AJ ALL -> COMPILATION -> CBO
NO_TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_USE_NL ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL_WITH_INDEX ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL_WITH_INDEX
NO_TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_CONNECT_BY_CB_WHR_ONLY ALL -> COMPILATION -> TRANSFORMATION
CONNECT_BY_CB_WHR_ONLY ALL -> COMPILATION -> TRANSFORMATION
INLINE ALL -> COMPILATION -> TRANSFORMATION
NO_TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_NLJ_BATCHING ALL -> EXECUTION
NLJ_BATCHING ALL -> EXECUTION
NO_NLJ_PREFETCH ALL -> EXECUTION
NLJ_PREFETCH ALL -> EXECUTION
ここで面白いことがたくさんがある – 最近現れた新しいヒントTABLE_LOOKUP_BY_NLは、例えばスター型変換と一緒に《使用》しなければならないようだ。（私はちょうどこの出力から、このことを学んだ）
興味深いことに、NLJ_BATCHINGとNLJ_PREFETCHヒントは明らかに実行フェーズのヒントであると考えられます。（これは私の用語です。私は、最適化時だけではなく、実行段階における意思決定に（も）影響を与えるヒントであると考えている）たとえば、通常、NLJプリフェッチ機能は、クエリの実行中に動的にオンとオフを切り替えることができる。私は《ヒントを使用して》この機能は常に有効だろうと推測している。（私はここにこれについては確信を得ているわけではない。ヒントが"実行"フェーズに関連すると表示されていることから推測しているだけだ。）
CBQTとCVMのようなオプティマイザ機能の用語がすぐにピンとこない場合は、これらのSQL機能名の略語が何を意味するかについて、または、この特定の機能が階層においてどこに位置づけられるのかについて、V$SQL_FEATUREビュー（または私のsqlfh.sqlスクリプト）を使用して、いくつかの詳細情報を一覧表示できます。
以下のスクリプトは、一切のパラメータを受け入れません。SQL全体の機能階層を出力します（V$SYSTEM_FIX_CONTROLから確認できる一時的なバグ修正の機能を除いて）：
SQL> @sqlfh SQL_FEATURE DESCRIPTION \------------------------------------------------------- ---------------------------------------------------------------- ALL A Universal Feature COMPILATION SQL COMPILATION CBO SQL Cost Based Optimization ACCESS_PATH Query access path AND_EQUAL Index and-equal access path BITMAP_TREE Bitmap tree access path FULL Full table scan INDEX Index INDEX_ASC Index (ascending) INDEX_COMBINE Combine index for bitmap access INDEX_DESC Use index (descending) INDEX_FFS Index fast full scan INDEX_JOIN Index join INDEX_RS_ASC Index range scan INDEX_RS_DESC Index range scan descending INDEX_SS Index skip scan INDEX_SS_ASC Index skip scan ascending INDEX_SS_DESC Index skip scan descending SORT_ELIM Sort Elimination Via Index CBQT Cost Based Query Transformation CVM Complex View Merging DIST_PLCMT Distinct Placement JOINFAC Join Factorization JPPD Join Predicate Push Down PLACE_GROUP_BY Group-By Placement PULL_PRED pull predicates STAR_TRANS Star Transformation TABLE_LOOKUP_BY_NL Table Lookup By Nested Loop TABLE_EXPANSION Table Expansion UNNEST unnest query block CURSOR_SHARING Cursor sharing DML DML JOIN_METHOD Join methods USE_HASH Hash join USE_MERGE Sort-merge join USE_MERGE_CARTESIAN Merge join cartesian USE_NL Nested-loop join USE_NL_WITH_INDEX Nested-loop index join JOIN_ORDER Join order OPT_MODE Optimizer mode ALL_ROWS All rows (optimizer mode) CHOOSE Choose (optimizer mode) FIRST_ROWS First rows (optimizer mode) OR_EXPAND OR expansion OUTLINE Outlines PARTITION Partition PQ Parallel Query PARALLEL Parallel table PQ_DISTRIBUTE PQ Distribution method PQ_MAP PQ slave mapper PX_JOIN_FILTER Bloom filtering for joins STAR_TRANS Star Transformation TABLE_LOOKUP_BY_NL Table Lookup By Nested Loop STATS Optimizer statistics CARDINALITY Cardinality computation COLUMN_STATS Basic column statistics CPU_COSTING CPU costing DBMS_STATS Statistics gathered by DBMS_STATS DYNAMIC_SAMPLING Dynamic sampling DYNAMIC_SAMPLING_EST_CDN Estimate CDN using dynamic sampling GATHER_PLAN_STATISTICS Gather plan statistics INDEX_STATS Basic index statistics OPT_ESTIMATE Optimizer estimates TABLE_STATS Basic table statistics QUERY_REWRITE query rewrite with materialized views RBO SQL Rule Based Optimization SQL_CODE_GENERATOR SQL Code Generator SQL_PLAN_MANAGEMENT SQL Plan Management TRANSFORMATION Query Transformation CBQT Cost Based Query Transformation CVM Complex View MergingDIST_PLCMT Distinct Placement JOINFAC Join Factorization JPPD Join Predicate Push Down PLACE_GROUP_BY Group-By Placement PULL_PRED pull predicates STAR_TRANS Star Transformation TABLE_LOOKUP_BY_NL Table Lookup By Nested Loop TABLE_EXPANSION Table Expansion UNNEST unnest query block HEURISTIC Heuristic Query Transformation CNT Count(col) to count(*) COALESCE_SQ coalesce subqueries CSE Common Sub-Expression Elimination CVM Complex View Merging FILTER_PUSH_PRED Push filter predicates FULL_OUTER_JOIN_TO_OUTER Join Conversion JPPD Join Predicate Push Down OBYE Order-by Elimination OLD_PUSH_PRED Old push predicate algorithm (pre-10.1.0.3) OUTER_JOIN_TO_ANTI Join Conversion OUTER_JOIN_TO_INNER Join Conversion PRED_MOVE_AROUND Predicate move around SET_TO_JOIN Transform set operations to joins SVM Simple View Merging TABLE_ELIM Table Elimination UNNEST unnest query block USE_CONCAT Or-optimization XML_REWRITE XML Rewrite CHECK_ACL_REWRITE Check ACL Rewrite COST_XML_QUERY_REWRITE Cost Based XML Query Rewrite XMLINDEX_REWRITE XMLIndex Rewrite EXECUTION SQL EXECUTION
私は上記のCVMとCBQTラインを強調しました…
参考までに（そして、あなたは、これらのスクリプトを自分で実行するのが面倒ならば）、ヒント機能階層スクリプトの完全な出力も貼り付けておきました。（Oracle Database 11.2.0.3 で実行しました）：
SQL> @hinth %
Display Hint feature hierarchy for hints like %
NAME PATH
\---------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------
NO_XDB_FASTPATH_INSERT ALL
XDB_FASTPATH_INSERT ALL
NO_USE_HASH_GBY_FOR_PUSHDOWN ALL
USE_HASH_GBY_FOR_PUSHDOWN ALL
XMLINDEX_SEL_IDX_TBL ALL
NO_DST_UPGRADE_INSERT_CONV ALL
DST_UPGRADE_INSERT_CONV ALL
NO_CONNECT_BY_ELIM_DUPS ALL
CONNECT_BY_ELIM_DUPS ALL
NO_MONITOR ALL
MONITOR ALL
NO_NATIVE_FULL_OUTER_JOIN ALL
NATIVE_FULL_OUTER_JOIN ALL
NO_CONNECT_BY_COMBINE_SW ALL
CONNECT_BY_COMBINE_SW ALL
OPT_PARAM ALL
OUTLINE_LEAF ALL
OUTLINE ALL
NO_CARTESIAN ALL
INCLUDE_VERSION ALL
RESTRICT_ALL_REF_CONS ALL
NO_ACCESS ALL
HASH ALL
DRIVING_SITE ALL
CACHE_TEMP_TABLE ALL
QB_NAME ALL
NO_STATS_GSETS ALL
NO_USE_HASH_AGGREGATION ALL
USE_HASH_AGGREGATION ALL
NO_MODEL_PUSH_REF ALL
MODEL_NO_ANALYSIS ALL
SCN_ASCENDING ALL
TIV_GB ALL
PIV_GB ALL
TIV_SSF ALL
PIV_SSF ALL
NO_CONNECT_BY_FILTERING ALL
CONNECT_BY_FILTERING ALL
BYPASS_RECURSIVE_CHECK ALL
SYS_RID_ORDER ALL
NO_BASETABLE_MULTIMV_REWRITE ALL
NO_MULTIMV_REWRITE ALL
REMOTE_MAPPED ALL
NO_GBY_PUSHDOWN ALL
GBY_PUSHDOWN ALL
IGNORE_OPTIM_EMBEDDED_HINTS ALL
DB_VERSION ALL
OPTIMIZER_FEATURES_ENABLE ALL
USE_WEAK_NAME_RESL ALL
IGNORE_WHERE_CLAUSE ALL
INLINE_XMLTYPE_NT ALL
NESTED_TABLE_FAST_INSERT ALL
NESTED_TABLE_SET_SETID ALL
PRESERVE_OID ALL
NESTED_TABLE_GET_REFS ALL
DEREF_NO_REWRITE ALL
NO_SQL_TUNE ALL
NO_MONITORING ALL
NO_OUTER_JOIN_TO_ANTI ALL -> COMPILATION -> CBO
OUTER_JOIN_TO_ANTI ALL -> COMPILATION -> CBO
NO_FULL_OUTER_JOIN_TO_OUTER ALL -> COMPILATION -> CBO
FULL_OUTER_JOIN_TO_OUTER ALL -> COMPILATION -> CBO
APPEND_VALUES ALL -> COMPILATION -> CBO
NUM_INDEX_KEYS ALL -> COMPILATION -> CBO
NO_DOMAIN_INDEX_FILTER ALL -> COMPILATION -> CBO
DOMAIN_INDEX_FILTER ALL -> COMPILATION -> CBO
NO_PARTIAL_COMMIT ALL -> COMPILATION -> CBO
SKIP_UNQ_UNUSABLE_IDX ALL -> COMPILATION -> CBO
X_DYN_PRUNE ALL -> COMPILATION -> CBO
ROWID ALL -> COMPILATION -> CBO
CLUSTER ALL -> COMPILATION -> CBO
NO_SWAP_JOIN_INPUTS ALL -> COMPILATION -> CBO
SWAP_JOIN_INPUTS ALL -> COMPILATION -> CBO
INDEX_RRS ALL -> COMPILATION -> CBO
NO_SUBQUERY_PRUNING ALL -> COMPILATION -> CBO
SUBQUERY_PRUNING ALL -> COMPILATION -> CBO
USE_SEMI ALL -> COMPILATION -> CBO
USE_ANTI ALL -> COMPILATION -> CBO
QUEUE_ROWP ALL -> COMPILATION -> CBO
QUEUE_CURR ALL -> COMPILATION -> CBO
CACHE_CB ALL -> COMPILATION -> CBO
NO_PARALLEL ALL -> COMPILATION -> CBO
CURSOR_SHARING_EXACT ALL -> COMPILATION -> CBO
NO_BUFFER ALL -> COMPILATION -> CBO
BUFFER ALL -> COMPILATION -> CBO
NO_QKN_BUFF ALL -> COMPILATION -> CBO
BITMAP ALL -> COMPILATION -> CBO
RESTORE_AS_INTERVALS ALL -> COMPILATION -> CBO
SAVE_AS_INTERVALS ALL -> COMPILATION -> CBO
CUBE_GB ALL -> COMPILATION -> CBO
SYS_PARALLEL_TXN ALL -> COMPILATION -> CBO
OVERFLOW_NOMOVE ALL -> COMPILATION -> CBO
HWM_BROKERED ALL -> COMPILATION -> CBO
LOCAL_INDEXES ALL -> COMPILATION -> CBO
BYPASS_UJVC ALL -> COMPILATION -> CBO
NL_SJ ALL -> COMPILATION -> CBO
HASH_SJ ALL -> COMPILATION -> CBO
MERGE_SJ ALL -> COMPILATION -> CBO
NL_AJ ALL -> COMPILATION -> CBO
HASH_AJ ALL -> COMPILATION -> CBO
MERGE_AJ ALL -> COMPILATION -> CBO
SEMIJOIN_DRIVER ALL -> COMPILATION -> CBO
SKIP_EXT_OPTIMIZER ALL -> COMPILATION -> CBO
DOMAIN_INDEX_NO_SORT ALL -> COMPILATION -> CBO
DOMAIN_INDEX_SORT ALL -> COMPILATION -> CBO
ORDERED_PREDICATES ALL -> COMPILATION -> CBO
ORDERED ALL -> COMPILATION -> CBO
FBTSCAN ALL -> COMPILATION -> CBO
MERGE_CONST_ON ALL -> COMPILATION -> CBO
STREAMS ALL -> COMPILATION -> CBO
EXPR_CORR_CHECK ALL -> COMPILATION -> CBO
VECTOR_READ_TRACE ALL -> COMPILATION -> CBO
VECTOR_READ ALL -> COMPILATION -> CBO
DML_UPDATE ALL -> COMPILATION -> CBO
SQLLDR ALL -> COMPILATION -> CBO
SYS_DL_CURSOR ALL -> COMPILATION -> CBO
NO_REF_CASCADE ALL -> COMPILATION -> CBO
REF_CASCADE_CURSOR ALL -> COMPILATION -> CBO
NOAPPEND ALL -> COMPILATION -> CBO
APPEND ALL -> COMPILATION -> CBO
AND_EQUAL ALL -> COMPILATION -> CBO -> ACCESS_PATH -> AND_EQUAL
BITMAP_TREE ALL -> COMPILATION -> CBO -> ACCESS_PATH -> BITMAP_TREE
FULL ALL -> COMPILATION -> CBO -> ACCESS_PATH -> FULL
NO_USE_INVISIBLE_INDEXES ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
USE_INVISIBLE_INDEXES ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
NO_INDEX ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
INDEX ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX
INDEX_ASC ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_ASC
INDEX_COMBINE ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_COMBINE
INDEX_DESC ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_DESC
NO_INDEX_FFS ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_FFS
INDEX_FFS ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_FFS
INDEX_JOIN ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_JOIN
INDEX_RS_ASC ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_RS_ASC
INDEX_RS_DESC ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_RS_DESC
NO_INDEX_SS ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS
INDEX_SS ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS
INDEX_SS_ASC ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS_ASC
INDEX_SS_DESC ALL -> COMPILATION -> CBO -> ACCESS_PATH -> INDEX_SS_DESC
NO_MERGE ALL -> COMPILATION -> CBO -> CBQT -> CVM
MERGE ALL -> COMPILATION -> CBO -> CBQT -> CVM
NO_PLACE_DISTINCT ALL -> COMPILATION -> CBO -> CBQT -> DIST_PLCMT
PLACE_DISTINCT ALL -> COMPILATION -> CBO -> CBQT -> DIST_PLCMT
NO_FACTORIZE_JOIN ALL -> COMPILATION -> CBO -> CBQT -> JOINFAC
FACTORIZE_JOIN ALL -> COMPILATION -> CBO -> CBQT -> JOINFAC
NO_PLACE_GROUP_BY ALL -> COMPILATION -> CBO -> CBQT -> PLACE_GROUP_BY
PLACE_GROUP_BY ALL -> COMPILATION -> CBO -> CBQT -> PLACE_GROUP_BY
NO_PULL_PRED ALL -> COMPILATION -> CBO -> CBQT -> PULL_PRED
PULL_PRED ALL -> COMPILATION -> CBO -> CBQT -> PULL_PRED
NO_FACT ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
FACT ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
NO_STAR_TRANSFORMATION ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
STAR_TRANSFORMATION ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
STAR ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS
NO_TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> CBO -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_EXPAND_TABLE ALL -> COMPILATION -> CBO -> CBQT -> TABLE_EXPANSION
EXPAND_TABLE ALL -> COMPILATION -> CBO -> CBQT -> TABLE_EXPANSION
NO_UNNEST ALL -> COMPILATION -> CBO -> CBQT -> UNNEST
UNNEST ALL -> COMPILATION -> CBO -> CBQT -> UNNEST
NO_BIND_AWARE ALL -> COMPILATION -> CBO -> CURSOR_SHARING
BIND_AWARE ALL -> COMPILATION -> CBO -> CURSOR_SHARING
RETRY_ON_ROW_CHANGE ALL -> COMPILATION -> CBO -> DML
CHANGE_DUPKEY_ERROR_INDEX ALL -> COMPILATION -> CBO -> DML
IGNORE_ROW_ON_DUPKEY_INDEX ALL -> COMPILATION -> CBO -> DML
NO_USE_HASH ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_HASH
USE_HASH ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_HASH
NO_USE_MERGE ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE
USE_MERGE ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE
USE_MERGE_CARTESIAN ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_MERGE_CARTESIAN
NO_USE_NL ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL
USE_NL_WITH_INDEX ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_NL_WITH_INDEX
LEADING ALL -> COMPILATION -> CBO -> JOIN_ORDER
ALL_ROWS ALL -> COMPILATION -> CBO -> OPT_MODE -> ALL_ROWS
CHOOSE ALL -> COMPILATION -> CBO -> OPT_MODE -> CHOOSE
FIRST_ROWS ALL -> COMPILATION -> CBO -> OPT_MODE -> FIRST_ROWS
OR_EXPAND ALL -> COMPILATION -> CBO -> OR_EXPAND
NO_PARALLEL_INDEX ALL -> COMPILATION -> CBO -> PQ
PARALLEL_INDEX ALL -> COMPILATION -> CBO -> PQ
NO_STATEMENT_QUEUING ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
STATEMENT_QUEUING ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
SHARED ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
NOPARALLEL ALL -> COMPILATION -> CBO -> PQ -> PARALLEL
PQ_DISTRIBUTE ALL -> COMPILATION -> CBO -> PQ -> PQ_DISTRIBUTE
PQ_NOMAP ALL -> COMPILATION -> CBO -> PQ -> PQ_MAP
PQ_MAP ALL -> COMPILATION -> CBO -> PQ -> PQ_MAP
NO_PX_JOIN_FILTER ALL -> COMPILATION -> CBO -> PQ -> PX_JOIN_FILTER
PX_JOIN_FILTER ALL -> COMPILATION -> CBO -> PQ -> PX_JOIN_FILTER
NO_FACT ALL -> COMPILATION -> CBO -> STAR_TRANS
STAR ALL -> COMPILATION -> CBO -> STAR_TRANS
FACT ALL -> COMPILATION -> CBO -> STAR_TRANS
NO_STAR_TRANSFORMATION ALL -> COMPILATION -> CBO -> STAR_TRANS
STAR_TRANSFORMATION ALL -> COMPILATION -> CBO -> STAR_TRANS
NO_TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> CBO -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
CARDINALITY ALL -> COMPILATION -> CBO -> STATS
TABLE_STATS ALL -> COMPILATION -> CBO -> STATS
INDEX_STATS ALL -> COMPILATION -> CBO -> STATS
COLUMN_STATS ALL -> COMPILATION -> CBO -> STATS
NO_CPU_COSTING ALL -> COMPILATION -> CBO -> STATS -> CPU_COSTING
CPU_COSTING ALL -> COMPILATION -> CBO -> STATS -> CPU_COSTING
DBMS_STATS ALL -> COMPILATION -> CBO -> STATS -> DBMS_STATS
DYNAMIC_SAMPLING ALL -> COMPILATION -> CBO -> STATS -> DYNAMIC_SAMPLING
DYNAMIC_SAMPLING_EST_CDN ALL -> COMPILATION -> CBO -> STATS -> DYNAMIC_SAMPLING_EST_CDN
GATHER_PLAN_STATISTICS ALL -> COMPILATION -> CBO -> STATS -> GATHER_PLAN_STATISTICS
OPT_ESTIMATE ALL -> COMPILATION -> CBO -> STATS -> OPT_ESTIMATE
RBO_OUTLINE ALL -> COMPILATION -> RBO
RULE ALL -> COMPILATION -> RBO
NO_PRUNE_GSETS ALL -> COMPILATION -> TRANSFORMATION
MODEL_DONTVERIFY_UNIQUENESS ALL -> COMPILATION -> TRANSFORMATION
MODEL_PUSH_REF ALL -> COMPILATION -> TRANSFORMATION
MODEL_COMPILE_SUBQUERY ALL -> COMPILATION -> TRANSFORMATION
MODEL_DYNAMIC_SUBQUERY ALL -> COMPILATION -> TRANSFORMATION
MODEL_MIN_ANALYSIS ALL -> COMPILATION -> TRANSFORMATION
NO_EXPAND_GSET_TO_UNION ALL -> COMPILATION -> TRANSFORMATION
EXPAND_GSET_TO_UNION ALL -> COMPILATION -> TRANSFORMATION
MV_MERGE ALL -> COMPILATION -> TRANSFORMATION
NO_CONNECT_BY_COST_BASED ALL -> COMPILATION -> TRANSFORMATION
CONNECT_BY_COST_BASED ALL -> COMPILATION -> TRANSFORMATION
INLINE ALL -> COMPILATION -> TRANSFORMATION
MATERIALIZE ALL -> COMPILATION -> TRANSFORMATION
REWRITE_OR_ERROR ALL -> COMPILATION -> TRANSFORMATION
NO_REWRITE ALL -> COMPILATION -> TRANSFORMATION
REWRITE ALL -> COMPILATION -> TRANSFORMATION
NO_SEMIJOIN ALL -> COMPILATION -> TRANSFORMATION
SEMIJOIN ALL -> COMPILATION -> TRANSFORMATION
ANTIJOIN ALL -> COMPILATION -> TRANSFORMATION
NO_PUSH_SUBQ ALL -> COMPILATION -> TRANSFORMATION
PUSH_SUBQ ALL -> COMPILATION -> TRANSFORMATION
NO_QUERY_TRANSFORMATION ALL -> COMPILATION -> TRANSFORMATION
OPAQUE_XCANONICAL ALL -> COMPILATION -> TRANSFORMATION
OPAQUE_TRANSFORM ALL -> COMPILATION -> TRANSFORMATION
NO_CONNECT_BY_CB_WHR_ONLY ALL -> COMPILATION -> TRANSFORMATION
CONNECT_BY_CB_WHR_ONLY ALL -> COMPILATION -> TRANSFORMATION
NO_TRANSFORM_DISTINCT_AGG ALL -> COMPILATION -> TRANSFORMATION
TRANSFORM_DISTINCT_AGG ALL -> COMPILATION -> TRANSFORMATION
PRECOMPUTE_SUBQUERY ALL -> COMPILATION -> TRANSFORMATION
LIKE_EXPAND ALL -> COMPILATION -> TRANSFORMATION
NO_ORDER_ROLLUPS ALL -> COMPILATION -> TRANSFORMATION
GBY_CONC_ROLLUP ALL -> COMPILATION -> TRANSFORMATION
USE_TTT_FOR_GSETS ALL -> COMPILATION -> TRANSFORMATION
MERGE ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> CVM
NO_MERGE ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> CVM
NO_PLACE_DISTINCT ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> DIST_PLCMT
PLACE_DISTINCT ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> DIST_PLCMT
FACTORIZE_JOIN ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> JOINFAC
NO_FACTORIZE_JOIN ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> JOINFAC
PLACE_GROUP_BY ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PLACE_GROUP_BY
NO_PLACE_GROUP_BY ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PLACE_GROUP_BY
PULL_PRED ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PULL_PRED
NO_PULL_PRED ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> PULL_PRED
NO_FACT ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
NO_STAR_TRANSFORMATION ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
STAR_TRANSFORMATION ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
STAR ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
FACT ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS
TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_TABLE_LOOKUP_BY_NL ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> STAR_TRANS -> TABLE_LOOKUP_BY_NL
NO_EXPAND_TABLE ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> TABLE_EXPANSION
EXPAND_TABLE ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> TABLE_EXPANSION
NO_UNNEST ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> UNNEST
UNNEST ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> UNNEST
NO_COALESCE_SQ ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> COALESCE_SQ
COALESCE_SQ ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> COALESCE_SQ
MERGE ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> CVM
NO_MERGE ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> CVM
NO_PUSH_PRED ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> FILTER_PUSH_PRED
PUSH_PRED ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> FILTER_PUSH_PRED
ELIMINATE_OBY ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OBYE
NO_ELIMINATE_OBY ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OBYE
OLD_PUSH_PRED ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OLD_PUSH_PRED
NO_OUTER_JOIN_TO_INNER ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OUTER_JOIN_TO_INNER
OUTER_JOIN_TO_INNER ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> OUTER_JOIN_TO_INNER
SET_TO_JOIN ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> SET_TO_JOIN
NO_SET_TO_JOIN ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> SET_TO_JOIN
ELIMINATE_JOIN ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> TABLE_ELIM
NO_ELIMINATE_JOIN ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> TABLE_ELIM
NO_UNNEST ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> UNNEST
UNNEST ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> UNNEST
NO_EXPAND ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> USE_CONCAT
USE_CONCAT ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> USE_CONCAT
XML_DML_RWT_STMT ALL -> COMPILATION -> XML_REWRITE
NO_XML_DML_REWRITE ALL -> COMPILATION -> XML_REWRITE
NO_XML_QUERY_REWRITE ALL -> COMPILATION -> XML_REWRITE
FORCE_XML_QUERY_REWRITE ALL -> COMPILATION -> XML_REWRITE
CHECK_ACL_REWRITE ALL -> COMPILATION -> XML_REWRITE -> CHECK_ACL_REWRITE
NO_CHECK_ACL_REWRITE ALL -> COMPILATION -> XML_REWRITE -> CHECK_ACL_REWRITE
NO_COST_XML_QUERY_REWRITE ALL -> COMPILATION -> XML_REWRITE -> COST_XML_QUERY_REWRITE
COST_XML_QUERY_REWRITE ALL -> COMPILATION -> XML_REWRITE -> COST_XML_QUERY_REWRITE
NO_XMLINDEX_REWRITE_IN_SELECT ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
XMLINDEX_REWRITE_IN_SELECT ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
NO_XMLINDEX_REWRITE ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
XMLINDEX_REWRITE ALL -> COMPILATION -> XML_REWRITE -> XMLINDEX_REWRITE
NO_SUBSTRB_PAD ALL -> EXECUTION
NO_NLJ_BATCHING ALL -> EXECUTION
NLJ_BATCHING ALL -> EXECUTION
NO_NLJ_PREFETCH ALL -> EXECUTION
NLJ_PREFETCH ALL -> EXECUTION
CACHE ALL -> EXECUTION
NO_RESULT_CACHE ALL -> EXECUTION
RESULT_CACHE ALL -> EXECUTION
TRACING ALL -> EXECUTION
NOCACHE ALL -> EXECUTION
NO_LOAD ALL -> EXECUTION
305 rows selected.
Lots of hints to remember and to try out some day… nah, I’ll just run on modern Oracle versions and gather the stats properly 😉
覚えて、試すにはちょっとヒントが多すぎる・・・ いやいや、私は新しいバージョンのOracle Databaseで、ちゃんとオプティマイザ統計を収集して《SQLを》実行するだけにしておくよ。