Optimizer Features

In Oracle 11.1 and above, a new dynamic performance view describes all supported SQL features including their name and description

In Oracle 11.1.0.7 there are 307 SQL features. Of this total 230 are bug fixes and therefore there are 77 "interesting" features in this release

In Oracle 11.2.0.1 there are 497 SQL features of which 407 are bug fixes. There are therefore 90 "interesting" features in this release.

The underlying fixed table is X$QKSFM which also contains 497 rows. Therefore there is no concept of "hidden" features

SQL Features are referenced in the V$SQL_HINT table. Every hint is associated with an SQL feature

The following table summarizes the SQL features listed in Oracle 11.2.0.1. Note that the bug fixes have been omitted from this list:

SQL FeatureDescription
QKSFM_ACCESS_ADVISORsql access advisor
QKSFM_ACCESS_PATHQuery access path
QKSFM_ALLA Universal Feature
QKSFM_ALL_ROWSAll rows (optimizer mode)
QKSFM_AND_EQUALIndex and-equal access path
QKSFM_AQAdvanced Queuing
QKSFM_BITMAP_TREEBitmap tree access path
QKSFM_CARDINALITYCardinality computation
QKSFM_CBOSQL Cost Based Optimization
QKSFM_CBQTCost Based Query Transformation
QKSFM_CDCchange data capture
QKSFM_CHECK_ACL_REWRITECheck ACL Rewrite
QKSFM_CHOOSEChoose (optimizer mode)
QKSFM_CNTCount(col) to count(*)
QKSFM_COALESCE_SQcoalesce subqueries
QKSFM_COLUMN_STATSBasic column statistics
QKSFM_COMPILATIONSQL COMPILATION
QKSFM_COST_XML_QUERY_REWRITECost Based XML Query Rewrite
QKSFM_CPU_COSTINGCPU costing
QKSFM_CSECommon Sub-Expression Elimination
QKSFM_CURSOR_SHARINGCursor sharing
QKSFM_CVMComplex View Merging
QKSFM_DBMS_STATSStatistics gathered by DBMS_STATS
QKSFM_DIST_PLCMTDistinct Placement
QKSFM_DMLDML
QKSFM_DYNAMIC_SAMPLINGDynamic sampling
QKSFM_DYNAMIC_SAMPLING_EST_CDNEstimate CDN using dynamic sampling
QKSFM_EXECUTIONSQL EXECUTION
QKSFM_FBAFlashback Data Archive
QKSFM_FILTER_PUSH_PREDPush filter predicates
QKSFM_FIRST_ROWSFirst rows (optimizer mode)
QKSFM_FULLFull table scan
QKSFM_GATHER_PLAN_STATISTICSGather plan statistics
QKSFM_HEURISTICHeuristic Query Transformation
QKSFM_INDEXIndex
QKSFM_INDEX_ASCIndex (ascending)
QKSFM_INDEX_COMBINECombine index for bitmap access
QKSFM_INDEX_DESCUse index (descending)
QKSFM_INDEX_FFSIndex fast full scan
QKSFM_INDEX_JOINIndex join
QKSFM_INDEX_RS_ASCIndex range scan
QKSFM_INDEX_RS_DESCIndex range scan descending
QKSFM_INDEX_SSIndex skip scan
QKSFM_INDEX_SS_ASCIndex skip scan ascending
QKSFM_INDEX_SS_DESCIndex skip scan descending
QKSFM_INDEX_STATSBasic index statistics
QKSFM_JOINFACJoin Factorization
QKSFM_JOIN_METHODJoin methods
QKSFM_JOIN_ORDERJoin order
QKSFM_JPPDJoin Predicate Push Down
QKSFM_MVIEWSmaterialized views
QKSFM_OBYEOrder-by Elimination
QKSFM_OLD_PUSH_PREDOld push predicate algorithm (pre-10.1.0.3)
QKSFM_ONLINE_REDEFonline redefinition with mviews
QKSFM_OPT_ESTIMATEOptimizer estimates
QKSFM_OPT_MODEOptimizer mode
QKSFM_OR_EXPANDOR expansion
QKSFM_OUTER_JOIN_TO_INNERJoin Conversion
QKSFM_OUTLINEOutlines
QKSFM_PARALLELParallel table
QKSFM_PARTITIONPartition
QKSFM_PLACE_GROUP_BYGroup-By Placement
QKSFM_PQParallel Query
QKSFM_PQ_DISTRIBUTEPQ Distribution method
QKSFM_PQ_MAPPQ slave mapper
QKSFM_PRED_MOVE_AROUNDPredicate move around
QKSFM_PULL_PREDpull predicates
QKSFM_PX_JOIN_FILTERBloom filtering for joins
QKSFM_QUERY_REWRITEquery rewrite with materialized views
QKSFM_RBOSQL Rule Based Optimization
QKSFM_SET_TO_JOINTransform set operations to joins
QKSFM_SORT_ELIMSort Elimination Via Index
QKSFM_SQL_CODE_GENERATORSQL Code Generator
QKSFM_SQL_PLAN_MANAGEMENTSQL Plan Management
QKSFM_STAR_TRANSStar Transformation
QKSFM_STATSOptimizer statistics
QKSFM_SVMSimple View Merging
QKSFM_TABLE_ELIMTable Elimination
QKSFM_TABLE_EXPANSIONTable Expansion
QKSFM_TABLE_STATSBasic table statistics
QKSFM_TRANSFORMATIONQuery Transformation
QKSFM_UNNESTunnest query block
QKSFM_USE_CONCATOr-optimization
QKSFM_USE_HASHHash join
QKSFM_USE_MERGESort-merge join
QKSFM_USE_MERGE_CARTESIANMerge join cartesian
QKSFM_USE_NLNested-loop join
QKSFM_USE_NL_WITH_INDEXNested-loop index join
QKSFM_XMLINDEX_REWRITEXMLIndex Rewrite
QKSFM_XML_REWRITEXML Rewrite