Oracle SQL Hints --- Abstraction
[中文]
Author:
fuyuncat
Source:
www.HelloDBA.com
Date:
2013-01-08 03:42:16
Share to
SQL HINT description and demonstration
SQL Hint is one of most important approaches to change the activity of optimizer and SQL execution, it’s also pretty important for SQL tuning. For instance, HINT is a part of the SQL Profiler advised by SQL Tuning Advisor. In each Oracle version, corresponding to the SQL features changes, new hints will be introduced, and old hints may be obsolete. Oracle introduced a new dynamic view, V$SQL_HINT, to show in which version the hint was involved in, and in which version it began work as outline data. The hints are associated with special SQL features. It will work only if the related features are enabled. Take HASH_AJ for example, it’s a CBO (QKSFM_CBO) feature hint, and it will not work if the SQL optimizer mode is set to RBO.
Some hints are only effect in the internal recursive SQLs, cannot be used in user SQL directly.
The embedded hints in SQL are a piece of comment, with the format /*+ <hint 1> [<hint 2> ...]*/. One comment may involve multiple hints, and one SQL may also involve multiple hint comments. And the hint will work only if they exist in the comment following the key words, SELECT, UPDATE, INSERT, MERGE and DELETE. If the SQL is a complex query involved in sub-query, the hint could be written as global or local format. The local format hints exist in the sub-query, and it can only affect the sub-query. While the global format hits exist in the main part of the query, it can be specified to affect any object in whole query by adding <object>@<block>. The alias could be used to replace the object name.
Tip: Since the embedded SQL hint is a piece of comment, its format could also be --+<hint>. For example,
HELLODBA.COM>select --+full(u)
2 * from t_users u where user_id =1;
We will descript all of hints, and also give demonstration of their usage.
SQL Features
Below hierarchy diagram shows all SQL features and their dependencies. Be aware, some features may be based on multiple features.
+QKSFM_ALL A Universal Feature
+--QKSFM_COMPILATION SQL COMPILATION
+----QKSFM_CBO SQL Cost Based Optimization
+------QKSFM_ACCESS_PATH Query access path
+--------QKSFM_AND_EQUAL Index and-equal access path
+--------QKSFM_BITMAP_TREE Bitmap tree access path
+--------QKSFM_FULL Full table scan
+--------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_SORT_ELIM Sort Elimination Via Index
+------QKSFM_CBQT Cost Based Query Transformation
+--------QKSFM_CVM Complex View Merging
+--------QKSFM_DIST_PLCMT Distinct Placement
+--------QKSFM_JOINFAC Join Factorization
+--------QKSFM_JPPD Join Predicate Push Down
+--------QKSFM_PLACE_GROUP_BY Group-By Placement
+--------QKSFM_PULL_PRED pull predicates
+--------QKSFM_TABLE_EXPANSION Table Expansion
+--------QKSFM_UNNEST unnest query block
+------QKSFM_CURSOR_SHARING Cursor sharing
+------QKSFM_DML DML
+------QKSFM_JOIN_METHOD Join methods
+--------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_JOIN_ORDER Join order
+------QKSFM_OPT_MODE Optimizer mode
+--------QKSFM_ALL_ROWS All rows (optimizer mode)
+--------QKSFM_CHOOSE Choose (optimizer mode)
+--------QKSFM_FIRST_ROWS First rows (optimizer mode)
+------QKSFM_OR_EXPAND OR expansion QKSFM_JPPD(Join Predicate Push Down)
+------QKSFM_OUTLINE Outlines
+------QKSFM_PARTITION Partition
+------QKSFM_PQ Parallel Query
+--------QKSFM_PARALLEL Parallel table
+--------QKSFM_PQ_DISTRIBUTE PQ Distribution method
+--------QKSFM_PQ_MAP PQ slave mapper
+--------QKSFM_PX_JOIN_FILTER Bloom filtering for joins
+------QKSFM_STAR_TRANS Star Transformation
+------QKSFM_STATS Optimizer statistics
+--------QKSFM_CARDINALITY Cardinality computation
+--------QKSFM_COLUMN_STATS Basic column statistics
+--------QKSFM_CPU_COSTING CPU costing
+--------QKSFM_DBMS_STATS Statistics gathered by DBMS_STATS
+--------QKSFM_DYNAMIC_SAMPLING Dynamic sampling
+--------QKSFM_DYNAMIC_SAMPLING_EST_CDN Estimate CDN using dynamic sampling
+--------QKSFM_GATHER_PLAN_STATISTICS Gather plan statistics
+--------QKSFM_INDEX_STATS Basic index statistics
+--------QKSFM_OPT_ESTIMATE Optimizer estimates
+--------QKSFM_TABLE_STATS Basic table statistics
+----QKSFM_QUERY_REWRITE query rewrite with materialized views
+----QKSFM_RBO SQL Rule Based Optimization
+----QKSFM_SQL_CODE_GENERATOR SQL Code Generator
+----QKSFM_SQL_PLAN_MANAGEMENT SQL Plan Management
+----QKSFM_TRANSFORMATION Query Transformation
+------QKSFM_CBQT Cost Based Query Transformation
+--------QKSFM_CVM Complex View Merging
+--------QKSFM_DIST_PLCMT Distinct Placement
+--------QKSFM_JOINFAC Join Factorization
+--------QKSFM_JPPD Join Predicate Push Down
+--------QKSFM_PLACE_GROUP_BY Group-By Placement
+--------QKSFM_PULL_PRED pull predicates
+--------QKSFM_TABLE_EXPANSION Table Expansion
+--------QKSFM_UNNEST unnest query block
+------QKSFM_HEURISTIC Heuristic Query Transformation
+--------QKSFM_CNT Count(col) to count(*)
+--------QKSFM_COALESCE_SQ coalesce subqueries
+--------QKSFM_CSE Common Sub-Expression Elimination
+--------QKSFM_CVM Complex View Merging
+--------QKSFM_FILTER_PUSH_PRED Push filter predicates
+--------QKSFM_JPPD Join Predicate Push Down
+--------QKSFM_OBYE Order-by Elimination
+--------QKSFM_OLD_PUSH_PRED Old push predicate algorithm (pre-10.1.0.3)
+--------QKSFM_OUTER_JOIN_TO_INNER Join Conversion
+--------QKSFM_PRED_MOVE_AROUND Predicate move around
+--------QKSFM_SET_TO_JOIN Transform set operations to joins
+--------QKSFM_SVM Simple View Merging
+--------QKSFM_TABLE_ELIM Table Elimination
+--------QKSFM_UNNEST unnest query block
+--------QKSFM_USE_CONCAT Or-optimization
+----QKSFM_XML_REWRITE XML Rewrite
+------QKSFM_CHECK_ACL_REWRITE Check ACL Rewrite
+------QKSFM_COST_XML_QUERY_REWRITE Cost Based XML Query Rewrite
+------QKSFM_XMLINDEX_REWRITE XMLIndex Rewrite
+--QKSFM_EXECUTION SQL EXECUTION
Then, we will group and introduce them according their feature.