select statement joines v$sql_hint to v$sql_feature_hierarchy so that each SQL hint is shown within the hierarchy it belong to. with rec (feature, lvl) as (
select
sql_feature as feature,
0 as lvl
from
v$sql_feature_hierarchy
where
parent_id is null and
sql_feature in (select parent_id from v$sql_feature_hierarchy where parent_id is not null)
UNION ALL
select
hir.sql_feature as feature,
rec.lvl + 1 as lvl
from
rec join
v$sql_feature_hierarchy hir on rec.feature = hir.parent_id
)
search depth first by feature set order_by,
b as (
select
case when nvl(lag(feature) over (order by order_by, hin.class nulls last, hin.name), chr(0)) != feature then 1 end new_feature,
rec.feature,
fet.description,
hin.name hint_name,
hin.class hint_class,
hin.version hint_version,
rec.order_by,
rec.lvl
from
rec join
v$sql_feature fet on rec.feature = fet.sql_feature join
v$sql_hint hin on rec.feature = hin.sql_feature
)
select
case when new_feature = 1 then rpad(' ', lvl*3 ) || replace(feature, 'QKSFM_', '') end feature,
case when new_feature = 1 then description end description,
hint_name,
hint_class,
hint_version
from
b
order by
order_by,
hint_class nulls last,
hint_name;
ALL A Universal Feature HASH ACCESS 8.1.0
BYPASS_RECURSIVE_CHECK BYPASS_RECURSIVE_CHECK 9.0.0
CONNECT_BY_COMBINE_SW CONNECT_BY_COMBINE_SW 10.2.0.4
NO_CONNECT_BY_COMBINE_SW CONNECT_BY_COMBINE_SW 10.2.0.4
CONNECT_BY_ELIM_DUPS CONNECT_BY_ELIM_DUPS 11.2.0.1
NO_CONNECT_BY_ELIM_DUPS CONNECT_BY_ELIM_DUPS 11.2.0.1
CONNECT_BY_FILTERING CONNECT_BY_FILTERING 10.2.0.2
NO_CONNECT_BY_FILTERING CONNECT_BY_FILTERING 10.2.0.2
CONTAINERS CONTAINERS 12.2.0.1
CURRENT_INSTANCE CURRENT_INSTANCE 18.1.0
DB_VERSION DB_VERSION 11.1.0.6
DEREF_NO_REWRITE DEREF_NO_REWRITE 8.1.0
DRIVING_SITE DRIVING_SITE 8.1.0
DST_UPGRADE_INSERT_CONV DST_UPGRADE_INSERT_CONV 11.2.0.1
NO_DST_UPGRADE_INSERT_CONV DST_UPGRADE_INSERT_CONV 11.2.0.1
GBY_PUSHDOWN GBY_PUSHDOWN 10.2.0.5
NO_GBY_PUSHDOWN GBY_PUSHDOWN 10.2.0.5
IGNORE_OPTIM_EMBEDDED_HINTS IGNORE_OPTIM_EMBEDDED_HINTS 10.1.0.3
IGNORE_WHERE_CLAUSE IGNORE_WHERE_CLAUSE 9.2.0
INCLUDE_VERSION INCLUDE_VERSION 10.1.0.3
INLINE_XMLTYPE_NT INLINE_XMLTYPE_NT 10.2.0.1
MODEL_NO_ANALYSIS MODEL_MIN_ANALYSIS 10.1.0.3
NO_MODEL_PUSH_REF MODEL_PUSH_REF 10.1.0.3
MONITOR MONITOR 11.1.0.6
NO_MONITOR MONITOR 11.1.0.6
NATIVE_FULL_OUTER_JOIN NATIVE_FULL_OUTER_JOIN 10.2.0.3
NO_NATIVE_FULL_OUTER_JOIN NATIVE_FULL_OUTER_JOIN 10.2.0.3
NESTED_TABLE_FAST_INSERT NESTED_TABLE_FAST_INSERT 10.1.0.3
NESTED_TABLE_GET_REFS NESTED_TABLE_GET_REFS 8.1.0
NESTED_TABLE_SET_SETID NESTED_TABLE_SET_SETID 8.1.5
NO_ACCESS NO_ACCESS 8.1.5
NO_CARTESIAN NO_CARTESIAN 10.2.0.1
NO_MONITORING NO_MONITORING 8.0.0
NO_SQL_TUNE NO_SQL_TUNE 10.2.0.1
NO_STATS_GSETS NO_STATS_GSETS 8.0.0
OPTIMIZER_FEATURES_ENABLE OPTIMIZER_FEATURES_ENABLE 10.1.0.3
OPT_PARAM OPT_PARAM 10.2.0.1
OUTLINE OUTLINE 10.2.0.1
OUTLINE_LEAF OUTLINE_LEAF 10.2.0.1
PIV_GB PIV_GB 8.1.0
TIV_GB PIV_GB 8.1.0
PIV_SSF PIV_SSF 8.1.0
TIV_SSF PIV_SSF 8.1.0
PRESERVE_OID PRESERVE_OID 10.2.0.1
QB_NAME QB_NAME 10.1.0.3
REMOTE_MAPPED REMOTE_MAPPED 8.1.0
RESTRICT_ALL_REF_CONS RESTRICT_ALL_REF_CONS 10.1.0.3
NO_BASETABLE_MULTIMV_REWRITE REWRITE 10.1.0.3
NO_MULTIMV_REWRITE REWRITE 10.1.0.3
SCN_ASCENDING SCN_ASCENDING 8.1.5
SKIP_PROXY SKIP_PROXY 18.1.0
SYSTEM_STATS SYSTEM_STATS 18.1.0
SYS_RID_ORDER SYS_RID_ORDER 9.2.0
NO_USE_HASH_AGGREGATION USE_HASH_AGGREGATION 10.2.0.1
USE_HASH_AGGREGATION USE_HASH_AGGREGATION 10.2.0.1
NO_USE_HASH_GBY_FOR_DAGGPSHD USE_HASH_GBY_FOR_DAGGPSHD 12.2.0.1
USE_HASH_GBY_FOR_DAGGPSHD USE_HASH_GBY_FOR_DAGGPSHD 12.2.0.1
NO_USE_HASH_GBY_FOR_PUSHDOWN USE_HASH_GBY_FOR_PUSHDOWN 11.2.0.2
USE_HASH_GBY_FOR_PUSHDOWN USE_HASH_GBY_FOR_PUSHDOWN 11.2.0.2
USE_WEAK_NAME_RESL USE_WEAK_NAME_RESL 10.1.0.3
WITH_PLSQL WITH_PLSQL 12.1.0.1
NO_XDB_FASTPATH_INSERT XDB_FASTPATH_INSERT 11.2.0.2
XDB_FASTPATH_INSERT XDB_FASTPATH_INSERT 11.2.0.2
XMLINDEX_SEL_IDX_TBL XMLINDEX_SEL_IDX_TBL 11.2.0.1
XMLTSET_DML_ENABLE XMLTSET_DML_ENABLE 12.2.0.1
ANSI_REARCH ANSI rearcihtecture ANSI_REARCH ANSI_REARCH 12.1.0.2
NO_ANSI_REARCH ANSI_REARCH 12.1.0.2
COMPILATION SQL COMPILATION SQL_SCOPE SQL_SCOPE 12.2.0.1
CBO SQL Cost Based Optimization CLUSTER ACCESS 8.0.0
INDEX_RRS ACCESS 9.0.0
NUM_INDEX_KEYS ACCESS 10.2.0.3
QUEUE_CURR ACCESS 8.0.0
QUEUE_ROWP ACCESS 8.0.0
ROWID ACCESS 8.0.0
APPEND APPEND 8.1.0
NOAPPEND APPEND 8.1.0
APPEND_VALUES APPEND_VALUES 11.2.0.1
BITMAP BITMAP 8.1.0
BUFFER BUFFER 8.1.5
NO_BUFFER BUFFER 8.1.5
BYPASS_UJVC BYPASS_UJVC 8.1.5
CACHE_CB CACHE_CB 8.1.5
CLUSTER_BY_ROWID CLUSTER_BY_ROWID 12.1.0.1
NO_CLUSTER_BY_ROWID CLUSTER_BY_ROWID 12.1.0.1
CUBE_GB CUBE_GB 8.1.5
CURSOR_SHARING_EXACT CURSOR_SHARING_EXACT 9.0.0
DML_UPDATE DML_UPDATE 9.0.0
DOMAIN_INDEX_FILTER DOMAIN_INDEX_FILTER 11.1.0.6
DOMAIN_INDEX_NO_SORT DOMAIN_INDEX_SORT 8.1.5
DOMAIN_INDEX_SORT DOMAIN_INDEX_SORT 8.1.5
EXPR_CORR_CHECK EXPR_CORR_CHECK 8.0.0
FBTSCAN FBTSCAN 10.1.0.3
FULL_OUTER_JOIN_TO_OUTER FULL_OUTER_JOIN_TO_OUTER 11.2.0.3
NO_FULL_OUTER_JOIN_TO_OUTER FULL_OUTER_JOIN_TO_OUTER 11.2.0.3
HWM_BROKERED HWM_BROKERED 9.0.0
LOCAL_INDEXES LOCAL_INDEXES 9.0.0
MERGE_CONST_ON MERGE_CONST_ON 8.0.0
NO_DOMAIN_INDEX_FILTER NO_DOMAIN_INDEX_FILTER 11.1.0.6
NO_PARTIAL_COMMIT NO_PARTIAL_COMMIT 10.1.0.3
NO_QKN_BUFF NO_QKN_BUFF 9.2.0
NO_SEMI_TO_INNER NO_SEMI_TO_INNER 11.2.0.3
ORDERED ORDERED 8.1.0
ORDERED_PREDICATES ORDERED_PREDICATES 8.0.0
NO_OUTER_JOIN_TO_ANTI OUTER_JOIN_TO_ANTI 11.2.0.3
OUTER_JOIN_TO_ANTI OUTER_JOIN_TO_ANTI 11.2.0.3
OVERFLOW_NOMOVE OVERFLOW_NOMOVE 9.0.0
NO_REF_CASCADE REF_CASCADE_CURSOR 9.2.0
REF_CASCADE_CURSOR REF_CASCADE_CURSOR 9.2.0
RESTORE_AS_INTERVALS RESTORE_AS_INTERVALS 8.1.5
SAVE_AS_INTERVALS SAVE_AS_INTERVALS 8.1.5
SEMIJOIN_DRIVER SEMIJOIN_DRIVER 8.1.0
SEMI_TO_INNER SEMI_TO_INNER 11.2.0.3
NO_PARALLEL SHARED 10.1.0.3
SKIP_EXT_OPTIMIZER SKIP_EXT_OPTIMIZER 9.0.0
SKIP_UNQ_UNUSABLE_IDX SKIP_UNQ_UNUSABLE_IDX 10.1.0.3
SQLLDR SQLLDR 9.0.0
STREAMS STREAMS 10.1.0.3
NO_SUBQUERY_PRUNING SUBQUERY_PRUNING 11.1.0.6
SUBQUERY_PRUNING SUBQUERY_PRUNING 11.1.0.6
NO_SWAP_JOIN_INPUTS SWAP_JOIN_INPUTS 10.1.0.3
SWAP_JOIN_INPUTS SWAP_JOIN_INPUTS 8.1.0
SYS_DL_CURSOR SYS_DL_CURSOR 9.2.0
SYS_PARALLEL_TXN SYS_PARALLEL_TXN 8.1.6
USE_ANTI USE_ANTI 8.1.0
USE_SEMI USE_SEMI 8.1.0
VECTOR_READ VECTOR_READ 10.1.0.3
VECTOR_READ_TRACE VECTOR_READ_TRACE 10.1.0.3
X_DYN_PRUNE X_DYN_PRUNE 10.1.0.3
AND_EQUAL Index and-equal access path AND_EQUAL ACCESS 8.1.0
BITMAP_TREE Bitmap tree access path BITMAP_TREE ACCESS 10.2.0.1
BITMAP_AND BITMAP_AND 12.1.0.1
FULL Full table scan FULL ACCESS 8.1.0
INDEX Index INDEX ACCESS 8.0.0
NO_INDEX NO_INDEX 8.1.5
NO_USE_INVISIBLE_INDEXES USE_INVISIBLE_INDEXES 11.1.0.6
USE_INVISIBLE_INDEXES USE_INVISIBLE_INDEXES 11.1.0.6
INDEX_ASC Index (ascending) INDEX_ASC ACCESS 8.1.0
INDEX_COMBINE Combine index for bitmap access INDEX_COMBINE ACCESS 8.1.0
INDEX_DESC Use index (descending) INDEX_DESC ACCESS 8.1.0
INDEX_FFS Index fast full scan INDEX_FFS ACCESS 8.1.0
NO_INDEX_FFS NO_INDEX_FFS 10.1.0.3
INDEX_JOIN Index join INDEX_JOIN ACCESS 8.1.5
INDEX_RS_ASC Index range scan INDEX_RS_ASC ACCESS 11.1.0.6
INDEX_RS_DESC Index range scan descending INDEX_RS_DESC ACCESS 11.1.0.6
INDEX_SS Index skip scan INDEX_SS ACCESS 9.0.0
NO_INDEX_SS NO_INDEX_SS 10.1.0.3
INDEX_SS_ASC Index skip scan ascending INDEX_SS_ASC ACCESS 9.0.0
INDEX_SS_DESC Index skip scan descending INDEX_SS_DESC ACCESS 9.0.0
ADAPTIVE_PLAN Adaptive plans ADAPTIVE_PLAN ADAPTIVE_PLAN 12.1.0.2
NO_ADAPTIVE_PLAN ADAPTIVE_PLAN 12.1.0.2
AUTO_REOPT Automatic reoptimization AUTO_REOPTIMIZE AUTO_REOPTIMIZE 12.1.0.1
NO_AUTO_REOPTIMIZE AUTO_REOPTIMIZE 12.1.0.1
BUSHY_JOIN bushy join BUSHY_JOIN BUSHY_JOIN 12.2.0.1
NO_BUSHY_JOIN BUSHY_JOIN 12.2.0.1
CBQT_OR_EXPANSION Cost Based OR Expansion NO_OR_EXPAND OR_EXPAND 12.2.0.1
OR_EXPAND OR_EXPAND 12.2.0.1
CVM Complex View Merging MERGE MERGE 8.1.0
NO_MERGE MERGE 8.0.0
DIST_PLCMT Distinct Placement NO_PLACE_DISTINCT PLACE_DISTINCT 11.2.0.1
PLACE_DISTINCT PLACE_DISTINCT 11.2.0.1
JOINFAC Join Factorization FACTORIZE_JOIN FACTORIZE_JOIN 11.2.0.1
NO_FACTORIZE_JOIN FACTORIZE_JOIN 11.2.0.1
PLACE_GROUP_BY Group-By Placement NO_PLACE_GROUP_BY PLACE_GROUP_BY 11.1.0.6
PLACE_GROUP_BY PLACE_GROUP_BY 11.1.0.6
PULL_PRED pull predicates NO_PULL_PRED PULL_PRED 10.2.0.1
PULL_PRED PULL_PRED 10.2.0.1
STAR_TRANS Star Transformation FACT FACT 8.1.0
NO_FACT FACT 8.1.0
STAR STAR 8.1.0
NO_STAR_TRANSFORMATION STAR_TRANSFORMATION 10.1.0.3
STAR_TRANSFORMATION STAR_TRANSFORMATION 8.1.0
TABLE_LOOKUP_BY_NL Table Lookup By Nested Loop NO_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL 11.2.0.2
TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL 11.2.0.2
TABLE_EXPANSION Table Expansion EXPAND_TABLE EXPAND_TABLE 11.2.0.1
NO_EXPAND_TABLE EXPAND_TABLE 11.2.0.1
NO_PQ_EXPAND_TABLE PQ_EXPAND_TABLE 19.1.0
PQ_EXPAND_TABLE PQ_EXPAND_TABLE 19.1.0
UNNEST unnest query block NO_UNNEST UNNEST 8.1.6
UNNEST UNNEST 8.1.6
VECTOR_AGG Vector Transformation NO_USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION 12.1.0.2
USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION 12.1.0.2
NO_VECTOR_TRANSFORM VECTOR_TRANSFORM 12.1.0.2
VECTOR_TRANSFORM VECTOR_TRANSFORM 12.1.0.2
NO_VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS 12.1.0.2
VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS 12.1.0.2
NO_VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT 12.1.0.2
VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT 12.1.0.2
CURSOR_SHARING Cursor sharing BIND_AWARE BIND_AWARE 11.1.0.7
NO_BIND_AWARE BIND_AWARE 11.1.0.7
DML DML CHANGE_DUPKEY_ERROR_INDEX CHANGE_DUPKEY_ERROR_INDEX 11.1.0.7
DISABLE_PARALLEL_DML ENABLE_PARALLEL_DML 11.2.0.4
ENABLE_PARALLEL_DML ENABLE_PARALLEL_DML 11.2.0.4
IGNORE_ROW_ON_DUPKEY_INDEX IGNORE_ROW_ON_DUPKEY_INDEX 11.1.0.7
PDB_LOCAL_ONLY PDB_LOCAL_ONLY 18.1.0
RETRY_ON_ROW_CHANGE RETRY_ON_ROW_CHANGE 11.1.0.7
JOIN_METHOD Join methods CUBE_AJ ANTIJOIN 12.1.0.1
HASH_AJ ANTIJOIN 8.1.0
MERGE_AJ ANTIJOIN 8.1.0
NL_AJ ANTIJOIN 8.0.0
CUBE_SJ SEMIJOIN 12.1.0.1
HASH_SJ SEMIJOIN 8.1.0
MERGE_SJ SEMIJOIN 8.1.0
NL_SJ SEMIJOIN 8.0.0
USE_CUBE Cube join NO_USE_CUBE JOIN 12.1.0.1
USE_CUBE JOIN 12.1.0.1
USE_HASH Hash join USE_HASH JOIN 8.1.0
NO_USE_HASH NO_USE_HASH 10.1.0.3
USE_MERGE Sort-merge join USE_MERGE JOIN 8.1.0
NO_USE_MERGE NO_USE_MERGE 10.1.0.3
USE_MERGE_CARTESIAN Merge join cartesian USE_MERGE_CARTESIAN JOIN 11.1.0.6
USE_NL Nested-loop join USE_NL JOIN 8.1.0
NO_USE_NL NO_USE_NL 10.1.0.3
USE_NL_WITH_INDEX Nested-loop index join USE_NL_WITH_INDEX USE_NL_WITH_INDEX 10.1.0.3
JOIN_ORDER Join order LEADING LEADING 8.1.6
ALL_ROWS All rows (optimizer mode) ALL_ROWS MODE 8.1.0
CHOOSE Choose (optimizer mode) CHOOSE MODE 8.1.0
FIRST_ROWS First rows (optimizer mode) FIRST_ROWS MODE 8.1.0
PARTIAL_JOIN Partial Join Evaluation NO_PARTIAL_JOIN PARTIAL_JOIN 12.1.0.1
PARTIAL_JOIN PARTIAL_JOIN 12.1.0.1
PARTITION Partition NO_REORDER_WIF REORDER_WIF 18.1.0
REORDER_WIF REORDER_WIF 18.1.0
USE_HIDDEN_PARTITIONS USE_HIDDEN_PARTITIONS 12.1.0.1
NO_USE_PARTITION_WISE_DISTINCT USE_PARTITION_WISE_DISTINCT 12.2.0.1
USE_PARTITION_WISE_DISTINCT USE_PARTITION_WISE_DISTINCT 12.2.0.1
NO_USE_PARTITION_WISE_GBY USE_PARTITION_WISE_GBY 12.2.0.1
USE_PARTITION_WISE_GBY USE_PARTITION_WISE_GBY 12.2.0.1
NO_USE_PARTITION_WISE_WIF USE_PARTITION_WISE_WIF 18.1.0
USE_PARTITION_WISE_WIF USE_PARTITION_WISE_WIF 18.1.0
PQ Parallel Query DIST_AGG_PROLLUP_PUSHDOWN DIST_AGG_PROLLUP_PUSHDOWN 12.2.0.1
NO_DIST_AGG_PROLLUP_PUSHDOWN DIST_AGG_PROLLUP_PUSHDOWN 12.2.0.1
NO_PARALLEL_INDEX PARALLEL_INDEX 8.1.0
PARALLEL_INDEX PARALLEL_INDEX 8.1.0
NO_PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN 12.1.0.1
PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN 12.1.0.1
NO_PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION 12.1.0.1
PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION 12.1.0.1
PQ_DISTRIBUTE_WINDOW PQ_DISTRIBUTE_WINDOW 12.1.0.1
PQ_FILTER PQ_FILTER 12.1.0.1
NO_PQ_SKEW PQ_SKEW 12.1.0.1
PQ_SKEW PQ_SKEW 12.1.0.1
NO_PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE 12.1.0.1
PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE 12.1.0.1
NO_USE_SCALABLE_GBY_INVDIST USE_SCALABLE_GBY_INVDIST 19.1.0
USE_SCALABLE_GBY_INVDIST USE_SCALABLE_GBY_INVDIST 19.1.0
PARALLEL Parallel table NOPARALLEL SHARED 8.1.0
SHARED SHARED 8.1.0
NO_STATEMENT_QUEUING STATEMENT_QUEUING 11.2.0.1
STATEMENT_QUEUING STATEMENT_QUEUING 11.2.0.1
PQ_DISTRIBUTE PQ Distribution method PQ_DISTRIBUTE PQ_DISTRIBUTE 8.1.5
PQ_MAP PQ slave mapper PQ_MAP PQ_MAP 9.0.0
PQ_NOMAP PQ_MAP 9.0.0
PQ_REPLICATE PQ replicate small tables NO_PQ_REPLICATE PQ_REPLICATE 12.1.0.1
PQ_REPLICATE PQ_REPLICATE 12.1.0.1
PX_JOIN_FILTER Bloom filtering for joins NO_PX_JOIN_FILTER PX_JOIN_FILTER 10.2.0.1
PX_JOIN_FILTER PX_JOIN_FILTER 10.2.0.1
STAR_TRANS Star Transformation FACT FACT 8.1.0
NO_FACT FACT 8.1.0
STAR STAR 8.1.0
NO_STAR_TRANSFORMATION STAR_TRANSFORMATION 10.1.0.3
STAR_TRANSFORMATION STAR_TRANSFORMATION 8.1.0
TABLE_LOOKUP_BY_NL Table Lookup By Nested Loop NO_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL 11.2.0.2
TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL 11.2.0.2
STATS Optimizer statistics CARDINALITY CARDINALITY 9.0.0
COLUMN_STATS TABLE_STATS 10.1.0.3
INDEX_STATS TABLE_STATS 10.1.0.3
TABLE_STATS TABLE_STATS 10.1.0.3
CPU_COSTING CPU costing CPU_COSTING CPU_COSTING 9.0.0
NO_CPU_COSTING CPU_COSTING 9.0.0
DBMS_STATS Statistics gathered by DBMS_STATS DBMS_STATS DBMS_STATS 10.2.0.1
GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS 12.1.0.1
NO_GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS 12.1.0.1
DYNAMIC_SAMPLING Dynamic sampling DYNAMIC_SAMPLING DYNAMIC_SAMPLING 9.2.0
DYNAMIC_SAMPLING_EST_CDN Estimate CDN using dynamic sampling DYNAMIC_SAMPLING_EST_CDN DYNAMIC_SAMPLING_EST_CDN 9.2.0
GATHER_PLAN_STATISTICS Gather plan statistics GATHER_PLAN_STATISTICS GATHER_PLAN_STATISTICS 10.1.0.3
OPT_ESTIMATE Optimizer estimates OPT_ESTIMATE OPT_ESTIMATE 10.1.0.3
DATA_SECURITY_REWRITE XS Data Security Rewrite DATA_SECURITY_REWRITE_LIMIT DATA_SECURITY_REWRITE_LIMIT 12.1.0.1
NO_DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT 12.1.0.1
RBO SQL Rule Based Optimization RULE MODE 8.1.0
RBO_OUTLINE RBO_OUTLINE 10.2.0.1
TRANSFORMATION Query Transformation ANTIJOIN ANTIJOIN 9.0.0
CONNECT_BY_CB_WHR_ONLY CONNECT_BY_CB_WHR_ONLY 10.2.0.5
NO_CONNECT_BY_CB_WHR_ONLY CONNECT_BY_CB_WHR_ONLY 10.2.0.5
CONNECT_BY_COST_BASED CONNECT_BY_COST_BASED 10.2.0.2
NO_CONNECT_BY_COST_BASED CONNECT_BY_COST_BASED 10.2.0.2
ELIM_GROUPBY ELIM_GROUPBY 12.1.0.2
NO_ELIM_GROUPBY ELIM_GROUPBY 12.1.0.2
EXPAND_GSET_TO_UNION EXPAND_GSET_TO_UNION 9.2.0
NO_EXPAND_GSET_TO_UNION EXPAND_GSET_TO_UNION 9.2.0
GBY_CONC_ROLLUP GBY_CONC_ROLLUP 9.0.0
INLINE INLINE 9.0.0
MATERIALIZE INLINE 9.0.0
MODEL_COMPILE_SUBQUERY MODEL_COMPILE_SUBQUERY 10.2.0.1
MODEL_DONTVERIFY_UNIQUENESS MODEL_DONTVERIFY_UNIQUENESS 10.1.0.3
MODEL_DYNAMIC_SUBQUERY MODEL_DYNAMIC_SUBQUERY 10.2.0.1
MODEL_MIN_ANALYSIS MODEL_MIN_ANALYSIS 10.1.0.3
MODEL_PUSH_REF MODEL_PUSH_REF 10.1.0.3
MV_MERGE MV_MERGE 9.0.0
NO_ORDER_ROLLUPS NO_ORDER_ROLLUPS 8.0.0
NO_PRUNE_GSETS NO_PRUNE_GSETS 9.0.0
NO_QUERY_TRANSFORMATION NO_QUERY_TRANSFORMATION 10.1.0.3
OPAQUE_TRANSFORM OPAQUE_TRANSFORM 10.1.0.3
OPAQUE_XCANONICAL OPAQUE_XCANONICAL 10.1.0.3
ORDER_SUBQ ORDER_SUBQ 12.2.0.1
PRECOMPUTE_SUBQUERY PRECOMPUTE_SUBQUERY 10.2.0.1
NO_PUSH_SUBQ PUSH_SUBQ 9.2.0
PUSH_SUBQ PUSH_SUBQ 8.1.0
NO_REWRITE REWRITE 8.1.5
REWRITE REWRITE 8.1.5
REWRITE_OR_ERROR REWRITE 10.1.0.3
NO_SEMIJOIN SEMIJOIN 9.0.0
SEMIJOIN SEMIJOIN 9.0.0
NO_TRANSFORM_DISTINCT_AGG TRANSFORM_DISTINCT_AGG 11.2.0.1
TRANSFORM_DISTINCT_AGG TRANSFORM_DISTINCT_AGG 11.2.0.1
USE_TTT_FOR_GSETS USE_TTT_FOR_GSETS 9.0.0
CBQT_OR_EXPANSION Cost Based OR Expansion NO_OR_EXPAND OR_EXPAND 12.2.0.1
OR_EXPAND OR_EXPAND 12.2.0.1
CVM Complex View Merging MERGE MERGE 8.1.0
NO_MERGE MERGE 8.0.0
DIST_PLCMT Distinct Placement NO_PLACE_DISTINCT PLACE_DISTINCT 11.2.0.1
PLACE_DISTINCT PLACE_DISTINCT 11.2.0.1
JOINFAC Join Factorization FACTORIZE_JOIN FACTORIZE_JOIN 11.2.0.1
NO_FACTORIZE_JOIN FACTORIZE_JOIN 11.2.0.1
PLACE_GROUP_BY Group-By Placement NO_PLACE_GROUP_BY PLACE_GROUP_BY 11.1.0.6
PLACE_GROUP_BY PLACE_GROUP_BY 11.1.0.6
PULL_PRED pull predicates NO_PULL_PRED PULL_PRED 10.2.0.1
PULL_PRED PULL_PRED 10.2.0.1
STAR_TRANS Star Transformation FACT FACT 8.1.0
NO_FACT FACT 8.1.0
STAR STAR 8.1.0
NO_STAR_TRANSFORMATION STAR_TRANSFORMATION 10.1.0.3
STAR_TRANSFORMATION STAR_TRANSFORMATION 8.1.0
TABLE_LOOKUP_BY_NL Table Lookup By Nested Loop NO_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL 11.2.0.2
TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL 11.2.0.2
TABLE_EXPANSION Table Expansion EXPAND_TABLE EXPAND_TABLE 11.2.0.1
NO_EXPAND_TABLE EXPAND_TABLE 11.2.0.1
NO_PQ_EXPAND_TABLE PQ_EXPAND_TABLE 19.1.0
PQ_EXPAND_TABLE PQ_EXPAND_TABLE 19.1.0
UNNEST unnest query block NO_UNNEST UNNEST 8.1.6
UNNEST UNNEST 8.1.6
VECTOR_AGG Vector Transformation NO_USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION 12.1.0.2
USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION 12.1.0.2
NO_VECTOR_TRANSFORM VECTOR_TRANSFORM 12.1.0.2
VECTOR_TRANSFORM VECTOR_TRANSFORM 12.1.0.2
NO_VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS 12.1.0.2
VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS 12.1.0.2
NO_VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT 12.1.0.2
VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT 12.1.0.2
GROUPING_SET_XFORM Grouping Set Transformation NO_USE_DAGG_UNION_ALL_GSETS USE_DAGG_UNION_ALL_GSETS 12.2.0.1
USE_DAGG_UNION_ALL_GSETS USE_DAGG_UNION_ALL_GSETS 12.2.0.1
ANSWER_QUERY_USING_STATS Statistics-based Query Transformation ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS 18.1.0
NO_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS 18.1.0
COALESCE_SQ coalesce subqueries COALESCE_SQ COALESCE_SQ 11.2.0.1
NO_COALESCE_SQ COALESCE_SQ 11.2.0.1
CVM Complex View Merging MERGE MERGE 8.1.0
NO_MERGE MERGE 8.0.0
DECORRELATE View Decorrelation DECORRELATE DECORRELATE 12.1.0.1
NO_DECORRELATE DECORRELATE 12.1.0.1
ELIMINATE_SQ eliminate subqueries ELIMINATE_SQ ELIMINATE_SQ 12.2.0.1
NO_ELIMINATE_SQ ELIMINATE_SQ 12.2.0.1
FILTER_PUSH_PRED Push filter predicates NO_PUSH_PRED PUSH_PRED 8.1.0
PUSH_PRED PUSH_PRED 8.1.0
OBYE Order-by Elimination ELIMINATE_OBY ELIMINATE_OBY 10.2.0.1
NO_ELIMINATE_OBY ELIMINATE_OBY 10.2.0.1
OLD_PUSH_PRED Old push predicate algorithm (pre-10.1.0.3) OLD_PUSH_PRED OLD_PUSH_PRED 10.2.0.1
OUTER_JOIN_TO_INNER Join Conversion NO_OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER 11.1.0.6
OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER 11.1.0.6
SET_TO_JOIN Transform set operations to joins NO_SET_TO_JOIN SET_TO_JOIN 10.1.0.3
SET_TO_JOIN SET_TO_JOIN 10.1.0.3
TABLE_ELIM Table Elimination ELIMINATE_JOIN ELIMINATE_JOIN 10.2.0.1
NO_ELIMINATE_JOIN ELIMINATE_JOIN 10.2.0.1
UNNEST unnest query block NO_UNNEST UNNEST 8.1.6
UNNEST UNNEST 8.1.6
USE_CONCAT Or-optimization NO_EXPAND OR_EXPAND 8.1.0
USE_CONCAT OR_EXPAND 8.1.0
XML_REWRITE XML Rewrite FORCE_XML_QUERY_REWRITE FORCE_XML_QUERY_REWRITE 9.2.0
NO_XML_QUERY_REWRITE FORCE_XML_QUERY_REWRITE 9.2.0
NO_XML_DML_REWRITE NO_XML_DML_REWRITE 10.2.0.1
XML_DML_RWT_STMT XML_DML_RWT_STMT 11.1.0.6
CHECK_ACL_REWRITE Check ACL Rewrite CHECK_ACL_REWRITE CHECK_ACL_REWRITE 11.1.0.6
NO_CHECK_ACL_REWRITE NO_CHECK_ACL_REWRITE 11.1.0.6
COST_XML_QUERY_REWRITE Cost Based XML Query Rewrite COST_XML_QUERY_REWRITE COST_XML_QUERY_REWRITE 11.1.0.6
NO_COST_XML_QUERY_REWRITE NO_COST_XML_QUERY_REWRITE 11.1.0.6
XMLINDEX_REWRITE XMLIndex Rewrite NO_XMLINDEX_REWRITE XMLINDEX_REWRITE 11.1.0.6
NO_XMLINDEX_REWRITE_IN_SELECT XMLINDEX_REWRITE 11.1.0.6
XMLINDEX_REWRITE XMLINDEX_REWRITE 11.1.0.6
XMLINDEX_REWRITE_IN_SELECT XMLINDEX_REWRITE 11.1.0.6
ZONEMAP Zone Maps NO_ZONEMAP ZONEMAP 12.1.0.1
ZONEMAP ZONEMAP 12.1.0.1
DDL DDL SUPPRESS_LOAD SUPPRESS_LOAD 18.1.0
EXECUTION SQL EXECUTION NLJ_BATCHING ACCESS 11.1.0.6
NLJ_PREFETCH ACCESS 11.1.0.6
NO_NLJ_BATCHING ACCESS 11.1.0.6
NO_NLJ_PREFETCH ACCESS 11.1.0.6
AV_CACHE AV_CACHE 18.1.0
BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID 12.1.0.1
NO_BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID 12.1.0.1
CACHE CACHE 8.1.0
NOCACHE CACHE 8.1.0
DATA_VALIDATE DATA_VALIDATE 12.2.0.1
INMEMORY INMEMORY 12.1.0.2
NO_INMEMORY INMEMORY 12.1.0.2
INMEMORY_PRUNING INMEMORY_PRUNING 12.1.0.2
NO_INMEMORY_PRUNING INMEMORY_PRUNING 12.1.0.2
JSON_LENGTH JSON_LENGTH 19.1.0
MEMOPTIMIZE_WRITE MEMOPTIMIZE_WRITE 18.1.0
NO_LOAD NO_LOAD 11.1.0.6
NO_SUBSTRB_PAD NO_SUBSTRB_PAD 11.2.0.1
NO_PUSH_HAVING_TO_GBY PUSH_HAVING_TO_GBY 18.1.0
PUSH_HAVING_TO_GBY PUSH_HAVING_TO_GBY 18.1.0
QUARANTINE QUARANTINE 19.1.0
RESERVOIR_SAMPLING RESERVOIR_SAMPLING 12.1.0.2
NO_RESULT_CACHE RESULT_CACHE 11.1.0.6
RESULT_CACHE RESULT_CACHE 11.1.0.6
TRACING TRACING 10.1.0.3