HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Oracle SQL Hints --- Abstraction


Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 03:42:16

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao


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-

+--------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.


Copyright ©2005, HelloDBA.Com All reseverd.

by fuyuncat