Oracle SQL提示含义与示例 --- 概述

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2013-01-08 03:42:16

分享到 

《Oracle 高性能SQL引擎剖析：Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充，帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。 附录：提示（HINT）的含义和示例 SQL提示是用户干预优化器以及语句执行的行为的重要手段。在SQL优化方法当中，也起到重要作用。例如，在自动优化给出的SQL优化配置，提示就可能作为辅助的概要数据存在，帮助优化器找到性能更好的执行计划。不过，并不是所有提示都能作为概要数据。很多提示是伴随着优化器的新的特性出现的，有些提示也随之优化器特性的过期而消失。在11G当中，Oracle提供了一个视图（V$SQL_HINT），使我们可以看到各个版本出现的提示以及其作为概要数据的优化器版本等信息。不同的提示属于不同的SQL特性，只有当前该SQL特性启用后，该提示才能其作用。例如，“HASH_AJ”是QKSFM_CBO特性中的提示，当当前优化器模式被设置为RBO时，该提示不起作用。 其中还有一些提示是Oracle产生的递归调用语句中使用的，这些语句和提示只在内部其作用，无法被用户直接调用。 嵌入语句的提示为一段注释，格式为 /*+ <提示1> <提示2> ... */。多个提示可以存在同一段注释当中，也可以分别在多个注释段当中。并且，提示所在的注释段必须是在关键字SELECT、UPDATE、INSERT、MERGE或DELETE之后。如果语句中存在子查询，提示可以以全局方式（推荐的方式）或者本地方式出现。本地方式是指将提示嵌入子查询当中；全局方式是指将所有提示都嵌入主查询当中，并且使用查询块标识（可以使用系统自动生成的标识、也可以使用提示指定的标识串）指明该提示所影响的对象在查询中的位置（例如：<对象名>@<@查询块>）。提示参数中的对象在查询当中存在别名的话，也可以用别名代替对象名。 提示：嵌入SQL提示是一段注释。因此，除了常用的/*+<提示>*/外，还可以用--+<提示>的方式嵌入。例如： HELLODBA.COM>select --+full(u)   2  * from t_users u where user_id =1;   下面，我们将将以11.2.0.1中该视图中出现的所有提示为基础，分别解释每个提示的作用、并给出示例。提示的出现版本、概要数据版本、SQL特性以及相反提示可以通过视图V$SQL_HINT（11g）或者ALL_SQL_HINT查询。 SQL特性 首先，我们看一下存在哪些SQL特性。特性之间存在隶属关系，并且，有些特性会隶属于多个特性。下面以树状关系列出所有特性： +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                                以下描述各个提示。我们按照使用范围分类，并没有严格遵循其所属SQL特性。示例如果没有特别说明，则是运行在示例出现版本或之前版本。