Here’s a quick and dirty script I thought I’d published a very long time ago. It has to be run as SYS and queries v$sql_hint to produce a list of all the things which (at least in theory) could be used as hints somewhere in the code.
I’ve ordered the list by the columns version then version_outline. I believe the version is supposed to be the version in which the hint first appeared (even if it wasn’t documented) and the version_outline is the version at which the hint could appear as part of a query’s “Outline Information” (which can then be used to generate a Stored Outline or SQL Plan Baseline – or SQL Patch). You’ll notice that a lot of the hints don’t have a version_outline.
One of the conveniences of this report is the appearance of the inverse column. For many hints there is an “opposite” hint. Hints often come in pairs (especially in recent versions of Oracle) following the pattern XXXX / NO_XXXX though there are many hints that don’t have an inverse and a few that don’t follow the pattern (e.g. use_concat / no_expand).
Perhaps one of the most useful parts of the report comes from the target_level which is a bitmap that can be decoded to show the query level(s) that the hint can apply to. There are 4 levels (corresponding to bits 0 to 3): statement, query block, object and join and that’s a useful piece of information to know when you’re trying to make best use of hints. For example, the cardinality hint is one that is not well-known and most of the people who knew anything about it seemed to assume that it could apply only to a table when in fact it can also be used for a query block (which can be extremely useful with subquery factoring) or a multi-table join (although that option is quite hard to use effectively except in the simplest cases).
rem
rem Script: sql_hints.sql
rem Author: Jonathan Lewis
rem Dated: Oct 2007
rem
set linesize 255
set pagesize 100
set trimspool on
set tab off
column name format a32
column inverse format a32
column sql_feature format a30
column version format a8
column outline format a8
column class format a35
column target format 999
column prop format 999
column bit0 format a6
column bit1 format a6
column bit2 format a6
column bit3 format a6
column bit4 format a3
column bit5 format a3
column bit6 format a3
column bit7 format a3
column bit8 format a3
break on version skip 1
compute count of version on version
spool sql_hints
select
version,
version_outline outline,
name,
inverse,
sql_feature,
class,
decode(bitand(target_level,1),1,'State') bit0,
decode(bitand(target_level,2),2,'QBlock') bit1,
decode(bitand(target_level,4),4,'Object') bit2,
decode(bitand(target_level,8),8,'Join') bit3,
target_level target,
decode(bitand(property,16),16,'16') bit4,
decode(bitand(property,32),32,'32') bit5,
decode(bitand(property,64),64,'64') bit6,
decode(bitand(property,128),128,'128') bit7,
decode(bitand(property,256),256,'256') bit8,
property prop
from
v$sql_hint
order by
to_number(substr(version,1,3)),
version,
to_number(substr(version_outline,1,3)) nulls last,
version_outline nulls last,
name
;
spool off
Footnote
There’s also a property column, which seems to be another bitmap but (at present) only using bits 4 to 8 of “something”. That points to the possibility that the level and property might be derived from the same couple of bytes but a quick check on x$qksht (the x$ structure underneath gv$sql_hint) shows two different columns (level_qkshtsyrow, props_qkshtsyrow) – with a 4 byte difference in their offsets. (This may be a fake, of course, since the address (addr) of every single “row” in the structure is the same, which means that when you’re looking at the x$ it’s been dynamically constructed from somewhere else.)
I haven’t yet worked out a consistent pattern for the bits in the property column, even though there are a number “coincidences” that look almost convincing, but I keep reporting the property – broken down by bits as well as the actual value – in this report in case one day I spot some clear correlation between the bits and the function of the hints.
If you’re interested in the results and don’t have access to v$sql_hint, here’s the output from 21.3.0.0. Of the 388 hints listed only about 120 are (officially) documented anywhere – there are a lot of hints you’re not supposed to use:
VERSION OUTLINE NAME INVERSE SQL_FEATURE CLASS BIT0 BIT1 BIT2 BIT3 TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
8.0.0 8.1.7 CLUSTER QKSFM_CBO ACCESS Object 4 16 256 272
8.1.7 INDEX NO_INDEX QKSFM_INDEX ACCESS Object 4 16 32 256 304
8.1.7 ROWID QKSFM_CBO ACCESS Object 4 16 256 272
10.1.0 NO_MERGE MERGE QKSFM_CVM MERGE QBlock Object 6 16 16
EXPR_CORR_CHECK QKSFM_CBO EXPR_CORR_CHECK State 1 0
MERGE_CONST_ON QKSFM_CBO MERGE_CONST_ON State 1 0
NL_AJ QKSFM_JOIN_METHOD ANTIJOIN QBlock 2 16 16
NL_SJ QKSFM_JOIN_METHOD SEMIJOIN QBlock 2 16 16
NO_MONITORING QKSFM_ALL NO_MONITORING State 1 0
NO_ORDER_ROLLUPS QKSFM_TRANSFORMATION NO_ORDER_ROLLUPS QBlock 2 0
NO_STATS_GSETS QKSFM_ALL NO_STATS_GSETS QBlock 2 0
ORDERED_PREDICATES QKSFM_CBO ORDERED_PREDICATES QBlock 2 16 16
QUEUE_CURR QKSFM_CBO ACCESS Object 4 256 256
QUEUE_ROWP QKSFM_CBO ACCESS Object 4 256 256
--------
14
8.1.0 8.1.5 BITMAP QKSFM_CBO BITMAP QBlock 2 256 256
8.1.5 NO_PUSH_PRED PUSH_PRED QKSFM_FILTER_PUSH_PRED PUSH_PRED QBlock Object 6 16 16
8.1.5 PUSH_PRED NO_PUSH_PRED QKSFM_FILTER_PUSH_PRED PUSH_PRED QBlock Object 6 16 16
8.1.5 RULE QKSFM_RBO MODE State 1 16 16
8.1.7 AND_EQUAL QKSFM_AND_EQUAL ACCESS Object 4 16 32 256 304
8.1.7 DRIVING_SITE QKSFM_ALL DRIVING_SITE Object 4 256 256
8.1.7 FACT NO_FACT QKSFM_STAR_TRANS FACT Object 4 16 256 272
8.1.7 FULL QKSFM_FULL ACCESS Object 4 16 256 272
8.1.7 HASH QKSFM_ALL ACCESS Object 4 16 256 272
8.1.7 HASH_AJ QKSFM_JOIN_METHOD ANTIJOIN QBlock 2 16 16
8.1.7 HASH_SJ QKSFM_JOIN_METHOD SEMIJOIN QBlock 2 16 16
8.1.7 INDEX_COMBINE QKSFM_INDEX_COMBINE ACCESS Object 4 16 32 128 256 432
8.1.7 INDEX_DESC NO_INDEX QKSFM_INDEX_DESC ACCESS Object 4 16 32 256 304
8.1.7 INDEX_FFS QKSFM_INDEX_FFS ACCESS Object 4 16 32 256 304
8.1.7 MERGE_AJ QKSFM_JOIN_METHOD ANTIJOIN QBlock 2 16 16
8.1.7 MERGE_SJ QKSFM_JOIN_METHOD SEMIJOIN QBlock 2 16 16
8.1.7 NO_EXPAND USE_CONCAT QKSFM_USE_CONCAT OR_EXPAND QBlock 2 16 16
8.1.7 NO_FACT FACT QKSFM_STAR_TRANS FACT Object 4 16 256 272
8.1.7 ORDERED QKSFM_CBO ORDERED QBlock 2 16 16
8.1.7 SEMIJOIN_DRIVER QKSFM_CBO SEMIJOIN_DRIVER QBlock 2 16 16
8.1.7 STAR_TRANSFORMATION NO_STAR_TRANSFORMATION QKSFM_STAR_TRANS STAR_TRANSFORMATION QBlock Object 6 16 16
8.1.7 SWAP_JOIN_INPUTS NO_SWAP_JOIN_INPUTS QKSFM_CBO SWAP_JOIN_INPUTS Object 4 16 256 272
8.1.7 USE_CONCAT NO_EXPAND QKSFM_USE_CONCAT OR_EXPAND QBlock 2 16 16
8.1.7 USE_HASH NO_USE_HASH QKSFM_USE_HASH JOIN Object 4 16 64 128 256 464
8.1.7 USE_MERGE NO_USE_MERGE QKSFM_USE_MERGE JOIN Object 4 16 64 256 336
8.1.7 USE_NL NO_USE_NL QKSFM_USE_NL JOIN Object 4 16 64 256 336
10.1.0 MERGE NO_MERGE QKSFM_CVM MERGE QBlock Object 6 16 16
10.2.0.1 ALL_ROWS QKSFM_ALL_ROWS MODE State 1 16 16
10.2.0.1 FIRST_ROWS QKSFM_FIRST_ROWS MODE State 1 16 16
10.2.0.5 PUSH_SUBQ NO_PUSH_SUBQ QKSFM_TRANSFORMATION PUSH_SUBQ QBlock 2 16 16
APPEND NOAPPEND QKSFM_CBO APPEND State 1 0
CACHE NOCACHE QKSFM_EXECUTION CACHE Object 4 256 256
CHOOSE QKSFM_CHOOSE MODE State 1 16 16
DEREF_NO_REWRITE QKSFM_ALL DEREF_NO_REWRITE State 1 0
INDEX_ASC NO_INDEX QKSFM_INDEX_ASC ACCESS Object 4 16 32 256 304
NESTED_TABLE_GET_REFS QKSFM_ALL NESTED_TABLE_GET_REFS State 1 0
NOAPPEND APPEND QKSFM_CBO APPEND State 1 0
NOCACHE CACHE QKSFM_EXECUTION CACHE Object 4 256 256
NOPARALLEL SHARED QKSFM_PARALLEL SHARED State Object 5 256 256
NO_PARALLEL_INDEX PARALLEL_INDEX QKSFM_PQ PARALLEL_INDEX Object 4 32 256 288
PARALLEL_INDEX NO_PARALLEL_INDEX QKSFM_PQ PARALLEL_INDEX Object 4 32 256 288
PIV_GB QKSFM_ALL PIV_GB QBlock 2 0
PIV_SSF QKSFM_ALL PIV_SSF QBlock 2 0
REMOTE_MAPPED QKSFM_ALL REMOTE_MAPPED QBlock 2 16 256 272
SHARED NO_PARALLEL QKSFM_PARALLEL SHARED State Object 5 256 256
STAR QKSFM_STAR_TRANS STAR QBlock 2 16 16
TIV_GB QKSFM_ALL PIV_GB QBlock 2 0
TIV_SSF QKSFM_ALL PIV_SSF QBlock 2 0
USE_ANTI QKSFM_CBO USE_ANTI Object 4 16 256 272
USE_SEMI QKSFM_CBO USE_SEMI Object 4 16 256 272
--------
50
8.1.5 8.1.7 NO_ACCESS QKSFM_ALL NO_ACCESS Object 4 256 256
8.1.7 NO_INDEX INDEX QKSFM_INDEX NO_INDEX Object 4 16 32 256 304
8.1.7 NO_REWRITE REWRITE QKSFM_TRANSFORMATION REWRITE QBlock 2 16 16
8.1.7 PQ_DISTRIBUTE QKSFM_PQ_DISTRIBUTE PQ_DISTRIBUTE Object 4 16 256 272
8.1.7 REWRITE NO_REWRITE QKSFM_TRANSFORMATION REWRITE QBlock 2 16 16
10.1.0.3 INDEX_JOIN QKSFM_INDEX_JOIN ACCESS Object 4 16 32 256 304
10.2.0.1 DOMAIN_INDEX_NO_SORT DOMAIN_INDEX_SORT QKSFM_CBO DOMAIN_INDEX_SORT QBlock 2 0
10.2.0.1 DOMAIN_INDEX_SORT DOMAIN_INDEX_NO_SORT QKSFM_CBO DOMAIN_INDEX_SORT QBlock 2 0
BUFFER NO_BUFFER QKSFM_CBO BUFFER QBlock 2 0
BYPASS_UJVC QKSFM_CBO BYPASS_UJVC QBlock 2 0
CACHE_CB NOCACHE QKSFM_CBO CACHE_CB Object 4 256 256
CUBE_GB QKSFM_CBO CUBE_GB QBlock 2 0
NESTED_TABLE_SET_SETID QKSFM_ALL NESTED_TABLE_SET_SETID State 1 0
NO_BUFFER BUFFER QKSFM_CBO BUFFER QBlock 2 0
RESTORE_AS_INTERVALS QKSFM_CBO RESTORE_AS_INTERVALS QBlock 2 0
SAVE_AS_INTERVALS QKSFM_CBO SAVE_AS_INTERVALS QBlock 2 0
SCN_ASCENDING QKSFM_ALL SCN_ASCENDING State 1 0
--------
17
8.1.6 10.1.0 NO_UNNEST UNNEST QKSFM_UNNEST UNNEST QBlock 2 16 16
10.1.0 UNNEST NO_UNNEST QKSFM_UNNEST UNNEST QBlock 2 16 16
10.1.0.3 LEADING QKSFM_JOIN_ORDER LEADING Join 8 16 256 272
SYS_PARALLEL_TXN QKSFM_CBO SYS_PARALLEL_TXN QBlock 2 0
--------
4
VERSION OUTLINE NAME INVERSE SQL_FEATURE CLASS BIT0 BIT1 BIT2 BIT3 TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
9.0.0 10.2.0.1 INDEX_SS NO_INDEX_SS QKSFM_INDEX_SS ACCESS Object 4 16 32 256 304
10.2.0.1 INDEX_SS_DESC NO_INDEX_SS QKSFM_INDEX_SS_DESC ACCESS Object 4 16 32 256 304
10.2.0.1 PQ_MAP PQ_NOMAP QKSFM_PQ_MAP PQ_MAP Object 4 16 256 272
10.2.0.1 PQ_NOMAP PQ_MAP QKSFM_PQ_MAP PQ_MAP Object 4 16 256 272
18.1.0 INLINE MATERIALIZE QKSFM_TRANSFORMATION INLINE QBlock 2 16 16
18.1.0 MATERIALIZE INLINE QKSFM_TRANSFORMATION INLINE QBlock 2 16 16
ANTIJOIN QKSFM_TRANSFORMATION ANTIJOIN QBlock 2 16 16
BYPASS_RECURSIVE_CHECK QKSFM_ALL BYPASS_RECURSIVE_CHECK QBlock 2 0
CARDINALITY QKSFM_STATS CARDINALITY QBlock Object Join 14 16 256 272
CPU_COSTING NO_CPU_COSTING QKSFM_CPU_COSTING CPU_COSTING QBlock 2 16 16
CURSOR_SHARING_EXACT QKSFM_CBO CURSOR_SHARING_EXACT QBlock 2 0
DML_UPDATE QKSFM_CBO DML_UPDATE State 1 0
GBY_CONC_ROLLUP QKSFM_TRANSFORMATION GBY_CONC_ROLLUP QBlock 2 0
HWM_BROKERED QKSFM_CBO HWM_BROKERED QBlock 2 0
INDEX_RRS QKSFM_CBO ACCESS Object 4 16 32 256 304
INDEX_SS_ASC NO_INDEX_SS QKSFM_INDEX_SS_ASC ACCESS Object 4 16 32 256 304
LOCAL_INDEXES QKSFM_CBO LOCAL_INDEXES QBlock 2 0
MV_MERGE QKSFM_TRANSFORMATION MV_MERGE QBlock 2 0
NO_CPU_COSTING CPU_COSTING QKSFM_CPU_COSTING CPU_COSTING QBlock 2 16 16
NO_PRUNE_GSETS QKSFM_TRANSFORMATION NO_PRUNE_GSETS QBlock 2 0
NO_SEMIJOIN SEMIJOIN QKSFM_TRANSFORMATION SEMIJOIN QBlock 2 16 16
OVERFLOW_NOMOVE QKSFM_CBO OVERFLOW_NOMOVE QBlock 2 0
SEMIJOIN NO_SEMIJOIN QKSFM_TRANSFORMATION SEMIJOIN QBlock 2 16 16
SKIP_EXT_OPTIMIZER QKSFM_CBO SKIP_EXT_OPTIMIZER QBlock 2 16 16
SQLLDR QKSFM_CBO SQLLDR State 1 0
USE_TTT_FOR_GSETS QKSFM_TRANSFORMATION USE_TTT_FOR_GSETS QBlock 2 0
--------
26
9.2.0 10.1.0 EXPAND_GSET_TO_UNION NO_EXPAND_GSET_TO_UNION QKSFM_TRANSFORMATION EXPAND_GSET_TO_UNION QBlock 2 0
10.1.0 NO_EXPAND_GSET_TO_UNION EXPAND_GSET_TO_UNION QKSFM_TRANSFORMATION EXPAND_GSET_TO_UNION QBlock 2 0
10.2.0.5 NO_PUSH_SUBQ PUSH_SUBQ QKSFM_TRANSFORMATION PUSH_SUBQ QBlock 2 16 16
11.1.0.6 FORCE_XML_QUERY_REWRITE NO_XML_QUERY_REWRITE QKSFM_XML_REWRITE FORCE_XML_QUERY_REWRITE State 1 0
11.1.0.6 NO_XML_QUERY_REWRITE FORCE_XML_QUERY_REWRITE QKSFM_XML_REWRITE FORCE_XML_QUERY_REWRITE State 1 0
DYNAMIC_SAMPLING QKSFM_DYNAMIC_SAMPLING DYNAMIC_SAMPLING QBlock Object 6 16 256 272
DYNAMIC_SAMPLING_EST_CDN QKSFM_DYNAMIC_SAMPLING_EST_CDN DYNAMIC_SAMPLING_EST_CDN Object 4 16 256 272
IGNORE_WHERE_CLAUSE QKSFM_ALL IGNORE_WHERE_CLAUSE State 1 0
NO_QKN_BUFF QKSFM_CBO NO_QKN_BUFF QBlock 2 0
NO_REF_CASCADE REF_CASCADE_CURSOR QKSFM_CBO REF_CASCADE_CURSOR State 1 0
REF_CASCADE_CURSOR NO_REF_CASCADE QKSFM_CBO REF_CASCADE_CURSOR State 1 0
SYS_DL_CURSOR QKSFM_CBO SYS_DL_CURSOR State 1 0
SYS_RID_ORDER QKSFM_ALL SYS_RID_ORDER QBlock 2 0
--------
13
10.1.0.3 10.1.0.3 NO_BASETABLE_MULTIMV_REWRITE REWRITE QKSFM_ALL REWRITE QBlock 2 16 16
10.1.0.3 NO_INDEX_FFS INDEX_FFS QKSFM_INDEX_FFS NO_INDEX_FFS Object 4 16 32 256 304
10.1.0.3 NO_INDEX_SS INDEX_SS QKSFM_INDEX_SS NO_INDEX_SS Object 4 16 32 256 304
10.1.0.3 NO_MULTIMV_REWRITE REWRITE QKSFM_ALL REWRITE QBlock 2 16 16
10.1.0.3 NO_SET_TO_JOIN SET_TO_JOIN QKSFM_SET_TO_JOIN SET_TO_JOIN QBlock 2 16 16
10.1.0.3 NO_STAR_TRANSFORMATION STAR_TRANSFORMATION QKSFM_STAR_TRANS STAR_TRANSFORMATION QBlock Object 6 16 16
10.1.0.3 NO_SWAP_JOIN_INPUTS SWAP_JOIN_INPUTS QKSFM_CBO SWAP_JOIN_INPUTS Object 4 16 256 272
10.1.0.3 NO_USE_HASH USE_HASH QKSFM_USE_HASH NO_USE_HASH Object 4 16 64 256 336
10.1.0.3 NO_USE_MERGE USE_MERGE QKSFM_USE_MERGE NO_USE_MERGE Object 4 16 64 256 336
10.1.0.3 NO_USE_NL USE_NL QKSFM_USE_NL NO_USE_NL Object 4 16 64 256 336
10.1.0.3 SET_TO_JOIN NO_SET_TO_JOIN QKSFM_SET_TO_JOIN SET_TO_JOIN QBlock 2 16 16
10.2.0.1 IGNORE_OPTIM_EMBEDDED_HINTS QKSFM_ALL IGNORE_OPTIM_EMBEDDED_HINTS State 1 0
10.2.0.1 OPTIMIZER_FEATURES_ENABLE QKSFM_ALL OPTIMIZER_FEATURES_ENABLE State 1 16 256 272
COLUMN_STATS QKSFM_STATS TABLE_STATS State 1 16 256 272
FBTSCAN QKSFM_CBO FBTSCAN State 1 0
GATHER_PLAN_STATISTICS QKSFM_GATHER_PLAN_STATISTICS GATHER_PLAN_STATISTICS State 1 0
INCLUDE_VERSION QKSFM_ALL INCLUDE_VERSION State 1 0
INDEX_STATS QKSFM_STATS TABLE_STATS State 1 16 256 272
MODEL_DONTVERIFY_UNIQUENESS QKSFM_TRANSFORMATION MODEL_DONTVERIFY_UNIQUENESS QBlock 2 0
MODEL_MIN_ANALYSIS QKSFM_TRANSFORMATION MODEL_MIN_ANALYSIS QBlock 2 0
MODEL_NO_ANALYSIS QKSFM_ALL MODEL_MIN_ANALYSIS QBlock 2 0
MODEL_PUSH_REF NO_MODEL_PUSH_REF QKSFM_TRANSFORMATION MODEL_PUSH_REF QBlock 2 0
NESTED_TABLE_FAST_INSERT QKSFM_ALL NESTED_TABLE_FAST_INSERT State 1 0
NO_MODEL_PUSH_REF MODEL_PUSH_REF QKSFM_ALL MODEL_PUSH_REF QBlock 2 0
NO_PARALLEL SHARED QKSFM_CBO SHARED State Object 5 256 256
NO_PARTIAL_COMMIT QKSFM_CBO NO_PARTIAL_COMMIT State 1 0
NO_QUERY_TRANSFORMATION QKSFM_TRANSFORMATION NO_QUERY_TRANSFORMATION State 1 16 16
OPAQUE_TRANSFORM QKSFM_TRANSFORMATION OPAQUE_TRANSFORM State 1 0
OPAQUE_XCANONICAL QKSFM_TRANSFORMATION OPAQUE_XCANONICAL State 1 0
OPT_ESTIMATE QKSFM_OPT_ESTIMATE OPT_ESTIMATE QBlock Object Join 14 16 256 272
QB_NAME QKSFM_ALL QB_NAME QBlock 2 256 256
RESTRICT_ALL_REF_CONS QKSFM_ALL RESTRICT_ALL_REF_CONS State 1 0
REWRITE_OR_ERROR QKSFM_TRANSFORMATION REWRITE QBlock 2 0
SKIP_UNQ_UNUSABLE_IDX QKSFM_CBO SKIP_UNQ_UNUSABLE_IDX State 1 0
STREAMS QKSFM_CBO STREAMS State 1 0
TABLE_STATS QKSFM_STATS TABLE_STATS State 1 16 256 272
TRACING QKSFM_EXECUTION TRACING State 1 0
USE_NL_WITH_INDEX NO_USE_NL QKSFM_USE_NL_WITH_INDEX USE_NL_WITH_INDEX Object 4 16 32 256 304
USE_WEAK_NAME_RESL QKSFM_ALL USE_WEAK_NAME_RESL State 1 0
VECTOR_READ QKSFM_CBO VECTOR_READ State 1 0
VECTOR_READ_TRACE QKSFM_CBO VECTOR_READ_TRACE State 1 0
X_DYN_PRUNE QKSFM_CBO X_DYN_PRUNE QBlock 2 0
--------
42
10.2.0.1 10.2.0.1 BITMAP_TREE QKSFM_BITMAP_TREE ACCESS Object 4 16 32 256 304
10.2.0.1 ELIMINATE_JOIN NO_ELIMINATE_JOIN QKSFM_TABLE_ELIM ELIMINATE_JOIN Object 4 16 16
10.2.0.1 ELIMINATE_OBY NO_ELIMINATE_OBY QKSFM_OBYE ELIMINATE_OBY QBlock 2 16 16
10.2.0.1 NO_ELIMINATE_JOIN ELIMINATE_JOIN QKSFM_TABLE_ELIM ELIMINATE_JOIN Object 4 16 16
10.2.0.1 NO_ELIMINATE_OBY ELIMINATE_OBY QKSFM_OBYE ELIMINATE_OBY QBlock 2 16 16
10.2.0.1 NO_PULL_PRED PULL_PRED QKSFM_PULL_PRED PULL_PRED Object 4 16 16
10.2.0.1 OLD_PUSH_PRED QKSFM_OLD_PUSH_PRED OLD_PUSH_PRED QBlock Object 6 16 16
VERSION OUTLINE NAME INVERSE SQL_FEATURE CLASS BIT0 BIT1 BIT2 BIT3 TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
10.2.0.1 10.2.0.1 OPT_PARAM QKSFM_ALL OPT_PARAM State 1 16 256 272
10.2.0.1 OUTLINE QKSFM_ALL OUTLINE QBlock 2 0
10.2.0.1 OUTLINE_LEAF QKSFM_ALL OUTLINE_LEAF QBlock 2 0
10.2.0.1 PULL_PRED NO_PULL_PRED QKSFM_PULL_PRED PULL_PRED Object 4 16 16
10.2.0.1 RBO_OUTLINE QKSFM_RBO RBO_OUTLINE State 1 0
10.2.0.5 NO_USE_HASH_AGGREGATION USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION QBlock 2 0
10.2.0.5 USE_HASH_AGGREGATION NO_USE_HASH_AGGREGATION QKSFM_ALL USE_HASH_AGGREGATION QBlock 2 0
11.1.0.6 NO_PX_JOIN_FILTER PX_JOIN_FILTER QKSFM_PX_JOIN_FILTER PX_JOIN_FILTER Object 4 16 64 256 336
11.1.0.6 NO_XML_DML_REWRITE QKSFM_XML_REWRITE NO_XML_DML_REWRITE State 1 0
11.1.0.6 PX_JOIN_FILTER NO_PX_JOIN_FILTER QKSFM_PX_JOIN_FILTER PX_JOIN_FILTER Object 4 16 64 256 336
DBMS_STATS QKSFM_DBMS_STATS DBMS_STATS State 1 0
INLINE_XMLTYPE_NT QKSFM_ALL INLINE_XMLTYPE_NT State 1 0
MODEL_COMPILE_SUBQUERY QKSFM_TRANSFORMATION MODEL_COMPILE_SUBQUERY QBlock 2 0
MODEL_DYNAMIC_SUBQUERY QKSFM_TRANSFORMATION MODEL_DYNAMIC_SUBQUERY QBlock 2 0
NO_CARTESIAN QKSFM_ALL NO_CARTESIAN Object 4 16 64 256 336
NO_SQL_TUNE QKSFM_ALL NO_SQL_TUNE State 1 0
PRECOMPUTE_SUBQUERY QKSFM_TRANSFORMATION PRECOMPUTE_SUBQUERY QBlock 2 0
PRESERVE_OID QKSFM_ALL PRESERVE_OID State 1 0
--------
25
10.2.0.2 10.2.0.2 CONNECT_BY_COST_BASED NO_CONNECT_BY_COST_BASED QKSFM_TRANSFORMATION CONNECT_BY_COST_BASED QBlock 2 16 16
10.2.0.2 CONNECT_BY_FILTERING NO_CONNECT_BY_FILTERING QKSFM_ALL CONNECT_BY_FILTERING QBlock 2 16 16
10.2.0.2 NO_CONNECT_BY_COST_BASED CONNECT_BY_COST_BASED QKSFM_TRANSFORMATION CONNECT_BY_COST_BASED QBlock 2 16 16
10.2.0.2 NO_CONNECT_BY_FILTERING CONNECT_BY_FILTERING QKSFM_ALL CONNECT_BY_FILTERING QBlock 2 16 16
--------
4
10.2.0.3 10.2.0.3 NATIVE_FULL_OUTER_JOIN NO_NATIVE_FULL_OUTER_JOIN QKSFM_ALL NATIVE_FULL_OUTER_JOIN QBlock 2 16 16
10.2.0.3 NO_NATIVE_FULL_OUTER_JOIN NATIVE_FULL_OUTER_JOIN QKSFM_ALL NATIVE_FULL_OUTER_JOIN QBlock 2 16 16
10.2.0.3 NUM_INDEX_KEYS QKSFM_CBO ACCESS Object 4 16 32 256 304
--------
3
10.2.0.4 10.2.0.4 CONNECT_BY_COMBINE_SW NO_CONNECT_BY_COMBINE_SW QKSFM_ALL CONNECT_BY_COMBINE_SW QBlock 2 16 16
10.2.0.4 NO_CONNECT_BY_COMBINE_SW CONNECT_BY_COMBINE_SW QKSFM_ALL CONNECT_BY_COMBINE_SW QBlock 2 16 16
--------
2
10.2.0.5 10.2.0.5 CONNECT_BY_CB_WHR_ONLY NO_CONNECT_BY_CB_WHR_ONLY QKSFM_TRANSFORMATION CONNECT_BY_CB_WHR_ONLY QBlock 2 16 16
10.2.0.5 GBY_PUSHDOWN NO_GBY_PUSHDOWN QKSFM_ALL GBY_PUSHDOWN QBlock 2 16 16
10.2.0.5 NO_CONNECT_BY_CB_WHR_ONLY CONNECT_BY_CB_WHR_ONLY QKSFM_TRANSFORMATION CONNECT_BY_CB_WHR_ONLY QBlock 2 16 16
10.2.0.5 NO_GBY_PUSHDOWN GBY_PUSHDOWN QKSFM_ALL GBY_PUSHDOWN QBlock 2 16 16
--------
4
11.1.0.6 11.1.0.6 COST_XML_QUERY_REWRITE NO_COST_XML_QUERY_REWRITE QKSFM_COST_XML_QUERY_REWRITE COST_XML_QUERY_REWRITE State 1 0
11.1.0.6 DB_VERSION QKSFM_ALL DB_VERSION State 1 16 256 272
11.1.0.6 DOMAIN_INDEX_FILTER NO_DOMAIN_INDEX_FILTER QKSFM_CBO DOMAIN_INDEX_FILTER Object 4 16 32 256 304
11.1.0.6 INDEX_RS_ASC QKSFM_INDEX_RS_ASC ACCESS Object 4 16 32 256 304
11.1.0.6 INDEX_RS_DESC QKSFM_INDEX_RS_DESC ACCESS Object 4 16 32 256 304
11.1.0.6 NLJ_BATCHING NO_NLJ_BATCHING QKSFM_EXECUTION ACCESS Object 4 16 256 272
11.1.0.6 NLJ_PREFETCH NO_NLJ_PREFETCH QKSFM_EXECUTION NLJ_PREFETCH Object 4 16 256 272
11.1.0.6 NO_COST_XML_QUERY_REWRITE COST_XML_QUERY_REWRITE QKSFM_COST_XML_QUERY_REWRITE NO_COST_XML_QUERY_REWRITE State 1 0
11.1.0.6 NO_DOMAIN_INDEX_FILTER DOMAIN_INDEX_FILTER QKSFM_CBO NO_DOMAIN_INDEX_FILTER Object 4 16 32 256 304
11.1.0.6 NO_NLJ_BATCHING NLJ_BATCHING QKSFM_EXECUTION ACCESS Object 4 16 256 272
11.1.0.6 NO_NLJ_PREFETCH NLJ_PREFETCH QKSFM_EXECUTION NLJ_PREFETCH Object 4 16 256 272
11.1.0.6 NO_OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER QKSFM_OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER QBlock Object 6 16 16
11.1.0.6 NO_PLACE_GROUP_BY PLACE_GROUP_BY QKSFM_PLACE_GROUP_BY PLACE_GROUP_BY QBlock 2 16 16
11.1.0.6 NO_SUBQUERY_PRUNING SUBQUERY_PRUNING QKSFM_CBO SUBQUERY_PRUNING Object 4 16 256 272
11.1.0.6 NO_USE_INVISIBLE_INDEXES USE_INVISIBLE_INDEXES QKSFM_INDEX USE_INVISIBLE_INDEXES State 1 0
11.1.0.6 NO_XMLINDEX_REWRITE XMLINDEX_REWRITE QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE State 1 0
11.1.0.6 NO_XMLINDEX_REWRITE_IN_SELECT XMLINDEX_REWRITE_IN_SELECT QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE State 1 0
11.1.0.6 OUTER_JOIN_TO_INNER NO_OUTER_JOIN_TO_INNER QKSFM_OUTER_JOIN_TO_INNER OUTER_JOIN_TO_INNER QBlock Object 6 16 16
11.1.0.6 PLACE_GROUP_BY NO_PLACE_GROUP_BY QKSFM_PLACE_GROUP_BY PLACE_GROUP_BY QBlock 2 16 16
11.1.0.6 SUBQUERY_PRUNING NO_SUBQUERY_PRUNING QKSFM_CBO SUBQUERY_PRUNING Object 4 16 256 272
11.1.0.6 USE_INVISIBLE_INDEXES NO_USE_INVISIBLE_INDEXES QKSFM_INDEX USE_INVISIBLE_INDEXES State 1 0
11.1.0.6 USE_MERGE_CARTESIAN QKSFM_USE_MERGE_CARTESIAN JOIN Object 4 16 64 256 336
11.1.0.6 XMLINDEX_REWRITE NO_XMLINDEX_REWRITE QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE State 1 0
11.1.0.6 XMLINDEX_REWRITE_IN_SELECT NO_XMLINDEX_REWRITE_IN_SELECT QKSFM_XMLINDEX_REWRITE XMLINDEX_REWRITE State 1 0
11.1.0.6 XML_DML_RWT_STMT QKSFM_XML_REWRITE XML_DML_RWT_STMT State 1 0
CHECK_ACL_REWRITE NO_CHECK_ACL_REWRITE QKSFM_CHECK_ACL_REWRITE CHECK_ACL_REWRITE State 1 0
MONITOR NO_MONITOR QKSFM_ALL MONITOR State 1 0
NO_CHECK_ACL_REWRITE CHECK_ACL_REWRITE QKSFM_CHECK_ACL_REWRITE NO_CHECK_ACL_REWRITE State 1 0
NO_LOAD QKSFM_EXECUTION NO_LOAD State 1 0
NO_MONITOR MONITOR QKSFM_ALL MONITOR State 1 0
NO_RESULT_CACHE RESULT_CACHE QKSFM_EXECUTION RESULT_CACHE QBlock 2 0
RESULT_CACHE NO_RESULT_CACHE QKSFM_EXECUTION RESULT_CACHE QBlock 2 0
--------
32
11.1.0.7 BIND_AWARE NO_BIND_AWARE QKSFM_CURSOR_SHARING BIND_AWARE State 1 0
CHANGE_DUPKEY_ERROR_INDEX QKSFM_DML CHANGE_DUPKEY_ERROR_INDEX Object 4 32 256 288
IGNORE_ROW_ON_DUPKEY_INDEX QKSFM_DML IGNORE_ROW_ON_DUPKEY_INDEX Object 4 32 256 288
NO_BIND_AWARE BIND_AWARE QKSFM_CURSOR_SHARING BIND_AWARE State 1 0
RETRY_ON_ROW_CHANGE QKSFM_DML RETRY_ON_ROW_CHANGE State 1 0
--------
5
11.2.0.1 11.2.0.1 COALESCE_SQ NO_COALESCE_SQ QKSFM_COALESCE_SQ COALESCE_SQ QBlock 2 16 16
11.2.0.1 CONNECT_BY_ELIM_DUPS NO_CONNECT_BY_ELIM_DUPS QKSFM_ALL CONNECT_BY_ELIM_DUPS QBlock 2 16 16
11.2.0.1 EXPAND_TABLE NO_EXPAND_TABLE QKSFM_TABLE_EXPANSION EXPAND_TABLE Object 4 16 16
11.2.0.1 FACTORIZE_JOIN NO_FACTORIZE_JOIN QKSFM_JOINFAC FACTORIZE_JOIN QBlock 2 16 16
11.2.0.1 NO_COALESCE_SQ COALESCE_SQ QKSFM_COALESCE_SQ COALESCE_SQ QBlock 2 16 16
11.2.0.1 NO_CONNECT_BY_ELIM_DUPS CONNECT_BY_ELIM_DUPS QKSFM_ALL CONNECT_BY_ELIM_DUPS QBlock 2 16 16
11.2.0.1 NO_EXPAND_TABLE EXPAND_TABLE QKSFM_TABLE_EXPANSION EXPAND_TABLE Object 4 16 16
11.2.0.1 NO_FACTORIZE_JOIN FACTORIZE_JOIN QKSFM_JOINFAC FACTORIZE_JOIN QBlock 2 16 16
VERSION OUTLINE NAME INVERSE SQL_FEATURE CLASS BIT0 BIT1 BIT2 BIT3 TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
11.2.0.1 11.2.0.1 NO_PLACE_DISTINCT PLACE_DISTINCT QKSFM_DIST_PLCMT PLACE_DISTINCT QBlock 2 16 16
11.2.0.1 NO_TRANSFORM_DISTINCT_AGG TRANSFORM_DISTINCT_AGG QKSFM_TRANSFORMATION TRANSFORM_DISTINCT_AGG QBlock 2 0
11.2.0.1 PLACE_DISTINCT NO_PLACE_DISTINCT QKSFM_DIST_PLCMT PLACE_DISTINCT QBlock 2 16 16
11.2.0.1 TRANSFORM_DISTINCT_AGG NO_TRANSFORM_DISTINCT_AGG QKSFM_TRANSFORMATION TRANSFORM_DISTINCT_AGG QBlock 2 0
21.1.0.1 NO_DST_UPGRADE_INSERT_CONV DST_UPGRADE_INSERT_CONV QKSFM_ALL DST_UPGRADE_INSERT_CONV State 1 0
APPEND_VALUES NOAPPEND QKSFM_CBO APPEND_VALUES State 1 0
DST_UPGRADE_INSERT_CONV NO_DST_UPGRADE_INSERT_CONV QKSFM_ALL DST_UPGRADE_INSERT_CONV State 1 0
NO_STATEMENT_QUEUING STATEMENT_QUEUING QKSFM_PARALLEL STATEMENT_QUEUING State 1 0
NO_SUBSTRB_PAD QKSFM_EXECUTION NO_SUBSTRB_PAD State 1 0
STATEMENT_QUEUING NO_STATEMENT_QUEUING QKSFM_PARALLEL STATEMENT_QUEUING State 1 0
XMLINDEX_SEL_IDX_TBL QKSFM_ALL XMLINDEX_SEL_IDX_TBL State 1 0
--------
19
11.2.0.2 11.2.0.2 NO_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL QKSFM_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL Object 4 16 16
11.2.0.2 NO_USE_HASH_GBY_FOR_PUSHDOWN USE_HASH_GBY_FOR_PUSHDOWN QKSFM_ALL USE_HASH_GBY_FOR_PUSHDOWN QBlock 2 0
11.2.0.2 TABLE_LOOKUP_BY_NL NO_TABLE_LOOKUP_BY_NL QKSFM_TABLE_LOOKUP_BY_NL TABLE_LOOKUP_BY_NL Object 4 16 16
11.2.0.2 USE_HASH_GBY_FOR_PUSHDOWN NO_USE_HASH_GBY_FOR_PUSHDOWN QKSFM_ALL USE_HASH_GBY_FOR_PUSHDOWN QBlock 2 0
NO_XDB_FASTPATH_INSERT XDB_FASTPATH_INSERT QKSFM_ALL XDB_FASTPATH_INSERT State 1 0
XDB_FASTPATH_INSERT NO_XDB_FASTPATH_INSERT QKSFM_ALL XDB_FASTPATH_INSERT State 1 0
--------
6
11.2.0.3 11.2.0.3 FULL_OUTER_JOIN_TO_OUTER NO_FULL_OUTER_JOIN_TO_OUTER QKSFM_CBO FULL_OUTER_JOIN_TO_OUTER Object 4 16 256 272
11.2.0.3 NO_FULL_OUTER_JOIN_TO_OUTER FULL_OUTER_JOIN_TO_OUTER QKSFM_CBO FULL_OUTER_JOIN_TO_OUTER Object 4 16 256 272
11.2.0.3 NO_OUTER_JOIN_TO_ANTI OUTER_JOIN_TO_ANTI QKSFM_CBO OUTER_JOIN_TO_ANTI Object 4 16 256 272
11.2.0.3 NO_SEMI_TO_INNER SEMI_TO_INNER QKSFM_CBO NO_SEMI_TO_INNER Object 4 16 256 272
11.2.0.3 OUTER_JOIN_TO_ANTI NO_OUTER_JOIN_TO_ANTI QKSFM_CBO OUTER_JOIN_TO_ANTI Object 4 16 256 272
11.2.0.3 SEMI_TO_INNER NO_SEMI_TO_INNER QKSFM_CBO SEMI_TO_INNER Object 4 16 256 272
--------
6
11.2.0.4 DISABLE_PARALLEL_DML ENABLE_PARALLEL_DML QKSFM_DML ENABLE_PARALLEL_DML State 1 0
ENABLE_PARALLEL_DML DISABLE_PARALLEL_DML QKSFM_DML ENABLE_PARALLEL_DML State 1 0
--------
2
12.1.0.1 12.1.0.1 BATCH_TABLE_ACCESS_BY_ROWID NO_BATCH_TABLE_ACCESS_BY_ROWID QKSFM_EXECUTION BATCH_TABLE_ACCESS_BY_ROWID Object 4 16 256 272
12.1.0.1 BITMAP_AND QKSFM_BITMAP_TREE BITMAP_AND Object 4 16 32 48
12.1.0.1 CLUSTERING NO_CLUSTERING QKSFM_CLUSTERING CLUSTERING State 1 0
12.1.0.1 CLUSTER_BY_ROWID NO_CLUSTER_BY_ROWID QKSFM_CBO CLUSTER_BY_ROWID Object 4 16 256 272
12.1.0.1 CUBE_AJ QKSFM_JOIN_METHOD ANTIJOIN QBlock 2 16 16
12.1.0.1 CUBE_SJ QKSFM_JOIN_METHOD SEMIJOIN QBlock 2 16 16
12.1.0.1 DATA_SECURITY_REWRITE_LIMIT NO_DATA_SECURITY_REWRITE QKSFM_DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT State 1 0
12.1.0.1 DECORRELATE NO_DECORRELATE QKSFM_DECORRELATE DECORRELATE QBlock 2 16 16
12.1.0.1 NO_BATCH_TABLE_ACCESS_BY_ROWID BATCH_TABLE_ACCESS_BY_ROWID QKSFM_EXECUTION BATCH_TABLE_ACCESS_BY_ROWID Object 4 16 256 272
12.1.0.1 NO_CLUSTERING CLUSTERING QKSFM_CLUSTERING CLUSTERING State 1 0
12.1.0.1 NO_CLUSTER_BY_ROWID CLUSTER_BY_ROWID QKSFM_CBO CLUSTER_BY_ROWID Object 4 16 256 272
12.1.0.1 NO_DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT QKSFM_DATA_SECURITY_REWRITE DATA_SECURITY_REWRITE_LIMIT State 1 0
12.1.0.1 NO_DECORRELATE DECORRELATE QKSFM_DECORRELATE DECORRELATE QBlock 2 16 16
12.1.0.1 NO_PARTIAL_JOIN PARTIAL_JOIN QKSFM_PARTIAL_JOIN PARTIAL_JOIN Object 4 16 256 272
12.1.0.1 NO_PARTIAL_ROLLUP_PUSHDOWN PARTIAL_ROLLUP_PUSHDOWN QKSFM_PQ PARTIAL_ROLLUP_PUSHDOWN QBlock 2 16 16
12.1.0.1 NO_PQ_CONCURRENT_UNION PQ_CONCURRENT_UNION QKSFM_PQ PQ_CONCURRENT_UNION State QBlock 3 0
12.1.0.1 NO_PQ_REPLICATE PQ_REPLICATE QKSFM_PQ_REPLICATE PQ_REPLICATE Object 4 16 256 272
12.1.0.1 NO_PQ_SKEW PQ_SKEW QKSFM_PQ PQ_SKEW Object 4 16 256 272
12.1.0.1 NO_PX_FAULT_TOLERANCE PX_FAULT_TOLERANCE QKSFM_PQ PX_FAULT_TOLERANCE State 1 0
12.1.0.1 NO_USE_CUBE USE_CUBE QKSFM_USE_CUBE JOIN Object 4 16 64 256 336
12.1.0.1 NO_ZONEMAP ZONEMAP QKSFM_ZONEMAP ZONEMAP Object 4 256 256
12.1.0.1 PARTIAL_JOIN NO_PARTIAL_JOIN QKSFM_PARTIAL_JOIN PARTIAL_JOIN Object 4 16 256 272
12.1.0.1 PARTIAL_ROLLUP_PUSHDOWN NO_PARTIAL_ROLLUP_PUSHDOWN QKSFM_PQ PARTIAL_ROLLUP_PUSHDOWN QBlock 2 16 16
12.1.0.1 PQ_CONCURRENT_UNION NO_PQ_CONCURRENT_UNION QKSFM_PQ PQ_CONCURRENT_UNION State QBlock 3 0
12.1.0.1 PQ_DISTRIBUTE_WINDOW QKSFM_PQ PQ_DISTRIBUTE_WINDOW QBlock 2 16 16
12.1.0.1 PQ_FILTER QKSFM_PQ PQ_FILTER QBlock 2 0
12.1.0.1 PQ_REPLICATE NO_PQ_REPLICATE QKSFM_PQ_REPLICATE PQ_REPLICATE Object 4 16 256 272
12.1.0.1 PQ_SKEW NO_PQ_SKEW QKSFM_PQ PQ_SKEW Object 4 16 256 272
12.1.0.1 PX_FAULT_TOLERANCE NO_PX_FAULT_TOLERANCE QKSFM_PQ PX_FAULT_TOLERANCE State 1 0
12.1.0.1 USE_CUBE NO_USE_CUBE QKSFM_USE_CUBE JOIN Object 4 16 64 256 336
12.1.0.1 ZONEMAP NO_ZONEMAP QKSFM_ZONEMAP ZONEMAP Object 4 256 256
AUTO_REOPTIMIZE NO_AUTO_REOPTIMIZE QKSFM_AUTO_REOPT AUTO_REOPTIMIZE State 1 0
GATHER_OPTIMIZER_STATISTICS NO_GATHER_OPTIMIZER_STATISTICS QKSFM_DBMS_STATS GATHER_OPTIMIZER_STATISTICS State 1 0
NO_AUTO_REOPTIMIZE AUTO_REOPTIMIZE QKSFM_AUTO_REOPT AUTO_REOPTIMIZE State 1 0
NO_GATHER_OPTIMIZER_STATISTICS GATHER_OPTIMIZER_STATISTICS QKSFM_DBMS_STATS GATHER_OPTIMIZER_STATISTICS State 1 0
USE_HIDDEN_PARTITIONS QKSFM_PARTITION USE_HIDDEN_PARTITIONS QBlock 2 0
WITH_PLSQL QKSFM_ALL WITH_PLSQL State 1 0
--------
37
12.1.0.2 12.1.0.2 ADAPTIVE_PLAN NO_ADAPTIVE_PLAN QKSFM_ADAPTIVE_PLAN ADAPTIVE_PLAN State 1 16 16
12.1.0.2 ANSI_REARCH NO_ANSI_REARCH QKSFM_ANSI_REARCH ANSI_REARCH QBlock 2 16 16
12.1.0.2 ELIM_GROUPBY NO_ELIM_GROUPBY QKSFM_TRANSFORMATION ELIM_GROUPBY QBlock 2 16 16
12.1.0.2 INMEMORY NO_INMEMORY QKSFM_EXECUTION INMEMORY QBlock Object 6 64 64
12.1.0.2 INMEMORY_PRUNING NO_INMEMORY_PRUNING QKSFM_EXECUTION INMEMORY_PRUNING QBlock Object 6 64 64
12.1.0.2 NO_ADAPTIVE_PLAN ADAPTIVE_PLAN QKSFM_ADAPTIVE_PLAN ADAPTIVE_PLAN State 1 16 16
12.1.0.2 NO_ANSI_REARCH ANSI_REARCH QKSFM_ANSI_REARCH ANSI_REARCH QBlock 2 16 16
12.1.0.2 NO_ELIM_GROUPBY ELIM_GROUPBY QKSFM_TRANSFORMATION ELIM_GROUPBY QBlock 2 16 16
12.1.0.2 NO_INMEMORY INMEMORY QKSFM_EXECUTION INMEMORY QBlock Object 6 64 64
12.1.0.2 NO_INMEMORY_PRUNING INMEMORY_PRUNING QKSFM_EXECUTION INMEMORY_PRUNING QBlock Object 6 64 64
12.1.0.2 NO_USE_VECTOR_AGGREGATION USE_VECTOR_AGGREGATION QKSFM_VECTOR_AGG USE_VECTOR_AGGREGATION QBlock 2 16 16
12.1.0.2 NO_VECTOR_TRANSFORM VECTOR_TRANSFORM QKSFM_VECTOR_AGG VECTOR_TRANSFORM QBlock 2 16 16
12.1.0.2 NO_VECTOR_TRANSFORM_DIMS VECTOR_TRANSFORM_DIMS QKSFM_VECTOR_AGG VECTOR_TRANSFORM_DIMS Object 4 16 64 80
12.1.0.2 NO_VECTOR_TRANSFORM_FACT VECTOR_TRANSFORM_FACT QKSFM_VECTOR_AGG VECTOR_TRANSFORM_FACT Object 4 16 64 80
12.1.0.2 USE_VECTOR_AGGREGATION NO_USE_VECTOR_AGGREGATION QKSFM_VECTOR_AGG USE_VECTOR_AGGREGATION QBlock 2 16 16
12.1.0.2 VECTOR_TRANSFORM NO_VECTOR_TRANSFORM QKSFM_VECTOR_AGG VECTOR_TRANSFORM QBlock 2 16 16
12.1.0.2 VECTOR_TRANSFORM_DIMS NO_VECTOR_TRANSFORM_DIMS QKSFM_VECTOR_AGG VECTOR_TRANSFORM_DIMS Object 4 16 64 80
12.1.0.2 VECTOR_TRANSFORM_FACT NO_VECTOR_TRANSFORM_FACT QKSFM_VECTOR_AGG VECTOR_TRANSFORM_FACT Object 4 16 64 80
RESERVOIR_SAMPLING QKSFM_EXECUTION RESERVOIR_SAMPLING State 1 0
--------
VERSION OUTLINE NAME INVERSE SQL_FEATURE CLASS BIT0 BIT1 BIT2 BIT3 TARGET BIT BIT BIT BIT BIT PROP
-------- -------- -------------------------------- -------------------------------- ------------------------------ ----------------------------------- ------ ------ ------ ------ ------ --- --- --- --- --- ----
19
12.2.0.1 12.2.0.1 BUSHY_JOIN NO_BUSHY_JOIN QKSFM_BUSHY_JOIN BUSHY_JOIN QBlock 2 16 16
12.2.0.1 CONTAINERS QKSFM_ALL CONTAINERS State 1 0
12.2.0.1 DIST_AGG_PROLLUP_PUSHDOWN NO_DIST_AGG_PROLLUP_PUSHDOWN QKSFM_PQ DIST_AGG_PROLLUP_PUSHDOWN QBlock 2 16 16
12.2.0.1 ELIMINATE_SQ NO_ELIMINATE_SQ QKSFM_ELIMINATE_SQ ELIMINATE_SQ QBlock 2 16 16
12.2.0.1 NO_BUSHY_JOIN BUSHY_JOIN QKSFM_BUSHY_JOIN BUSHY_JOIN QBlock 2 16 16
12.2.0.1 NO_DIST_AGG_PROLLUP_PUSHDOWN DIST_AGG_PROLLUP_PUSHDOWN QKSFM_PQ DIST_AGG_PROLLUP_PUSHDOWN QBlock 2 16 16
12.2.0.1 NO_ELIMINATE_SQ ELIMINATE_SQ QKSFM_ELIMINATE_SQ ELIMINATE_SQ QBlock 2 16 16
12.2.0.1 NO_OR_EXPAND OR_EXPAND QKSFM_CBQT_OR_EXPANSION OR_EXPAND QBlock 2 16 16
12.2.0.1 NO_USE_DAGG_UNION_ALL_GSETS USE_DAGG_UNION_ALL_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS QBlock 2 0
12.2.0.1 NO_USE_HASH_GBY_FOR_DAGGPSHD USE_HASH_GBY_FOR_DAGGPSHD QKSFM_ALL USE_HASH_GBY_FOR_DAGGPSHD QBlock 2 0
12.2.0.1 NO_USE_PARTITION_WISE_DISTINCT USE_PARTITION_WISE_DISTINCT QKSFM_PARTITION USE_PARTITION_WISE_DISTINCT QBlock 2 0
12.2.0.1 NO_USE_PARTITION_WISE_GBY USE_PARTITION_WISE_GBY QKSFM_PARTITION USE_PARTITION_WISE_GBY QBlock 2 0
12.2.0.1 ORDER_SUBQ QKSFM_TRANSFORMATION ORDER_SUBQ QBlock 2 16 16
12.2.0.1 OR_EXPAND NO_OR_EXPAND QKSFM_CBQT_OR_EXPANSION OR_EXPAND QBlock 2 16 16
12.2.0.1 USE_DAGG_UNION_ALL_GSETS NO_USE_DAGG_UNION_ALL_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS QBlock 2 0
12.2.0.1 USE_HASH_GBY_FOR_DAGGPSHD NO_USE_HASH_GBY_FOR_DAGGPSHD QKSFM_ALL USE_HASH_GBY_FOR_DAGGPSHD QBlock 2 0
12.2.0.1 USE_PARTITION_WISE_DISTINCT NO_USE_PARTITION_WISE_DISTINCT QKSFM_PARTITION USE_PARTITION_WISE_DISTINCT QBlock 2 0
12.2.0.1 USE_PARTITION_WISE_GBY NO_USE_PARTITION_WISE_GBY QKSFM_PARTITION USE_PARTITION_WISE_GBY QBlock 2 0
DATA_VALIDATE QKSFM_EXECUTION DATA_VALIDATE State 1 0
FRESH_MV QKSFM_MVIEWS FRESH_MV State 1 0
SQL_SCOPE QKSFM_COMPILATION SQL_SCOPE State 1 0
XMLTSET_DML_ENABLE QKSFM_ALL XMLTSET_DML_ENABLE State 1 0
--------
22
18.1.0 18.1.0 ANSWER_QUERY_USING_STATS NO_ANSWER_QUERY_USING_STATS QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS QBlock 2 16 16
18.1.0 NO_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS QKSFM_ANSWER_QUERY_USING_STATS ANSWER_QUERY_USING_STATS QBlock 2 16 16
18.1.0 NO_PUSH_HAVING_TO_GBY PUSH_HAVING_TO_GBY QKSFM_EXECUTION PUSH_HAVING_TO_GBY QBlock 2 0
18.1.0 NO_REORDER_WIF REORDER_WIF QKSFM_PARTITION REORDER_WIF QBlock 2 0
18.1.0 NO_USE_PARTITION_WISE_WIF USE_PARTITION_WISE_WIF QKSFM_PARTITION USE_PARTITION_WISE_WIF QBlock 2 0
18.1.0 PUSH_HAVING_TO_GBY NO_PUSH_HAVING_TO_GBY QKSFM_EXECUTION PUSH_HAVING_TO_GBY QBlock 2 0
18.1.0 REORDER_WIF NO_REORDER_WIF QKSFM_PARTITION REORDER_WIF QBlock 2 0
18.1.0 USE_PARTITION_WISE_WIF NO_USE_PARTITION_WISE_WIF QKSFM_PARTITION USE_PARTITION_WISE_WIF QBlock 2 0
AV_CACHE QKSFM_EXECUTION AV_CACHE QBlock 2 0
CURRENT_INSTANCE QKSFM_ALL CURRENT_INSTANCE State 1 0
MEMOPTIMIZE_WRITE QKSFM_EXECUTION MEMOPTIMIZE_WRITE State 1 0
PDB_LOCAL_ONLY QKSFM_DML PDB_LOCAL_ONLY State 1 0
SKIP_PROXY QKSFM_ALL SKIP_PROXY State 1 0
SUPPRESS_LOAD QKSFM_DDL SUPPRESS_LOAD State 1 0
SYSTEM_STATS QKSFM_ALL SYSTEM_STATS State 1 16 256 272
--------
15
19.1.0 19.1.0 NO_PQ_EXPAND_TABLE PQ_EXPAND_TABLE QKSFM_TABLE_EXPANSION PQ_EXPAND_TABLE Object 4 16 16
19.1.0 NO_USE_SCALABLE_GBY_INVDIST USE_SCALABLE_GBY_INVDIST QKSFM_PQ USE_SCALABLE_GBY_INVDIST QBlock 2 0
19.1.0 PQ_EXPAND_TABLE NO_PQ_EXPAND_TABLE QKSFM_TABLE_EXPANSION PQ_EXPAND_TABLE Object 4 16 16
19.1.0 USE_SCALABLE_GBY_INVDIST NO_USE_SCALABLE_GBY_INVDIST QKSFM_PQ USE_SCALABLE_GBY_INVDIST QBlock 2 0
JSON_LENGTH QKSFM_EXECUTION JSON_LENGTH State 1 0
QUARANTINE QKSFM_EXECUTION QUARANTINE State 1 0
--------
6
20.1.0 20.1.0 FORCE_JSON_TABLE_TRANSFORM NO_JSON_TABLE_TRANSFORM QKSFM_JSON_REWRITE FORCE_JSON_TABLE_TRANSFORM State 1 0
20.1.0 NO_JSON_TABLE_TRANSFORM FORCE_JSON_TABLE_TRANSFORM QKSFM_JSON_REWRITE FORCE_JSON_TABLE_TRANSFORM State 1 0
20.1.0 NO_SET_GBY_PUSHDOWN SET_GBY_PUSHDOWN QKSFM_ALL SET_GBY_PUSHDOWN QBlock 2 16 16
20.1.0 SET_GBY_PUSHDOWN NO_SET_GBY_PUSHDOWN QKSFM_ALL SET_GBY_PUSHDOWN QBlock 2 16 16
ANALYTIC_VIEW_SQL QKSFM_COMPILATION ANALYTIC_VIEW_SQL QBlock 2 0
DENORM_AV QKSFM_COMPILATION DENORM_AV QBlock 2 0
--------
6
21.1.0 21.1.0 DAGG_OPTIM_GSETS NO_DAGG_OPTIM_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS QBlock 2 0
21.1.0 HASHSET_BUILD QKSFM_EXECUTION HASHSET_BUILD QBlock 2 16 16
21.1.0 NO_DAGG_OPTIM_GSETS DAGG_OPTIM_GSETS QKSFM_GROUPING_SET_XFORM DAGG_OPTIM_GSETS QBlock 2 0
21.1.0 NO_OBY_GBYPD_SEPARATE OBY_GBYPD_SEPARATE QKSFM_PQ OBY_GBYPD_SEPARATE QBlock 2 16 16
21.1.0 NO_PQ_NONLEAF_SKEW PQ_NONLEAF_SKEW QKSFM_PQ PQ_NONLEAF_SKEW Object 4 16 256 272
21.1.0 OBY_GBYPD_SEPARATE NO_OBY_GBYPD_SEPARATE QKSFM_PQ OBY_GBYPD_SEPARATE QBlock 2 16 16
21.1.0 ORDER_KEY_VECTOR_USE QKSFM_VECTOR_AGG ORDER_KEY_VECTOR_USE QBlock 2 16 256 272
21.1.0 OSON_GET_CONTENT QKSFM_JSON OSON_GET_CONTENT State 1 0
21.1.0 PQ_NONLEAF_SKEW NO_PQ_NONLEAF_SKEW QKSFM_PQ PQ_NONLEAF_SKEW Object 4 16 256 272
--------
9
388 rows selected
Oracle Scratchpad 
[…] spent on calls to dbms_stats over the stats retention period. System stats: report current values SQL Hints: simple listing of v$sql_hint showing all the hints, their inverses, and the level(s) for which […]
Pingback by Simple scripts | Oracle Scratchpad — March 7, 2022 @ 10:23 am GMT Mar 7,2022 |
[…] If you want to see a complete list of hints, including the version where they appeared, and the version where they got into the “stored outline” code, then 11g gives you the view v$sql_hint. (Sample query with output from 19c) […]
Pingback by Undocumented Hints | Oracle Scratchpad — April 2, 2022 @ 9:51 am BST Apr 2,2022 |
[…] the time of writing this note a query against the view v$sql_hint on Oracle 12.1.0.2 reports 332 hints, but there are very few which we should really consider as […]
Pingback by Five Hints | Oracle Scratchpad — October 21, 2022 @ 12:11 pm BST Oct 21,2022 |