帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い Tweet
癖の多いSQLシリーズネタは沢山あるわけですが、今回は、Oracle/PostgreSQL/MySQLで利用可能なオプティマイザヒントのざっくりしたまとめ。
というか、Advent Calendar向けの準備運動的ななエントリーその１. 意味の同じオプティマイザーヒントを使った何かをネタにしようとしてまーすw（その２は、どうするか検討中w）
各DBの Optimizer Hint (PostgreSQLは、pg_hint_plan拡張機能を利用）の数（特定の最適化の有効化、無効化ヒントはそれぞれ1ヒントとしてカウント。e.g. INDEX, NO_INDEXで2つとカウントする）
Oracle Database 21c : 388
（圧倒的に多いヒント。まあ、Oracle Database 8.1ぐらいからありますからねぇw 様々な最適化を制御できるようになっているということでもあるわけですけども。それにしても多いですね）
PostgreSQL with pg_hint_plan : 26
（pg_hint_planのドキュメントから拾って数得ました。これぐらいなんですね。）
MySQL 8.0.x : 37
（MySQL 8.0の Optimizer Hint には、HASH_JOIN/NO_HASH_JOINのように特定のリリースでしか使えないものなども含まれる）
MySQL系のAurora MySQL compatible, TiDBなどは、独自のヒントをいくつか追加しているものもある。e.g. HASH JOINのBUILD/PROBE表を制御するものなど。。ただし、Aurora MySQLの場合は、Vanila MySQLでHASH_JOINヒントが廃止された影響で、HASH_JOIN_BUILDINGなどの関連ヒントが廃止されるなど、各DB毎に多少温度差のある対応があり、Vanila以外のMySQLを利用する場合には利用可能な Optimizer Hint は個別に調査動作確認しておくことをお勧めする。
参考 Aurora MySQL hints（独自拡張あり）
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Reference.Hints.html
TiDB(独自拡張あり)
https://docs.pingcap.com/ja/tidb/stable/optimizer-hints
YagabyteDB (pg_hint_planを使うようなので、Vanila PostgreSQLと同じ！)
https://docs.yugabyte.com/preview/explore/query-1-performance/pg-hint-plan/
Oracleのマニュアルでは、全量が解説されているわけではないので、v$sql_hintビューを問い合わせた結果も載せておきます。(ドキュメントのリンク貼っても、OracleさんのドキュメントURLって簡単にリンク切れになるのですが、取り敢えず貼っときますw)
YOracle Database 21c SQL Language Reference / Table 2-23 Hints by Functional Category
中には、黒魔術すぎた影響でw 指定しても無視(ヒントレポートでは、常に Error 扱い)されるヒントもあります( 参考: https://blogs.oracle.com/otnjp/post/tsushima-hakushi-11 )
（10gぐらいまでのSQLチューニング経験者なら一度ぐらいは聞いたことがあるかもしれねい、BYPASS_UJVC ヒントが有名ですね. ちなみに、このヒント単体で使うと単純に無視されヒントレーポートにも現れません。なお、v$sql_hintには残されていますw）
DISCUS@ORCLCDB> select * from v$sql_hint order by name;
NAME SQL_FEATURE CLASS INVERSE TARGET_LEVEL PROPERTY VERSION VERSION_OU
---------------------------------------- ------------------------------ ------------------------------ ------------------------------ ------------ ---------- ---------- ----------
ADAPTIVE_PLAN QKSFM_ADAPTIVE_PLAN ADAPTIVE_PLAN NO_ADAPTIVE_PLAN 1 16 12.1.0.2 12.1.0.2
ALL_ROWS QKSFM_ALL_ROWS MODE 1 16 8.1.0 10.2.0.1
ANALYTIC_VIEW_SQL QKSFM_COMPILATION ANALYTIC_VIEW_SQL 2 0 20.1.0
AND_EQUAL QKSFM_AND_EQUAL ACCESS 4 304 8.1.0 8.1.7
ANSI_REARCH QKSFM_ANSI_REARCH ANSI_REARCH NO_ANSI_REARCH 2 16 12.1.0.2 12.1.0.2
ANSWER_QUERY_USING_STATS QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS NO_ANSWER_QUERY_USING_STATS 2 16 18.1.0 18.1.0
ANTIJOIN QKSFM_TRANSFORMATION ANTIJOIN 2 16 9.0.0
APPEND QKSFM_CBO APPEND NOAPPEND 1 0 8.1.0
APPEND_VALUES QKSFM_CBO APPEND_VALUES NOAPPEND 1 0 11.2.0.1
AUTO_REOPTIMIZE QKSFM_AUTO_REOPT AUTO_REOPTIMIZE NO_AUTO_REOPTIMIZE 1 0 12.1.0.1
AV_CACHE QKSFM_EXECUTION AV_CACHE 2 0 18.1.0
BATCH_TABLE_ACCESS_BY_ROWID QKSFM_EXECUTION BATCH_TABLE_ACCESS_BY_ROWID NO_BATCH_TABLE_ACCESS_BY_ROWID 4 272 12.1.0.1 12.1.0.1
BIND_AWARE QKSFM_CURSOR_SHARING BIND_AWARE NO_BIND_AWARE 1 0 11.1.0.7
BITMAP QKSFM_CBO BITMAP 2 256 8.1.0 8.1.5
BITMAP_AND QKSFM_BITMAP_TREE BITMAP_AND 4 48 12.1.0.1 12.1.0.1
BITMAP_TREE QKSFM_BITMAP_TREE ACCESS 4 304 10.2.0.1 10.2.0.1
BUFFER QKSFM_CBO BUFFER NO_BUFFER 2 0 8.1.5
BUSHY_JOIN QKSFM_BUSHY_JOIN BUSHY_JOIN NO_BUSHY_JOIN 2 16 12.2.0.1 12.2.0.1
BYPASS_RECURSIVE_CHECK QKSFM_ALL BYPASS_RECURSIVE_CHECK 2 0 9.0.0
BYPASS_UJVC QKSFM_CBO BYPASS_UJVC 2 0 8.1.5
CACHE QKSFM_EXECUTION CACHE NOCACHE 4 256 8.1.0
CACHE_CB QKSFM_CBO CACHE_CB NOCACHE 4 256 8.1.5
CARDINALITY QKSFM_STATS CARDINALITY 14 272 9.0.0
CHANGE_DUPKEY_ERROR_INDEX QKSFM_DML CHANGE_DUPKEY_ERROR_INDEX 4 288 11.1.0.7
CHECK_ACL_REWRITE QKSFM_CHECK_ACL_REWRITE CHECK_ACL_REWRITE NO_CHECK_ACL_REWRITE 1 0 11.1.0.6
CHOOSE QKSFM_CHOOSE MODE 1 16 8.1.0
CLUSTER QKSFM_CBO ACCESS 4 272 8.0.0 8.1.7
CLUSTERING QKSFM_CLUSTERING CLUSTERING NO_CLUSTERING 1 0 12.1.0.1 12.1.0.1
CLUSTER_BY_ROWID QKSFM_CBO CLUSTER_BY_ROWID NO_CLUSTER_BY_ROWID 4 272 12.1.0.1 12.1.0.1
COALESCE_SQ QKSFM_COALESCE_SQ COALESCE_SQ NO_COALESCE_SQ 2 16 11.2.0.1 11.2.0.1
COLUMN_STATS QKSFM_STATS TABLE_STATS 1 272 10.1.0.3
CONNECT_BY_CB_WHR_ONLY QKSFM_TRANSFORMATION CONNECT_BY_CB_WHR_ONLY NO_CONNECT_BY_CB_WHR_ONLY 2 16 10.2.0.5 10.2.0.5
CONNECT_BY_COMBINE_SW QKSFM_ALL CONNECT_BY_COMBINE_SW NO_CONNECT_BY_COMBINE_SW 2 16 10.2.0.4 10.2.0.4
CONNECT_BY_COST_BASED QKSFM_TRANSFORMATION CONNECT_BY_COST_BASED NO_CONNECT_BY_COST_BASED 2 16 10.2.0.2 10.2.0.2
CONNECT_BY_ELIM_DUPS QKSFM_ALL CONNECT_BY_ELIM_DUPS NO_CONNECT_BY_ELIM_DUPS 2 16 11.2.0.1 11.2.0.1
CONNECT_BY_FILTERING QKSFM_ALL CONNECT_BY_FILTERING NO_CONNECT_BY_FILTERING 2 16 10.2.0.2 10.2.0.2
CONTAINERS QKSFM_ALL CONTAINERS 1 0 12.2.0.1 12.2.0.1
COST_XML_QUERY_REWRITE QKSFM_COST_XML_QUERY_REWRITE COST_XML_QUERY_REWRITE NO_COST_XML_QUERY_REWRITE 1 0 11.1.0.6 11.1.0.6
CPU_COSTING QKSFM_CPU_COSTING CPU_COSTING NO_CPU_COSTING 2 16 9.0.0
CUBE_AJ QKSFM_JOIN_METHOD ANTIJOIN 2 16 12.1.0.1 12.1.0.1
CUBE_GB QKSFM_CBO CUBE_GB 2 0 8.1.5
CUBE_SJ QKSFM_JOIN_METHOD SEMIJOIN 2 16 12.1.0.1 12.1.0.1
CURRENT_INSTANCE QKSFM_ALL CURRENT_INSTANCE 1 0 18.1.0
CURSOR_SHARING_EXACT QKSFM_CBO CURSOR_SHARING_EXACT 2 0 9.0.0
DAGG_OPTIM_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS NO_DAGG_OPTIM_GSETS 2 0 21.1.0 21.1.0
DATA_SECURITY_REWRITE_LIMIT QKSFM_DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT NO_DATA_SECURITY_REWRITE 1 0 12.1.0.1 12.1.0.1
DATA_VALIDATE QKSFM_EXECUTION DATA_VALIDATE 1 0 12.2.0.1
DBMS_STATS QKSFM_DBMS_STATS DBMS_STATS 1 0 10.2.0.1
DB_VERSION QKSFM_ALL DB_VERSION 1 272 11.1.0.6 11.1.0.6
DECORRELATE QKSFM_DECORRELATE DECORRELATE NO_DECORRELATE 2 16 12.1.0.1 12.1.0.1
DENORM_AV QKSFM_COMPILATION DENORM_AV 2 0 20.1.0
DEREF_NO_REWRITE QKSFM_ALL DEREF_NO_REWRITE 1 0 8.1.0
DISABLE_PARALLEL_DML QKSFM_DML ENABLE_PARALLEL_DML ENABLE_PARALLEL_DML 1 0 11.2.0.4
DIST_AGG_PROLLUP_PUSHDOWN QKSFM_PQ DIST_AGG_PROLLUP_PUSHDOWN NO_DIST_AGG_PROLLUP_PUSHDOWN 2 16 12.2.0.1 12.2.0.1
DML_UPDATE QKSFM_CBO DML_UPDATE 1 0 9.0.0
DOMAIN_INDEX_FILTER QKSFM_CBO DOMAIN_INDEX_FILTER NO_DOMAIN_INDEX_FILTER 4 304 11.1.0.6 11.1.0.6
DOMAIN_INDEX_NO_SORT QKSFM_CBO DOMAIN_INDEX_SORT DOMAIN_INDEX_SORT 2 0 8.1.5 10.2.0.1
DOMAIN_INDEX_SORT QKSFM_CBO DOMAIN_INDEX_SORT DOMAIN_INDEX_NO_SORT 2 0 8.1.5 10.2.0.1
DRIVING_SITE QKSFM_ALL DRIVING_SITE 4 256 8.1.0 8.1.7
DST_UPGRADE_INSERT_CONV QKSFM_ALL DST_UPGRADE_INSERT_CONV NO_DST_UPGRADE_INSERT_CONV 1 0 11.2.0.1
DYNAMIC_SAMPLING QKSFM_DYNAMIC_SAMPLING DYNAMIC_SAMPLING 6 272 9.2.0
DYNAMIC_SAMPLING_EST_CDN QKSFM_DYNAMIC_SAMPLING_EST_CDN DYNAMIC_SAMPLING_EST_CDN 4 272 9.2.0
ELIMINATE_JOIN QKSFM_TABLE_ELIM ELIMINATE_JOIN NO_ELIMINATE_JOIN 4 16 10.2.0.1 10.2.0.1
ELIMINATE_OBY QKSFM_OBYE ELIMINATE_OBY NO_ELIMINATE_OBY 2 16 10.2.0.1 10.2.0.1
ELIMINATE_SQ QKSFM_ELIMINATE_SQ ELIMINATE_SQ NO_ELIMINATE_SQ 2 16 12.2.0.1 12.2.0.1
ELIM_GROUPBY QKSFM_TRANSFORMATION ELIM_GROUPBY NO_ELIM_GROUPBY 2 16 12.1.0.2 12.1.0.2
ENABLE_PARALLEL_DML QKSFM_DML ENABLE_PARALLEL_DML DISABLE_PARALLEL_DML 1 0 11.2.0.4
EXPAND_GSET_TO_UNION QKSFM_TRANSFORMATION EXPAND_GSET_TO_UNION NO_EXPAND_GSET_TO_UNION 2 0 9.2.0 10.1.0
EXPAND_TABLE QKSFM_TABLE_EXPANSION EXPAND_TABLE NO_EXPAND_TABLE 4 16 11.2.0.1 11.2.0.1
EXPR_CORR_CHECK QKSFM_CBO EXPR_CORR_CHECK 1 0 8.0.0
FACT QKSFM_STAR_TRANS FACT NO_FACT 4 272 8.1.0 8.1.7
FACTORIZE_JOIN QKSFM_JOINFAC FACTORIZE_JOIN NO_FACTORIZE_JOIN 2 16 11.2.0.1 11.2.0.1
FBTSCAN QKSFM_CBO FBTSCAN 1 0 10.1.0.3
FIRST_ROWS QKSFM_FIRST_ROWS MODE 1 16 8.1.0 10.2.0.1
FORCE_JSON_TABLE_TRANSFORM QKSFM_JSON_REWRITE FORCE_JSON_TABLE_TRANSFORM NO_JSON_TABLE_TRANSFORM 1 0 20.1.0 20.1.0
FORCE_XML_QUERY_REWRITE QKSFM_XML_REWRITE FORCE_XML_QUERY_REWRITE NO_XML_QUERY_REWRITE 1 0 9.2.0 11.1.0.6
FRESH_MV QKSFM_MVIEWS FRESH_MV 1 0 12.2.0.1
FULL QKSFM_FULL ACCESS 4 272 8.1.0 8.1.7
FULL_OUTER_JOIN_TO_OUTER QKSFM_CBO FULL_OUTER_JOIN_TO_OUTER NO_FULL_OUTER_JOIN_TO_OUTER 4 272 11.2.0.3 11.2.0.3
GATHER_OPTIMIZER_STATISTICS QKSFM_DBMS_STATS GATHER_OPTIMIZER_STATISTICS NO_GATHER_OPTIMIZER_STATISTICS 1 0 12.1.0.1
GATHER_PLAN_STATISTICS QKSFM_GATHER_PLAN_STATISTICS GATHER_PLAN_STATISTICS 1 0 10.1.0.3
GBY_CONC_ROLLUP QKSFM_TRANSFORMATION GBY_CONC_ROLLUP 2 0 9.0.0
GBY_PUSHDOWN QKSFM_ALL GBY_PUSHDOWN NO_GBY_PUSHDOWN 2 16 10.2.0.5 10.2.0.5
HASH QKSFM_ALL ACCESS 4 272 8.1.0 8.1.7
HASHSET_BUILD QKSFM_EXECUTION HASHSET_BUILD 2 16 21.1.0 21.1.0
HASH_AJ QKSFM_JOIN_METHOD ANTIJOIN 2 16 8.1.0 8.1.7
HASH_SJ QKSFM_JOIN_METHOD SEMIJOIN 2 16 8.1.0 8.1.7
HWM_BROKERED QKSFM_CBO HWM_BROKERED 2 0 9.0.0
IGNORE_OPTIM_EMBEDDED_HINTS QKSFM_ALL IGNORE_OPTIM_EMBEDDED_HINTS 1 0 10.1.0.3 10.2.0.1
IGNORE_ROW_ON_DUPKEY_INDEX QKSFM_DML IGNORE_ROW_ON_DUPKEY_INDEX 4 288 11.1.0.7
IGNORE_WHERE_CLAUSE QKSFM_ALL IGNORE_WHERE_CLAUSE 1 0 9.2.0
INCLUDE_VERSION QKSFM_ALL INCLUDE_VERSION 1 0 10.1.0.3
INDEX QKSFM_INDEX ACCESS NO_INDEX 4 304 8.0.0 8.1.7
INDEX_ASC QKSFM_INDEX_ASC ACCESS NO_INDEX 4 304 8.1.0
INDEX_COMBINE QKSFM_INDEX_COMBINE ACCESS 4 432 8.1.0 8.1.7
INDEX_DESC QKSFM_INDEX_DESC ACCESS NO_INDEX 4 304 8.1.0 8.1.7
INDEX_FFS QKSFM_INDEX_FFS ACCESS 4 304 8.1.0 8.1.7
INDEX_JOIN QKSFM_INDEX_JOIN ACCESS 4 304 8.1.5 10.1.0.3
INDEX_RRS QKSFM_CBO ACCESS 4 304 9.0.0
INDEX_RS_ASC QKSFM_INDEX_RS_ASC ACCESS 4 304 11.1.0.6 11.1.0.6
INDEX_RS_DESC QKSFM_INDEX_RS_DESC ACCESS 4 304 11.1.0.6 11.1.0.6
INDEX_SS QKSFM_INDEX_SS ACCESS NO_INDEX_SS 4 304 9.0.0 10.2.0.1
INDEX_SS_ASC QKSFM_INDEX_SS_ASC ACCESS NO_INDEX_SS 4 304 9.0.0
INDEX_SS_DESC QKSFM_INDEX_SS_DESC ACCESS NO_INDEX_SS 4 304 9.0.0 10.2.0.1
INDEX_STATS QKSFM_STATS TABLE_STATS 1 272 10.1.0.3
INLINE QKSFM_TRANSFORMATION INLINE MATERIALIZE 2 16 9.0.0 18.1.0
INLINE_XMLTYPE_NT QKSFM_ALL INLINE_XMLTYPE_NT 1 0 10.2.0.1
INMEMORY QKSFM_EXECUTION INMEMORY NO_INMEMORY 6 64 12.1.0.2 12.1.0.2
INMEMORY_PRUNING QKSFM_EXECUTION INMEMORY_PRUNING NO_INMEMORY_PRUNING 6 64 12.1.0.2 12.1.0.2
JSON_LENGTH QKSFM_EXECUTION JSON_LENGTH 1 0 19.1.0
LEADING QKSFM_JOIN_ORDER LEADING 8 272 8.1.6 10.1.0.3
LOCAL_INDEXES QKSFM_CBO LOCAL_INDEXES 2 0 9.0.0
MATERIALIZE QKSFM_TRANSFORMATION INLINE INLINE 2 16 9.0.0 18.1.0
MEMOPTIMIZE_WRITE QKSFM_EXECUTION MEMOPTIMIZE_WRITE 1 0 18.1.0
MERGE QKSFM_CVM MERGE NO_MERGE 6 16 8.1.0 10.1.0
MERGE_AJ QKSFM_JOIN_METHOD ANTIJOIN 2 16 8.1.0 8.1.7
MERGE_CONST_ON QKSFM_CBO MERGE_CONST_ON 1 0 8.0.0
MERGE_SJ QKSFM_JOIN_METHOD SEMIJOIN 2 16 8.1.0 8.1.7
MODEL_COMPILE_SUBQUERY QKSFM_TRANSFORMATION MODEL_COMPILE_SUBQUERY 2 0 10.2.0.1
MODEL_DONTVERIFY_UNIQUENESS QKSFM_TRANSFORMATION MODEL_DONTVERIFY_UNIQUENESS 2 0 10.1.0.3
MODEL_DYNAMIC_SUBQUERY QKSFM_TRANSFORMATION MODEL_DYNAMIC_SUBQUERY 2 0 10.2.0.1
MODEL_MIN_ANALYSIS QKSFM_TRANSFORMATION MODEL_MIN_ANALYSIS 2 0 10.1.0.3
MODEL_NO_ANALYSIS QKSFM_ALL MODEL_MIN_ANALYSIS 2 0 10.1.0.3
MODEL_PUSH_REF QKSFM_TRANSFORMATION MODEL_PUSH_REF NO_MODEL_PUSH_REF 2 0 10.1.0.3
MONITOR QKSFM_ALL MONITOR NO_MONITOR 1 0 11.1.0.6
MV_MERGE QKSFM_TRANSFORMATION MV_MERGE 2 0 9.0.0
NATIVE_FULL_OUTER_JOIN QKSFM_ALL NATIVE_FULL_OUTER_JOIN NO_NATIVE_FULL_OUTER_JOIN 2 16 10.2.0.3 10.2.0.3
NESTED_TABLE_FAST_INSERT QKSFM_ALL NESTED_TABLE_FAST_INSERT 1 0 10.1.0.3
NESTED_TABLE_GET_REFS QKSFM_ALL NESTED_TABLE_GET_REFS 1 0 8.1.0
NESTED_TABLE_SET_SETID QKSFM_ALL NESTED_TABLE_SET_SETID 1 0 8.1.5
NLJ_BATCHING QKSFM_EXECUTION ACCESS NO_NLJ_BATCHING 4 272 11.1.0.6 11.1.0.6
NLJ_PREFETCH QKSFM_EXECUTION NLJ_PREFETCH NO_NLJ_PREFETCH 4 272 11.1.0.6 11.1.0.6
NL_AJ QKSFM_JOIN_METHOD ANTIJOIN 2 16 8.0.0
NL_SJ QKSFM_JOIN_METHOD SEMIJOIN 2 16 8.0.0
NOAPPEND QKSFM_CBO APPEND APPEND 1 0 8.1.0
NOCACHE QKSFM_EXECUTION CACHE CACHE 4 256 8.1.0
NOPARALLEL QKSFM_PARALLEL SHARED SHARED 5 256 8.1.0
NO_ACCESS QKSFM_ALL NO_ACCESS 4 256 8.1.5 8.1.7
NO_ADAPTIVE_PLAN QKSFM_ADAPTIVE_PLAN ADAPTIVE_PLAN ADAPTIVE_PLAN 1 16 12.1.0.2 12.1.0.2
NO_ANSI_REARCH QKSFM_ANSI_REARCH ANSI_REARCH ANSI_REARCH 2 16 12.1.0.2 12.1.0.2
NO_ANSWER_QUERY_USING_STATS QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS 2 16 18.1.0 18.1.0
NO_AUTO_REOPTIMIZE QKSFM_AUTO_REOPT AUTO_REOPTIMIZE AUTO_REOPTIMIZE 1 0 12.1.0.1
NO_BASETABLE_MULTIMV_REWRITE QKSFM_ALL REWRITE REWRITE 2 16 10.1.0.3 10.1.0.3
NO_BATCH_TABLE_ACCESS_BY_ROWID QKSFM_EXECUTION BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID 4 272 12.1.0.1 12.1.0.1
NO_BIND_AWARE QKSFM_CURSOR_SHARING BIND_AWARE BIND_AWARE 1 0 11.1.0.7
NO_BUFFER QKSFM_CBO BUFFER BUFFER 2 0 8.1.5
NO_BUSHY_JOIN QKSFM_BUSHY_JOIN BUSHY_JOIN BUSHY_JOIN 2 16 12.2.0.1 12.2.0.1
NO_CARTESIAN QKSFM_ALL NO_CARTESIAN 4 336 10.2.0.1
NO_CHECK_ACL_REWRITE QKSFM_CHECK_ACL_REWRITE NO_CHECK_ACL_REWRITE CHECK_ACL_REWRITE 1 0 11.1.0.6
NO_CLUSTERING QKSFM_CLUSTERING CLUSTERING CLUSTERING 1 0 12.1.0.1 12.1.0.1
NO_CLUSTER_BY_ROWID QKSFM_CBO CLUSTER_BY_ROWID CLUSTER_BY_ROWID 4 272 12.1.0.1 12.1.0.1
NO_COALESCE_SQ QKSFM_COALESCE_SQ COALESCE_SQ COALESCE_SQ 2 16 11.2.0.1 11.2.0.1
NO_CONNECT_BY_CB_WHR_ONLY QKSFM_TRANSFORMATION CONNECT_BY_CB_WHR_ONLY CONNECT_BY_CB_WHR_ONLY 2 16 10.2.0.5 10.2.0.5
NO_CONNECT_BY_COMBINE_SW QKSFM_ALL CONNECT_BY_COMBINE_SW CONNECT_BY_COMBINE_SW 2 16 10.2.0.4 10.2.0.4
NO_CONNECT_BY_COST_BASED QKSFM_TRANSFORMATION CONNECT_BY_COST_BASED CONNECT_BY_COST_BASED 2 16 10.2.0.2 10.2.0.2
NO_CONNECT_BY_ELIM_DUPS QKSFM_ALL CONNECT_BY_ELIM_DUPS CONNECT_BY_ELIM_DUPS 2 16 11.2.0.1 11.2.0.1
NO_CONNECT_BY_FILTERING QKSFM_ALL CONNECT_BY_FILTERING CONNECT_BY_FILTERING 2 16 10.2.0.2 10.2.0.2
NO_COST_XML_QUERY_REWRITE QKSFM_COST_XML_QUERY_REWRITE NO_COST_XML_QUERY_REWRITE COST_XML_QUERY_REWRITE 1 0 11.1.0.6 11.1.0.6
NO_CPU_COSTING QKSFM_CPU_COSTING CPU_COSTING CPU_COSTING 2 16 9.0.0
NO_DAGG_OPTIM_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS DAGG_OPTIM_GSETS 2 0 21.1.0 21.1.0
NO_DATA_SECURITY_REWRITE QKSFM_DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT DATA_SECURITY_REWRITE_LIMIT 1 0 12.1.0.1 12.1.0.1
NO_DECORRELATE QKSFM_DECORRELATE DECORRELATE DECORRELATE 2 16 12.1.0.1 12.1.0.1
NO_DIST_AGG_PROLLUP_PUSHDOWN QKSFM_PQ DIST_AGG_PROLLUP_PUSHDOWN DIST_AGG_PROLLUP_PUSHDOWN 2 16 12.2.0.1 12.2.0.1
NO_DOMAIN_INDEX_FILTER QKSFM_CBO NO_DOMAIN_INDEX_FILTER DOMAIN_INDEX_FILTER 4 304 11.1.0.6 11.1.0.6
NO_DST_UPGRADE_INSERT_CONV QKSFM_ALL DST_UPGRADE_INSERT_CONV DST_UPGRADE_INSERT_CONV 1 0 11.2.0.1 21.1.0.1
NO_ELIMINATE_JOIN QKSFM_TABLE_ELIM ELIMINATE_JOIN ELIMINATE_JOIN 4 16 10.2.0.1 10.2.0.1
NO_ELIMINATE_OBY QKSFM_OBYE ELIMINATE_OBY ELIMINATE_OBY 2 16 10.2.0.1 10.2.0.1
NO_ELIMINATE_SQ QKSFM_ELIMINATE_SQ ELIMINATE_SQ ELIMINATE_SQ 2 16 12.2.0.1 12.2.0.1
NO_ELIM_GROUPBY QKSFM_TRANSFORMATION ELIM_GROUPBY ELIM_GROUPBY 2 16 12.1.0.2 12.1.0.2
NO_EXPAND QKSFM_USE_CONCAT OR_EXPAND USE_CONCAT 2 16 8.1.0 8.1.7
NO_EXPAND_GSET_TO_UNION QKSFM_TRANSFORMATION EXPAND_GSET_TO_UNION EXPAND_GSET_TO_UNION 2 0 9.2.0 10.1.0
NO_EXPAND_TABLE QKSFM_TABLE_EXPANSION EXPAND_TABLE EXPAND_TABLE 4 16 11.2.0.1 11.2.0.1
NO_FACT QKSFM_STAR_TRANS FACT FACT 4 272 8.1.0 8.1.7
NO_FACTORIZE_JOIN QKSFM_JOINFAC FACTORIZE_JOIN FACTORIZE_JOIN 2 16 11.2.0.1 11.2.0.1
NO_FULL_OUTER_JOIN_TO_OUTER QKSFM_CBO FULL_OUTER_JOIN_TO_OUTER FULL_OUTER_JOIN_TO_OUTER 4 272 11.2.0.3 11.2.0.3
NO_GATHER_OPTIMIZER_STATISTICS QKSFM_DBMS_STATS GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS 1 0 12.1.0.1
NO_GBY_PUSHDOWN QKSFM_ALL GBY_PUSHDOWN GBY_PUSHDOWN 2 16 10.2.0.5 10.2.0.5
NO_INDEX QKSFM_INDEX NO_INDEX INDEX 4 304 8.1.5 8.1.7
NO_INDEX_FFS QKSFM_INDEX_FFS NO_INDEX_FFS INDEX_FFS 4 304 10.1.0.3 10.1.0.3
NO_INDEX_SS QKSFM_INDEX_SS NO_INDEX_SS INDEX_SS 4 304 10.1.0.3 10.1.0.3
NO_INMEMORY QKSFM_EXECUTION INMEMORY INMEMORY 6 64 12.1.0.2 12.1.0.2
NO_INMEMORY_PRUNING QKSFM_EXECUTION INMEMORY_PRUNING INMEMORY_PRUNING 6 64 12.1.0.2 12.1.0.2
NO_JSON_TABLE_TRANSFORM QKSFM_JSON_REWRITE FORCE_JSON_TABLE_TRANSFORM FORCE_JSON_TABLE_TRANSFORM 1 0 20.1.0 20.1.0
NO_LOAD QKSFM_EXECUTION NO_LOAD 1 0 11.1.0.6
NO_MERGE QKSFM_CVM MERGE MERGE 6 16 8.0.0 10.1.0
NO_MODEL_PUSH_REF QKSFM_ALL MODEL_PUSH_REF MODEL_PUSH_REF 2 0 10.1.0.3
NO_MONITOR QKSFM_ALL MONITOR MONITOR 1 0 11.1.0.6
NO_MONITORING QKSFM_ALL NO_MONITORING 1 0 8.0.0
NO_MULTIMV_REWRITE QKSFM_ALL REWRITE REWRITE 2 16 10.1.0.3 10.1.0.3
NO_NATIVE_FULL_OUTER_JOIN QKSFM_ALL NATIVE_FULL_OUTER_JOIN NATIVE_FULL_OUTER_JOIN 2 16 10.2.0.3 10.2.0.3
NO_NLJ_BATCHING QKSFM_EXECUTION ACCESS NLJ_BATCHING 4 272 11.1.0.6 11.1.0.6
NO_NLJ_PREFETCH QKSFM_EXECUTION NLJ_PREFETCH NLJ_PREFETCH 4 272 11.1.0.6 11.1.0.6
NO_OBY_GBYPD_SEPARATE QKSFM_PQ OBY_GBYPD_SEPARATE OBY_GBYPD_SEPARATE 2 16 21.1.0 21.1.0
NO_ORDER_ROLLUPS QKSFM_TRANSFORMATION NO_ORDER_ROLLUPS 2 0 8.0.0
NO_OR_EXPAND QKSFM_CBQT_OR_EXPANSION OR_EXPAND OR_EXPAND 2 16 12.2.0.1 12.2.0.1
NO_OUTER_JOIN_TO_ANTI QKSFM_CBO OUTER_JOIN_TO_ANTI OUTER_JOIN_TO_ANTI 4 272 11.2.0.3 11.2.0.3
NO_OUTER_JOIN_TO_INNER QKSFM_OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER 6 16 11.1.0.6 11.1.0.6
NO_PARALLEL QKSFM_CBO SHARED SHARED 5 256 10.1.0.3
NO_PARALLEL_INDEX QKSFM_PQ PARALLEL_INDEX PARALLEL_INDEX 4 288 8.1.0
NO_PARTIAL_COMMIT QKSFM_CBO NO_PARTIAL_COMMIT 1 0 10.1.0.3
NO_PARTIAL_JOIN QKSFM_PARTIAL_JOIN PARTIAL_JOIN PARTIAL_JOIN 4 272 12.1.0.1 12.1.0.1
NO_PARTIAL_ROLLUP_PUSHDOWN QKSFM_PQ PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN 2 16 12.1.0.1 12.1.0.1
NO_PLACE_DISTINCT QKSFM_DIST_PLCMT PLACE_DISTINCT PLACE_DISTINCT 2 16 11.2.0.1 11.2.0.1
NO_PLACE_GROUP_BY QKSFM_PLACE_GROUP_BY PLACE_GROUP_BY PLACE_GROUP_BY 2 16 11.1.0.6 11.1.0.6
NO_PQ_CONCURRENT_UNION QKSFM_PQ PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION 3 0 12.1.0.1 12.1.0.1
NO_PQ_EXPAND_TABLE QKSFM_TABLE_EXPANSION PQ_EXPAND_TABLE PQ_EXPAND_TABLE 4 16 19.1.0 19.1.0
NO_PQ_NONLEAF_SKEW QKSFM_PQ PQ_NONLEAF_SKEW PQ_NONLEAF_SKEW 4 272 21.1.0 21.1.0
NO_PQ_REPLICATE QKSFM_PQ_REPLICATE PQ_REPLICATE PQ_REPLICATE 4 272 12.1.0.1 12.1.0.1
NO_PQ_SKEW QKSFM_PQ PQ_SKEW PQ_SKEW 4 272 12.1.0.1 12.1.0.1
NO_PRUNE_GSETS QKSFM_TRANSFORMATION NO_PRUNE_GSETS 2 0 9.0.0
NO_PULL_PRED QKSFM_PULL_PRED PULL_PRED PULL_PRED 4 16 10.2.0.1 10.2.0.1
NO_PUSH_HAVING_TO_GBY QKSFM_EXECUTION PUSH_HAVING_TO_GBY PUSH_HAVING_TO_GBY 2 0 18.1.0 18.1.0
NO_PUSH_PRED QKSFM_FILTER_PUSH_PRED PUSH_PRED PUSH_PRED 6 16 8.1.0 8.1.5
NO_PUSH_SUBQ QKSFM_TRANSFORMATION PUSH_SUBQ PUSH_SUBQ 2 16 9.2.0 10.2.0.5
NO_PX_FAULT_TOLERANCE QKSFM_PQ PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE 1 0 12.1.0.1 12.1.0.1
NO_PX_JOIN_FILTER QKSFM_PX_JOIN_FILTER PX_JOIN_FILTER PX_JOIN_FILTER 4 336 10.2.0.1 11.1.0.6
NO_QKN_BUFF QKSFM_CBO NO_QKN_BUFF 2 0 9.2.0
NO_QUERY_TRANSFORMATION QKSFM_TRANSFORMATION NO_QUERY_TRANSFORMATION 1 16 10.1.0.3
NO_REF_CASCADE QKSFM_CBO REF_CASCADE_CURSOR REF_CASCADE_CURSOR 1 0 9.2.0
NO_REORDER_WIF QKSFM_PARTITION REORDER_WIF REORDER_WIF 2 0 18.1.0 18.1.0
NO_RESULT_CACHE QKSFM_EXECUTION RESULT_CACHE RESULT_CACHE 2 0 11.1.0.6
NO_REWRITE QKSFM_TRANSFORMATION REWRITE REWRITE 2 16 8.1.5 8.1.7
NO_SEMIJOIN QKSFM_TRANSFORMATION SEMIJOIN SEMIJOIN 2 16 9.0.0
NO_SEMI_TO_INNER QKSFM_CBO NO_SEMI_TO_INNER SEMI_TO_INNER 4 272 11.2.0.3 11.2.0.3
NO_SET_GBY_PUSHDOWN QKSFM_ALL SET_GBY_PUSHDOWN SET_GBY_PUSHDOWN 2 16 20.1.0 20.1.0
NO_SET_TO_JOIN QKSFM_SET_TO_JOIN SET_TO_JOIN SET_TO_JOIN 2 16 10.1.0.3 10.1.0.3
NO_SQL_TUNE QKSFM_ALL NO_SQL_TUNE 1 0 10.2.0.1
NO_STAR_TRANSFORMATION QKSFM_STAR_TRANS STAR_TRANSFORMATION STAR_TRANSFORMATION 6 16 10.1.0.3 10.1.0.3
NO_STATEMENT_QUEUING QKSFM_PARALLEL STATEMENT_QUEUING STATEMENT_QUEUING 1 0 11.2.0.1
NO_STATS_GSETS QKSFM_ALL NO_STATS_GSETS 2 0 8.0.0
NO_SUBQUERY_PRUNING QKSFM_CBO SUBQUERY_PRUNING SUBQUERY_PRUNING 4 272 11.1.0.6 11.1.0.6
NO_SUBSTRB_PAD QKSFM_EXECUTION NO_SUBSTRB_PAD 1 0 11.2.0.1
NO_SWAP_JOIN_INPUTS QKSFM_CBO SWAP_JOIN_INPUTS SWAP_JOIN_INPUTS 4 272 10.1.0.3 10.1.0.3
NO_TABLE_LOOKUP_BY_NL QKSFM_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL 4 16 11.2.0.2 11.2.0.2
NO_TRANSFORM_DISTINCT_AGG QKSFM_TRANSFORMATION TRANSFORM_DISTINCT_AGG TRANSFORM_DISTINCT_AGG 2 0 11.2.0.1 11.2.0.1
NO_UNNEST QKSFM_UNNEST UNNEST UNNEST 2 16 8.1.6 10.1.0
NO_USE_CUBE QKSFM_USE_CUBE JOIN USE_CUBE 4 336 12.1.0.1 12.1.0.1
NO_USE_DAGG_UNION_ALL_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS USE_DAGG_UNION_ALL_GSETS 2 0 12.2.0.1 12.2.0.1
NO_USE_HASH QKSFM_USE_HASH NO_USE_HASH USE_HASH 4 336 10.1.0.3 10.1.0.3
NO_USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION USE_HASH_AGGREGATION 2 0 10.2.0.1 10.2.0.5
NO_USE_HASH_GBY_FOR_DAGGPSHD QKSFM_ALL USE_HASH_GBY_FOR_DAGGPSHD USE_HASH_GBY_FOR_DAGGPSHD 2 0 12.2.0.1 12.2.0.1
NO_USE_HASH_GBY_FOR_PUSHDOWN QKSFM_ALL USE_HASH_GBY_FOR_PUSHDOWN USE_HASH_GBY_FOR_PUSHDOWN 2 0 11.2.0.2 11.2.0.2
NO_USE_INVISIBLE_INDEXES QKSFM_INDEX USE_INVISIBLE_INDEXES USE_INVISIBLE_INDEXES 1 0 11.1.0.6 11.1.0.6
NO_USE_MERGE QKSFM_USE_MERGE NO_USE_MERGE USE_MERGE 4 336 10.1.0.3 10.1.0.3
NO_USE_NL QKSFM_USE_NL NO_USE_NL USE_NL 4 336 10.1.0.3 10.1.0.3
NO_USE_PARTITION_WISE_DISTINCT QKSFM_PARTITION USE_PARTITION_WISE_DISTINCT USE_PARTITION_WISE_DISTINCT 2 0 12.2.0.1 12.2.0.1
NO_USE_PARTITION_WISE_GBY QKSFM_PARTITION USE_PARTITION_WISE_GBY USE_PARTITION_WISE_GBY 2 0 12.2.0.1 12.2.0.1
NO_USE_PARTITION_WISE_WIF QKSFM_PARTITION USE_PARTITION_WISE_WIF USE_PARTITION_WISE_WIF 2 0 18.1.0 18.1.0
NO_USE_SCALABLE_GBY_INVDIST QKSFM_PQ USE_SCALABLE_GBY_INVDIST USE_SCALABLE_GBY_INVDIST 2 0 19.1.0 19.1.0
NO_USE_VECTOR_AGGREGATION QKSFM_VECTOR_AGG USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION 2 16 12.1.0.2 12.1.0.2
NO_VECTOR_TRANSFORM QKSFM_VECTOR_AGG VECTOR_TRANSFORM VECTOR_TRANSFORM 2 16 12.1.0.2 12.1.0.2
NO_VECTOR_TRANSFORM_DIMS QKSFM_VECTOR_AGG VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS 4 80 12.1.0.2 12.1.0.2
NO_VECTOR_TRANSFORM_FACT QKSFM_VECTOR_AGG VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT 4 80 12.1.0.2 12.1.0.2
NO_XDB_FASTPATH_INSERT QKSFM_ALL XDB_FASTPATH_INSERT XDB_FASTPATH_INSERT 1 0 11.2.0.2
NO_XMLINDEX_REWRITE QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE XMLINDEX_REWRITE 1 0 11.1.0.6 11.1.0.6
NO_XMLINDEX_REWRITE_IN_SELECT QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE XMLINDEX_REWRITE_IN_SELECT 1 0 11.1.0.6 11.1.0.6
NO_XML_DML_REWRITE QKSFM_XML_REWRITE NO_XML_DML_REWRITE 1 0 10.2.0.1 11.1.0.6
NO_XML_QUERY_REWRITE QKSFM_XML_REWRITE FORCE_XML_QUERY_REWRITE FORCE_XML_QUERY_REWRITE 1 0 9.2.0 11.1.0.6
NO_ZONEMAP QKSFM_ZONEMAP ZONEMAP ZONEMAP 4 256 12.1.0.1 12.1.0.1
NUM_INDEX_KEYS QKSFM_CBO ACCESS 4 304 10.2.0.3 10.2.0.3
OBY_GBYPD_SEPARATE QKSFM_PQ OBY_GBYPD_SEPARATE NO_OBY_GBYPD_SEPARATE 2 16 21.1.0 21.1.0
OLD_PUSH_PRED QKSFM_OLD_PUSH_PRED OLD_PUSH_PRED 6 16 10.2.0.1 10.2.0.1
OPAQUE_TRANSFORM QKSFM_TRANSFORMATION OPAQUE_TRANSFORM 1 0 10.1.0.3
OPAQUE_XCANONICAL QKSFM_TRANSFORMATION OPAQUE_XCANONICAL 1 0 10.1.0.3
OPTIMIZER_FEATURES_ENABLE QKSFM_ALL OPTIMIZER_FEATURES_ENABLE 1 272 10.1.0.3 10.2.0.1
OPT_ESTIMATE QKSFM_OPT_ESTIMATE OPT_ESTIMATE 14 272 10.1.0.3
OPT_PARAM QKSFM_ALL OPT_PARAM 1 272 10.2.0.1 10.2.0.1
ORDERED QKSFM_CBO ORDERED 2 16 8.1.0 8.1.7
ORDERED_PREDICATES QKSFM_CBO ORDERED_PREDICATES 2 16 8.0.0
ORDER_KEY_VECTOR_USE QKSFM_VECTOR_AGG ORDER_KEY_VECTOR_USE 2 272 21.1.0 21.1.0
ORDER_SUBQ QKSFM_TRANSFORMATION ORDER_SUBQ 2 16 12.2.0.1 12.2.0.1
OR_EXPAND QKSFM_CBQT_OR_EXPANSION OR_EXPAND NO_OR_EXPAND 2 16 12.2.0.1 12.2.0.1
OSON_GET_CONTENT QKSFM_JSON OSON_GET_CONTENT 1 0 21.1.0 21.1.0
OUTER_JOIN_TO_ANTI QKSFM_CBO OUTER_JOIN_TO_ANTI NO_OUTER_JOIN_TO_ANTI 4 272 11.2.0.3 11.2.0.3
OUTER_JOIN_TO_INNER QKSFM_OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER NO_OUTER_JOIN_TO_INNER 6 16 11.1.0.6 11.1.0.6
OUTLINE QKSFM_ALL OUTLINE 2 0 10.2.0.1 10.2.0.1
OUTLINE_LEAF QKSFM_ALL OUTLINE_LEAF 2 0 10.2.0.1 10.2.0.1
OVERFLOW_NOMOVE QKSFM_CBO OVERFLOW_NOMOVE 2 0 9.0.0
PARALLEL_INDEX QKSFM_PQ PARALLEL_INDEX NO_PARALLEL_INDEX 4 288 8.1.0
PARTIAL_JOIN QKSFM_PARTIAL_JOIN PARTIAL_JOIN NO_PARTIAL_JOIN 4 272 12.1.0.1 12.1.0.1
PARTIAL_ROLLUP_PUSHDOWN QKSFM_PQ PARTIAL_ROLLUP_PUSHDOWN NO_PARTIAL_ROLLUP_PUSHDOWN 2 16 12.1.0.1 12.1.0.1
PDB_LOCAL_ONLY QKSFM_DML PDB_LOCAL_ONLY 1 0 18.1.0
PIV_GB QKSFM_ALL PIV_GB 2 0 8.1.0
PIV_SSF QKSFM_ALL PIV_SSF 2 0 8.1.0
PLACE_DISTINCT QKSFM_DIST_PLCMT PLACE_DISTINCT NO_PLACE_DISTINCT 2 16 11.2.0.1 11.2.0.1
PLACE_GROUP_BY QKSFM_PLACE_GROUP_BY PLACE_GROUP_BY NO_PLACE_GROUP_BY 2 16 11.1.0.6 11.1.0.6
PQ_CONCURRENT_UNION QKSFM_PQ PQ_CONCURRENT_UNION NO_PQ_CONCURRENT_UNION 3 0 12.1.0.1 12.1.0.1
PQ_DISTRIBUTE QKSFM_PQ_DISTRIBUTE PQ_DISTRIBUTE 4 272 8.1.5 8.1.7
PQ_DISTRIBUTE_WINDOW QKSFM_PQ PQ_DISTRIBUTE_WINDOW 2 16 12.1.0.1 12.1.0.1
PQ_EXPAND_TABLE QKSFM_TABLE_EXPANSION PQ_EXPAND_TABLE NO_PQ_EXPAND_TABLE 4 16 19.1.0 19.1.0
PQ_FILTER QKSFM_PQ PQ_FILTER 2 0 12.1.0.1 12.1.0.1
PQ_MAP QKSFM_PQ_MAP PQ_MAP PQ_NOMAP 4 272 9.0.0 10.2.0.1
PQ_NOMAP QKSFM_PQ_MAP PQ_MAP PQ_MAP 4 272 9.0.0 10.2.0.1
PQ_NONLEAF_SKEW QKSFM_PQ PQ_NONLEAF_SKEW NO_PQ_NONLEAF_SKEW 4 272 21.1.0 21.1.0
PQ_REPLICATE QKSFM_PQ_REPLICATE PQ_REPLICATE NO_PQ_REPLICATE 4 272 12.1.0.1 12.1.0.1
PQ_SKEW QKSFM_PQ PQ_SKEW NO_PQ_SKEW 4 272 12.1.0.1 12.1.0.1
PRECOMPUTE_SUBQUERY QKSFM_TRANSFORMATION PRECOMPUTE_SUBQUERY 2 0 10.2.0.1
PRESERVE_OID QKSFM_ALL PRESERVE_OID 1 0 10.2.0.1
PULL_PRED QKSFM_PULL_PRED PULL_PRED NO_PULL_PRED 4 16 10.2.0.1 10.2.0.1
PUSH_HAVING_TO_GBY QKSFM_EXECUTION PUSH_HAVING_TO_GBY NO_PUSH_HAVING_TO_GBY 2 0 18.1.0 18.1.0
PUSH_PRED QKSFM_FILTER_PUSH_PRED PUSH_PRED NO_PUSH_PRED 6 16 8.1.0 8.1.5
PUSH_SUBQ QKSFM_TRANSFORMATION PUSH_SUBQ NO_PUSH_SUBQ 2 16 8.1.0 10.2.0.5
PX_FAULT_TOLERANCE QKSFM_PQ PX_FAULT_TOLERANCE NO_PX_FAULT_TOLERANCE 1 0 12.1.0.1 12.1.0.1
PX_JOIN_FILTER QKSFM_PX_JOIN_FILTER PX_JOIN_FILTER NO_PX_JOIN_FILTER 4 336 10.2.0.1 11.1.0.6
QB_NAME QKSFM_ALL QB_NAME 2 256 10.1.0.3
QUARANTINE QKSFM_EXECUTION QUARANTINE 1 0 19.1.0
QUEUE_CURR QKSFM_CBO ACCESS 4 256 8.0.0
QUEUE_ROWP QKSFM_CBO ACCESS 4 256 8.0.0
RBO_OUTLINE QKSFM_RBO RBO_OUTLINE 1 0 10.2.0.1 10.2.0.1
REF_CASCADE_CURSOR QKSFM_CBO REF_CASCADE_CURSOR NO_REF_CASCADE 1 0 9.2.0
REMOTE_MAPPED QKSFM_ALL REMOTE_MAPPED 2 272 8.1.0
REORDER_WIF QKSFM_PARTITION REORDER_WIF NO_REORDER_WIF 2 0 18.1.0 18.1.0
RESERVOIR_SAMPLING QKSFM_EXECUTION RESERVOIR_SAMPLING 1 0 12.1.0.2
RESTORE_AS_INTERVALS QKSFM_CBO RESTORE_AS_INTERVALS 2 0 8.1.5
RESTRICT_ALL_REF_CONS QKSFM_ALL RESTRICT_ALL_REF_CONS 1 0 10.1.0.3
RESULT_CACHE QKSFM_EXECUTION RESULT_CACHE NO_RESULT_CACHE 2 0 11.1.0.6
RETRY_ON_ROW_CHANGE QKSFM_DML RETRY_ON_ROW_CHANGE 1 0 11.1.0.7
REWRITE QKSFM_TRANSFORMATION REWRITE NO_REWRITE 2 16 8.1.5 8.1.7
REWRITE_OR_ERROR QKSFM_TRANSFORMATION REWRITE 2 0 10.1.0.3
ROWID QKSFM_CBO ACCESS 4 272 8.0.0 8.1.7
RULE QKSFM_RBO MODE 1 16 8.1.0 8.1.5
SAVE_AS_INTERVALS QKSFM_CBO SAVE_AS_INTERVALS 2 0 8.1.5
SCN_ASCENDING QKSFM_ALL SCN_ASCENDING 1 0 8.1.5
SEMIJOIN QKSFM_TRANSFORMATION SEMIJOIN NO_SEMIJOIN 2 16 9.0.0
SEMIJOIN_DRIVER QKSFM_CBO SEMIJOIN_DRIVER 2 16 8.1.0 8.1.7
SEMI_TO_INNER QKSFM_CBO SEMI_TO_INNER NO_SEMI_TO_INNER 4 272 11.2.0.3 11.2.0.3
SET_GBY_PUSHDOWN QKSFM_ALL SET_GBY_PUSHDOWN NO_SET_GBY_PUSHDOWN 2 16 20.1.0 20.1.0
SET_TO_JOIN QKSFM_SET_TO_JOIN SET_TO_JOIN NO_SET_TO_JOIN 2 16 10.1.0.3 10.1.0.3
SHARED QKSFM_PARALLEL SHARED NO_PARALLEL 5 256 8.1.0
SKIP_EXT_OPTIMIZER QKSFM_CBO SKIP_EXT_OPTIMIZER 2 16 9.0.0
SKIP_PROXY QKSFM_ALL SKIP_PROXY 1 0 18.1.0
SKIP_UNQ_UNUSABLE_IDX QKSFM_CBO SKIP_UNQ_UNUSABLE_IDX 1 0 10.1.0.3
SQLLDR QKSFM_CBO SQLLDR 1 0 9.0.0
SQL_SCOPE QKSFM_COMPILATION SQL_SCOPE 1 0 12.2.0.1
STAR QKSFM_STAR_TRANS STAR 2 16 8.1.0
STAR_TRANSFORMATION QKSFM_STAR_TRANS STAR_TRANSFORMATION NO_STAR_TRANSFORMATION 6 16 8.1.0 8.1.7
STATEMENT_QUEUING QKSFM_PARALLEL STATEMENT_QUEUING NO_STATEMENT_QUEUING 1 0 11.2.0.1
STREAMS QKSFM_CBO STREAMS 1 0 10.1.0.3
SUBQUERY_PRUNING QKSFM_CBO SUBQUERY_PRUNING NO_SUBQUERY_PRUNING 4 272 11.1.0.6 11.1.0.6
SUPPRESS_LOAD QKSFM_DDL SUPPRESS_LOAD 1 0 18.1.0
SWAP_JOIN_INPUTS QKSFM_CBO SWAP_JOIN_INPUTS NO_SWAP_JOIN_INPUTS 4 272 8.1.0 8.1.7
SYSTEM_STATS QKSFM_ALL SYSTEM_STATS 1 272 18.1.0
SYS_DL_CURSOR QKSFM_CBO SYS_DL_CURSOR 1 0 9.2.0
SYS_PARALLEL_TXN QKSFM_CBO SYS_PARALLEL_TXN 2 0 8.1.6
SYS_RID_ORDER QKSFM_ALL SYS_RID_ORDER 2 0 9.2.0
TABLE_LOOKUP_BY_NL QKSFM_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL NO_TABLE_LOOKUP_BY_NL 4 16 11.2.0.2 11.2.0.2
TABLE_STATS QKSFM_STATS TABLE_STATS 1 272 10.1.0.3
TIV_GB QKSFM_ALL PIV_GB 2 0 8.1.0
TIV_SSF QKSFM_ALL PIV_SSF 2 0 8.1.0
TRACING QKSFM_EXECUTION TRACING 1 0 10.1.0.3
TRANSFORM_DISTINCT_AGG QKSFM_TRANSFORMATION TRANSFORM_DISTINCT_AGG NO_TRANSFORM_DISTINCT_AGG 2 0 11.2.0.1 11.2.0.1
UNNEST QKSFM_UNNEST UNNEST NO_UNNEST 2 16 8.1.6 10.1.0
USE_ANTI QKSFM_CBO USE_ANTI 4 272 8.1.0
USE_CONCAT QKSFM_USE_CONCAT OR_EXPAND NO_EXPAND 2 16 8.1.0 8.1.7
USE_CUBE QKSFM_USE_CUBE JOIN NO_USE_CUBE 4 336 12.1.0.1 12.1.0.1
USE_DAGG_UNION_ALL_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS NO_USE_DAGG_UNION_ALL_GSETS 2 0 12.2.0.1 12.2.0.1
USE_HASH QKSFM_USE_HASH JOIN NO_USE_HASH 4 464 8.1.0 8.1.7
USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION NO_USE_HASH_AGGREGATION 2 0 10.2.0.1 10.2.0.5
USE_HASH_GBY_FOR_DAGGPSHD QKSFM_ALL USE_HASH_GBY_FOR_DAGGPSHD NO_USE_HASH_GBY_FOR_DAGGPSHD 2 0 12.2.0.1 12.2.0.1
USE_HASH_GBY_FOR_PUSHDOWN QKSFM_ALL USE_HASH_GBY_FOR_PUSHDOWN NO_USE_HASH_GBY_FOR_PUSHDOWN 2 0 11.2.0.2 11.2.0.2
USE_HIDDEN_PARTITIONS QKSFM_PARTITION USE_HIDDEN_PARTITIONS 2 0 12.1.0.1
USE_INVISIBLE_INDEXES QKSFM_INDEX USE_INVISIBLE_INDEXES NO_USE_INVISIBLE_INDEXES 1 0 11.1.0.6 11.1.0.6
USE_MERGE QKSFM_USE_MERGE JOIN NO_USE_MERGE 4 336 8.1.0 8.1.7
USE_MERGE_CARTESIAN QKSFM_USE_MERGE_CARTESIAN JOIN 4 336 11.1.0.6 11.1.0.6
USE_NL QKSFM_USE_NL JOIN NO_USE_NL 4 336 8.1.0 8.1.7
USE_NL_WITH_INDEX QKSFM_USE_NL_WITH_INDEX USE_NL_WITH_INDEX NO_USE_NL 4 304 10.1.0.3
USE_PARTITION_WISE_DISTINCT QKSFM_PARTITION USE_PARTITION_WISE_DISTINCT NO_USE_PARTITION_WISE_DISTINCT 2 0 12.2.0.1 12.2.0.1
USE_PARTITION_WISE_GBY QKSFM_PARTITION USE_PARTITION_WISE_GBY NO_USE_PARTITION_WISE_GBY 2 0 12.2.0.1 12.2.0.1
USE_PARTITION_WISE_WIF QKSFM_PARTITION USE_PARTITION_WISE_WIF NO_USE_PARTITION_WISE_WIF 2 0 18.1.0 18.1.0
USE_SCALABLE_GBY_INVDIST QKSFM_PQ USE_SCALABLE_GBY_INVDIST NO_USE_SCALABLE_GBY_INVDIST 2 0 19.1.0 19.1.0
USE_SEMI QKSFM_CBO USE_SEMI 4 272 8.1.0
USE_TTT_FOR_GSETS QKSFM_TRANSFORMATION USE_TTT_FOR_GSETS 2 0 9.0.0
USE_VECTOR_AGGREGATION QKSFM_VECTOR_AGG USE_VECTOR_AGGREGATION NO_USE_VECTOR_AGGREGATION 2 16 12.1.0.2 12.1.0.2
USE_WEAK_NAME_RESL QKSFM_ALL USE_WEAK_NAME_RESL 1 0 10.1.0.3
VECTOR_READ QKSFM_CBO VECTOR_READ 1 0 10.1.0.3
VECTOR_READ_TRACE QKSFM_CBO VECTOR_READ_TRACE 1 0 10.1.0.3
VECTOR_TRANSFORM QKSFM_VECTOR_AGG VECTOR_TRANSFORM NO_VECTOR_TRANSFORM 2 16 12.1.0.2 12.1.0.2
VECTOR_TRANSFORM_DIMS QKSFM_VECTOR_AGG VECTOR_TRANSFORM_DIMS NO_VECTOR_TRANSFORM_DIMS 4 80 12.1.0.2 12.1.0.2
VECTOR_TRANSFORM_FACT QKSFM_VECTOR_AGG VECTOR_TRANSFORM_FACT NO_VECTOR_TRANSFORM_FACT 4 80 12.1.0.2 12.1.0.2
WITH_PLSQL QKSFM_ALL WITH_PLSQL 1 0 12.1.0.1
XDB_FASTPATH_INSERT QKSFM_ALL XDB_FASTPATH_INSERT NO_XDB_FASTPATH_INSERT 1 0 11.2.0.2
XMLINDEX_REWRITE QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE NO_XMLINDEX_REWRITE 1 0 11.1.0.6 11.1.0.6
XMLINDEX_REWRITE_IN_SELECT QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE NO_XMLINDEX_REWRITE_IN_SELECT 1 0 11.1.0.6 11.1.0.6
XMLINDEX_SEL_IDX_TBL QKSFM_ALL XMLINDEX_SEL_IDX_TBL 1 0 11.2.0.1
XMLTSET_DML_ENABLE QKSFM_ALL XMLTSET_DML_ENABLE 1 0 12.2.0.1
XML_DML_RWT_STMT QKSFM_XML_REWRITE XML_DML_RWT_STMT 1 0 11.1.0.6 11.1.0.6
X_DYN_PRUNE QKSFM_CBO X_DYN_PRUNE 2 0 10.1.0.3
ZONEMAP QKSFM_ZONEMAP ZONEMAP NO_ZONEMAP 4 256 12.1.0.1 12.1.0.1
388行が選択されました。
PostgreSQL / pg_hint_plan (22-Nov-2023時点）
pg_hint_planのヒントもOracleの影響を受けているところもあるけども、PostgreSQL独特の言い回しを使う傾向はありますよね。面白いのは、LEADINGヒントで（a b）みたいなペアで優先度を記述する部分。これかなり慣れが必要な気がします。
https://github.com/ossc-db/pg_hint_plan
https://github.com/ossc-db/pg_hint_plan/blob/master/docs/hint_list.md
Hint list
| Format | Description | |
|---|---|---|
| Scan method | SeqScan(table) |
Forces sequential scan on the table. |
TidScan(table) |
Forces TID scan on the table. | |
IndexScan(table[ index...]) |
Forces index scan on the table. Restricts to specified indexes if any. | |
IndexOnlyScan(table[ index...]) |
Forces index-only scan on the table. Restricts to specified indexes if any. Index scan may be used if index-only scan is not available. | |
BitmapScan(table[ index...]) |
Forces bitmap scan on the table. Restricts to specified indexes if any. | |
IndexScanRegexp(table[ POSIX Regexp...])IndexOnlyScanRegexp(table[ POSIX Regexp...])BitmapScanRegexp(table[ POSIX Regexp...]) |
Forces index scan, index-only scan (For PostgreSQL 9.2 and later) or bitmap scan on the table. Restricts to indexes that matches the specified POSIX regular expression pattern. | |
NoSeqScan(table) |
Forces to not do sequential scan on the table. | |
NoTidScan(table) |
Forces to not do TID scan on the table. | |
NoIndexScan(table) |
Forces to not do index scan and index-only scan on the table. | |
NoIndexOnlyScan(table) |
Forces to not do index only scan on the table. | |
NoBitmapScan(table) |
Forces to not do bitmap scan on the table. | |
| Join method | NestLoop(table table[ table...]) |
Forces nested loop for the joins on the tables specified. |
HashJoin(table table[ table...]) |
Forces hash join for the joins on the tables specified. | |
MergeJoin(table table[ table...]) |
Forces merge join for the joins on the tables specified. | |
NoNestLoop(table table[ table...]) |
Forces to not do nested loop for the joins on the tables specified. | |
NoHashJoin(table table[ table...]) |
Forces to not do hash join for the joins on the tables specified. | |
NoMergeJoin(table table[ table...]) |
Forces to not do merge join for the joins on the tables specified. | |
| Join order | Leading(table table[ table...]) |
Forces join order as specified. |
Leading(<join pair>) |
Forces join order and directions as specified. A join pair is a pair of tables and/or other join pairs enclosed by parentheses, which can make a nested structure. | |
| Behavior control on Join | Memoize(table table[ table...]) |
Allows the topmost join of a join among the specified tables to Memoize the inner result. Not enforced. |
NoMemoize(table table[ table...]) |
Inhibits the topmost join of a join among the specified tables from Memoizing the inner result. | |
| Row number correction | Rows(table table[ table...] correction) |
Corrects row number of a result of the joins on the tables specified. The available correction methods are absolute (#), addition (+), subtract (-) and multiplication (*). should be a string that strtod() can understand. |
| Parallel query configuration | Parallel(table <# of workers> [soft|hard]) |
Enforces or inhibits parallel execution of the specified table. <# of workers> is the desired number of parallel workers, where zero means inhibiting parallel execution. If the third parameter is soft (default), it just changes max_parallel_workers_per_gather and leaves everything else to the planner. Hard enforces the specified number of workers. |
| GUC | Set(GUC-param value) |
Sets GUC parameter to the value defined while planner is running. |
MySQL 8.0
https://dev.mysql.com/doc/refman/8.0/en/optimizer-hints.html
Table 8.2 Optimizer Hints Available
MySQLのOptimizer HintsはOracleにもある同一用途のヒントもちらほら。ヒント名も同一で:)
| Hint Name | Description | Applicable Scopes |
|---|---|---|
BKA, NO_BKA |
Affects Batched Key Access join processing | Query block, table |
BNL, NO_BNL |
Prior to MySQL 8.0.20: affects Block Nested-Loop join processing; MySQL 8.0.18 and later: also affects hash join optimization; MySQL 8.0.20 and later: affects hash join optimization only | Query block, table |
DERIVED_CONDITION_PUSHDOWN, NO_DERIVED_CONDITION_PUSHDOWN |
Use or ignore the derived condition pushdown optimization for materialized derived tables (Added in MySQL 8.0.22) | Query block, table |
GROUP_INDEX, NO_GROUP_INDEX |
Use or ignore the specified index or indexes for index scans in GROUP BY operations (Added in MySQL 8.0.20) |
Index |
HASH_JOIN, NO_HASH_JOIN |
Affects Hash Join optimization (MySQL 8.0.18 only | Query block, table |
INDEX, NO_INDEX |
Acts as the combination of JOIN_INDEX, GROUP_INDEX, and ORDER_INDEX, or as the combination of NO_JOIN_INDEX, NO_GROUP_INDEX, and NO_ORDER_INDEX (Added in MySQL 8.0.20) |
Index |
INDEX_MERGE, NO_INDEX_MERGE |
Affects Index Merge optimization | Table, index |
JOIN_FIXED_ORDER |
Use table order specified in FROM clause for join order |
Query block |
JOIN_INDEX, NO_JOIN_INDEX |
Use or ignore the specified index or indexes for any access method (Added in MySQL 8.0.20) | Index |
JOIN_ORDER |
Use table order specified in hint for join order | Query block |
JOIN_PREFIX |
Use table order specified in hint for first tables of join order | Query block |
JOIN_SUFFIX |
Use table order specified in hint for last tables of join order | Query block |
MAX_EXECUTION_TIME |
Limits statement execution time | Global |
MERGE, NO_MERGE |
Affects derived table/view merging into outer query block | Table |
MRR, NO_MRR |
Affects Multi-Range Read optimization | Table, index |
NO_ICP |
Affects Index Condition Pushdown optimization | Table, index |
NO_RANGE_OPTIMIZATION |
Affects range optimization | Table, index |
ORDER_INDEX, NO_ORDER_INDEX |
Use or ignore the specified index or indexes for sorting rows (Added in MySQL 8.0.20) | Index |
QB_NAME |
Assigns name to query block | Query block |
RESOURCE_GROUP |
Set resource group during statement execution | Global |
SEMIJOIN, NO_SEMIJOIN |
Affects semijoin strategies; beginning with MySQL 8.0.17, this also applies to antijoins | Query block |
SKIP_SCAN, NO_SKIP_SCAN |
Affects Skip Scan optimization | Table, index |
SET_VAR |
Set variable during statement execution | Global |
SUBQUERY |
Affects materialization, IN-to-EXISTS subquery strategies |
Query block |
いよいよ、Advent Calendar 2023の季節が近くなってきました。今年は、全部俺シリーズは、やらずに、Oracle/PostgreSQL/MySQLのカレンダーへクロスポストすることだけは決めてます〜 :)
全部俺では無いけど、いくつかエントリーは書く予定ではいます。このエントリーもAdvent Calendarネタへ繋がるネタなのですけどね。w
では、また。
関連エントリー
・Oracle Database 20c 20.1.0以降〜21c 21.1.0で v$sql_hintに追加されたヒント/ FAQ
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺 #11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺 #12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺 #14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺 #16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺 #17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺 #18 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺 #20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺 #25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺 おまけ SQL de 湯婆婆やるにも癖がでるw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
| 固定リンク | 0
コメント