ORACLE HINTS

 

HINTS

  • PLANS
  • PARAMETERS
  •  

    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

     

    O R A C L E L A B 로그인 :)