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 Feature | Description |
| QKSFM_ACCESS_ADVISOR | sql access advisor |
| QKSFM_ACCESS_PATH | Query access path |
| QKSFM_ALL | A Universal Feature |
| QKSFM_ALL_ROWS | All rows (optimizer mode) |
| QKSFM_AND_EQUAL | Index and-equal access path |
| QKSFM_AQ | Advanced Queuing |
| QKSFM_BITMAP_TREE | Bitmap tree access path |
| QKSFM_CARDINALITY | Cardinality computation |
| QKSFM_CBO | SQL Cost Based Optimization |
| QKSFM_CBQT | Cost Based Query Transformation |
| QKSFM_CDC | change data capture |
| QKSFM_CHECK_ACL_REWRITE | Check ACL Rewrite |
| QKSFM_CHOOSE | Choose (optimizer mode) |
| QKSFM_CNT | Count(col) to count(*) |
| QKSFM_COALESCE_SQ | coalesce subqueries |
| QKSFM_COLUMN_STATS | Basic column statistics |
| QKSFM_COMPILATION | SQL COMPILATION |
| QKSFM_COST_XML_QUERY_REWRITE | Cost Based XML Query Rewrite |
| QKSFM_CPU_COSTING | CPU costing |
| QKSFM_CSE | Common Sub-Expression Elimination |
| QKSFM_CURSOR_SHARING | Cursor sharing |
| QKSFM_CVM | Complex View Merging |
| QKSFM_DBMS_STATS | Statistics gathered by DBMS_STATS |
| QKSFM_DIST_PLCMT | Distinct Placement |
| QKSFM_DML | DML |
| QKSFM_DYNAMIC_SAMPLING | Dynamic sampling |
| QKSFM_DYNAMIC_SAMPLING_EST_CDN | Estimate CDN using dynamic sampling |
| QKSFM_EXECUTION | SQL EXECUTION |
| QKSFM_FBA | Flashback Data Archive |
| QKSFM_FILTER_PUSH_PRED | Push filter predicates |
| QKSFM_FIRST_ROWS | First rows (optimizer mode) |
| QKSFM_FULL | Full table scan |
| QKSFM_GATHER_PLAN_STATISTICS | Gather plan statistics |
| QKSFM_HEURISTIC | Heuristic Query Transformation |
| QKSFM_INDEX | Index |
| QKSFM_INDEX_ASC | Index (ascending) |
| QKSFM_INDEX_COMBINE | Combine index for bitmap access |
| QKSFM_INDEX_DESC | Use index (descending) |
| QKSFM_INDEX_FFS | Index fast full scan |
| QKSFM_INDEX_JOIN | Index join |
| QKSFM_INDEX_RS_ASC | Index range scan |
| QKSFM_INDEX_RS_DESC | Index range scan descending |
| QKSFM_INDEX_SS | Index skip scan |
| QKSFM_INDEX_SS_ASC | Index skip scan ascending |
| QKSFM_INDEX_SS_DESC | Index skip scan descending |
| QKSFM_INDEX_STATS | Basic index statistics |
| QKSFM_JOINFAC | Join Factorization |
| QKSFM_JOIN_METHOD | Join methods |
| QKSFM_JOIN_ORDER | Join order |
| QKSFM_JPPD | Join Predicate Push Down |
| QKSFM_MVIEWS | materialized views |
| QKSFM_OBYE | Order-by Elimination |
| QKSFM_OLD_PUSH_PRED | Old push predicate algorithm (pre-10.1.0.3) |
| QKSFM_ONLINE_REDEF | online redefinition with mviews |
| QKSFM_OPT_ESTIMATE | Optimizer estimates |
| QKSFM_OPT_MODE | Optimizer mode |
| QKSFM_OR_EXPAND | OR expansion |
| QKSFM_OUTER_JOIN_TO_INNER | Join Conversion |
| QKSFM_OUTLINE | Outlines |
| QKSFM_PARALLEL | Parallel table |
| QKSFM_PARTITION | Partition |
| QKSFM_PLACE_GROUP_BY | Group-By Placement |
| QKSFM_PQ | Parallel Query |
| QKSFM_PQ_DISTRIBUTE | PQ Distribution method |
| QKSFM_PQ_MAP | PQ slave mapper |
| QKSFM_PRED_MOVE_AROUND | Predicate move around |
| QKSFM_PULL_PRED | pull predicates |
| QKSFM_PX_JOIN_FILTER | Bloom filtering for joins |
| QKSFM_QUERY_REWRITE | query rewrite with materialized views |
| QKSFM_RBO | SQL Rule Based Optimization |
| QKSFM_SET_TO_JOIN | Transform set operations to joins |
| QKSFM_SORT_ELIM | Sort Elimination Via Index |
| QKSFM_SQL_CODE_GENERATOR | SQL Code Generator |
| QKSFM_SQL_PLAN_MANAGEMENT | SQL Plan Management |
| QKSFM_STAR_TRANS | Star Transformation |
| QKSFM_STATS | Optimizer statistics |
| QKSFM_SVM | Simple View Merging |
| QKSFM_TABLE_ELIM | Table Elimination |
| QKSFM_TABLE_EXPANSION | Table Expansion |
| QKSFM_TABLE_STATS | Basic table statistics |
| QKSFM_TRANSFORMATION | Query Transformation |
| QKSFM_UNNEST | unnest query block |
| QKSFM_USE_CONCAT | Or-optimization |
| QKSFM_USE_HASH | Hash join |
| QKSFM_USE_MERGE | Sort-merge join |
| QKSFM_USE_MERGE_CARTESIAN | Merge join cartesian |
| QKSFM_USE_NL | Nested-loop join |
| QKSFM_USE_NL_WITH_INDEX | Nested-loop index join |
| QKSFM_XMLINDEX_REWRITE | XMLIndex Rewrite |
| QKSFM_XML_REWRITE | XML Rewrite |
