Oracle SQL提示含义与示例 --- 层次查询和XML查询提示

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2013-01-08 05:12:05

分享到 

《Oracle 高性能SQL引擎剖析：Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充，帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。 层次查询提示 CONNECT_BY_CB_WHR_ONLY       语法：CONNECT_BY_CB_WHR_ONLY([<@查询块>]) 描述：未知。可能是指示优化器进行CONNECT BY操作时，仅结合WHERE条件关联数据进行查询转换。优化器参数“_optimizer_connect_by_cb_whr_only”控制这一特性。 NO_CONNECT_BY_CB_WHR_ONLY 语法：NO_CONNECT_BY_CB_WHR_ONLY([<@查询块>]) 描述：未知。可能是禁止优化器进行CONNECT BY操作时，结合WHERE条件关联数据进行查询转换. 优化器参数“_optimizer_connect_by_cb_whr_only”控制这一特性。 CONNECT_BY_COMBINE_SW 语法：CONNECT_BY_COMBINE_SW([<@查询块>]) 描述：指示优化器进行CONNECT BY操作时，结合START WITH的条件关联数据 HELLODBA.COM>exec sql_explain('select /*+NO_CONNECT_BY_FILTERING(@SEL$1) CONNECT_BY_COMBINE_SW(@SEL$1)*/owner, table_name, level from t_constraints connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A','BASIC OUTLINE PREDICATE');   ----------------------------------------------------------------- | Id  | Operation                               | Name          | ----------------------------------------------------------------- |   0 | SELECT STATEMENT                        |               | |*  1 |  CONNECT BY NO FILTERING WITH START-WITH|               | |   2 |   TABLE ACCESS FULL                     | T_CONSTRAINTS | ----------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------      1 - access("OWNER"=PRIOR "R_OWNER" AND "CONSTRAINT_NAME"=PRIOR               "R_CONSTRAINT_NAME")        filter("OWNER"=:A) NO_CONNECT_BY_COMBINE_SW 语法：NO_CONNECT_BY_COMBINE_SW([<@查询块>]) 描述：禁止优化器进行CONNECT BY操作时，结合START WITH的条件关联数据 HELLODBA.COM>exec sql_explain('select /*+NO_CONNECT_BY_FILTERING(@"SEL$1") NO_CONNECT_BY_COMBINE_SW(@"SEL$1")*/owner, table_name, level from t_constraints connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A','BASIC OUTLINE PREDICATE');   ------------------------------------------------------ | Id  | Operation                    | Name          | ------------------------------------------------------ |   0 | SELECT STATEMENT             |               | |*  1 |  CONNECT BY WITHOUT FILTERING|               | |*  2 |   TABLE ACCESS FULL          | T_CONSTRAINTS | |   3 |   TABLE ACCESS FULL          | T_CONSTRAINTS | ------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - access("OWNER"=PRIOR "R_OWNER" AND "CONSTRAINT_NAME"=PRIOR               "R_CONSTRAINT_NAME")    2 - filter("OWNER"=:A) CONNECT_BY_COST_BASED 语法：CONNECT_BY_COST_BASED([<@查询块>]) 描述：指示优化器基于代价对CONNECT BY进行转换 示例（10.2.0.4）： HELLODBA.COM>exec sql_explain('select /*+QB_NAME(M) CONNECT_BY_COST_BASED(@M)*/owner, table_name, level from t_constraints where constraint_type=:B connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A and level > 2','BASIC OUTLINE PREDICATE'); … …   Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       … …       OUTLINE(@"M")       CONNECT_BY_COST_BASED(@"M")       OUTLINE(@"SEL$C20E7289")       CONNECT_BY_FILTERING(@"SEL$C20E7289")       … …       IGNORE_OPTIM_EMBEDDED_HINTS       END_OUTLINE_DATA   */ NO_CONNECT_BY_COST_BASED 语法：NO_CONNECT_BY_COST_BASED([<@查询块>]) 描述：禁止优化器基于代价对CONNECT BY进行转换                             示例（10.2.0.4）： HELLODBA.COM>exec sql_explain('select /*+QB_NAME(M) NO_CONNECT_BY_COST_BASED(@M)*/owner, table_name, level from t_constraints where constraint_type=:B connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A and level > 2','BASIC OUTLINE PREDICATE'); BEGIN sql_explain('select /*+QB_NAME(M) NO_CONNECT_BY_COST_BASED(@M)*/owner, table_name, level from t_constraints where constraint_type=:B connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A and level > 2','BASIC OUTLINE PREDICATE'); END;   * ERROR at line 1: ORA-01788: CONNECT BY clause required in this query block ORA-06512: at "SYS.SQL_EXPLAIN", line 25 ORA-06512: at line 1 CONNECT_BY_ELIM_DUPS 语法：CONNECT_BY_ELIM_DUPS([<@查询块>]) 描述：指示优化器进行CONNECT BY操作时，在关联数据时消除重复值； HELLODBA.COM>exec sql_explain('SELECT /*+qb_name(BV) CONNECT_BY_ELIM_DUPS(@"BV")*/DISTINCT LEVEL FROM DUAL CONNECT BY LEVEL <= 10', 'TYPICAL OUTLINE');   --------------------------------------------------------------------------------------- | Id  | Operation                              | Name | Rows  | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                       |      |     1 |     3  (34)| 00:00:01 | |   1 |  HASH UNIQUE                           |      |     1 |     3  (34)| 00:00:01 | |*  2 |   CONNECT BY WITHOUT FILTERING (UNIQUE)|      |       |            |          | |   3 |    FAST DUAL                           |      |     1 |     2   (0)| 00:00:01 | --------------------------------------------------------------------------------------- NO_CONNECT_BY_ELIM_DUPS 语法：NO_CONNECT_BY_ELIM_DUPS([<@查询块>]) 描述：禁止优化器进行CONNECT BY操作时，在关联数据时消除重复值； HELLODBA.COM>exec sql_explain('SELECT /*+qb_name(BV) NO_CONNECT_BY_ELIM_DUPS(@"BV")*/DISTINCT LEVEL FROM DUAL CONNECT BY LEVEL <= 10', 'TYPICAL OUTLINE');   ------------------------------------------------------------------------------ | Id  | Operation                     | Name | Rows  | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT              |      |     1 |     3  (34)| 00:00:01 | |   1 |  HASH UNIQUE                  |      |     1 |     3  (34)| 00:00:01 | |*  2 |   CONNECT BY WITHOUT FILTERING|      |       |            |          | |   3 |    FAST DUAL                  |      |     1 |     2   (0)| 00:00:01 | ------------------------------------------------------------------------------ CONNECT_BY_FILTERING 语法：CONNECT_BY_FILTERING([<@查询块>]) 描述：指示优化器进行CONNECT BY操作时，在获取数据时做过滤 HELLODBA.COM>exec sql_explain('select /*+CONNECT_BY_FILTERING(@"SEL$1")*/owner, table_name, level from t_constraints c start with constraint_name in (select index_name from t_indexes t where t.owner = c.owner ) connect by prior r_owner=owner and prior r_constraint_name = constraint_name','BASIC');   --------------------------------------------------- | Id  | Operation                 | Name          | --------------------------------------------------- |   0 | SELECT STATEMENT          |               | |   1 |  CONNECT BY WITH FILTERING|               | |   2 |   NESTED LOOPS            |               | |   3 |    TABLE ACCESS FULL      | T_CONSTRAINTS | |   4 |    INDEX UNIQUE SCAN      | T_INDEXES_PK  | |   5 |   HASH JOIN               |               | |   6 |    CONNECT BY PUMP        |               | |   7 |    TABLE ACCESS FULL      | T_CONSTRAINTS | ---------------------------------------------------   NO_CONNECT_BY_FILTERING 语法：NO_CONNECT_BY_FILTERING([<@查询块>]) 描述：指示优化器进行CONNECT BY操作时，在关联数据时做过滤 HELLODBA.COM>exec sql_explain('select /*+NO_CONNECT_BY_FILTERING(@"SEL$1")*/owner, table_name, level from t_constraints connect by prior r_owner=owner and prior r_constraint_name = constraint_name start with owner=:A','BASIC');   ----------------------------------------------------------------- | Id  | Operation                               | Name          | ----------------------------------------------------------------- |   0 | SELECT STATEMENT                        |               | |   1 |  CONNECT BY NO FILTERING WITH START-WITH|               | |   2 |   TABLE ACCESS FULL                     | T_CONSTRAINTS | ----------------------------------------------------------------- XML查询提示 COST_XML_QUERY_REWRITE 语法：COST_XML_QUERY_REWRITE 描述：指示优化器（采用关系型对象查询重写技术）对含有XML对象的查询进行基于代价的查询重写。默认情况下，优化器采用基于规则的方式进行查询重写。对优化器解析过程做19027事件跟踪，可以看到重写跟踪或者不能重写的原因。 HELLODBA.COM>desc xml_test  Name                                          Null?    Type  -------------------------------------------- --------- ----------------------------------------------- TABLE of SYS.XMLTYPE(XMLSchema "http://www.HelloDBA.com/xml/schema.xsd" Element "test-xml") STORAGE Object-relational TYPE "test-xml748_T"   HELLODBA.COM>exec sql_explain('SELECT /*+*/EXTRACT(VALUE(x), ''/test-xml/id'') FROM xml_test x WHERE  EXTRACTVALUE(value(x), ''/test-xml/name'') = ''aaa''', 'TYPICAL OUTLINE'); Plan hash value: 3532887779 … … Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       FULL(@"SEL$1" "X"@"SEL$1")       OUTLINE_LEAF(@"SEL$1")       NO_COST_XML_QUERY_REWRITE       XMLINDEX_REWRITE_IN_SELECT       XMLINDEX_REWRITE       XML_DML_RWT_STMT       … …       END_OUTLINE_DATA   */   HELLODBA.COM>exec sql_explain('SELECT /*+COST_XML_QUERY_REWRITE*/EXTRACT(VALUE(x), ''/test-xml/id'') FROM xml_test x WHERE  EXTRACTVALUE(value(x), ''/test-xml/name'') = ''aaa''', 'TYPICAL OUTLINE'); Plan hash value: 3532887779 … … Outline Data -------------     /*+       BEGIN_OUTLINE_DATA       FULL(@"SEL$1" "X"@"SEL$1")       OUTLINE_LEAF(@"SEL$1")       XMLINDEX_REWRITE_IN_SELECT       XMLINDEX_REWRITE       XML_DML_RWT_STMT       FORCE_XML_QUERY_REWRITE       … …       END_OUTLINE_DATA   */ NO_COST_XML_QUERY_REWRITE 语法：NO_COST_XML_QUERY_REWRITE    描述：禁止优化器对含有XML对象的查询进行基于代价的查询重写。 见上例。 FORCE_XML_QUERY_REWRITE 语法：FORCE_XML_QUERY_REWRITE      描述：强制优化器对XML查询操作进行重写 HELLODBA.COM>exec sql_explain('select /*+FORCE_XML_QUERY_REWRITE*/rowid from xml_test where existsnode(object_value,''/test-xml[name="aaa"]'')=1', 'BASIC PREDICATE'); Plan hash value: 3532887779 … … Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("XML_TEST"."SYS_NC00009$"='aaa') NO_XML_QUERY_REWRITE 语法：NO_XML_QUERY_REWRITE 描述：禁止优化器对XML查询操作进行重写 HELLODBA.COM>exec sql_explain('select /*+NO_XML_QUERY_REWRITE*/rowid from xml_test where existsnode(object_value,''/test-xml[name="aaa"]'')=1', 'BASIC PREDICATE'); Plan hash value: 3532887779 … … Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter(EXISTSNODE(SYS_MAKEXML('43408109EBBC4C0C942DC83C286B7241',               4347,"XML_TEST"."XMLEXTRA","XML_TEST"."XMLDATA"),'/test-xml[name="aaa"]'               )=1) XML_DML_RWT_STMT 语法：XML_DML_RWT_STMT             描述：指示优化器对XML数据操作进行重写。概要数据中存在该提示的语句，如果再嵌入NO_XML_DML_REWRITE进行解析，会导致该提示从概要数据中消失。 HELLODBA.COM>CREATE TABLE xmltest OF XMLType xmltype STORE as BINARY XML;   Table created.   HELLODBA.COM>exec sql_explain('UPDATE /*+XML_DML_RWT_STMT*/xmltest SET OBJECT_VALUE = deleteXML(OBJECT_VALUE, ''/product_details[@id=1]/product[@id=2]'')', 'BASIC PREDICATE');   --------------------------------------- | Id  | Operation           | Name    | --------------------------------------- |   0 | UPDATE STATEMENT    |         | |   1 |  UPDATE             | XMLTEST | |   2 |   TABLE ACCESS FULL | XMLTEST | |   3 |   SORT AGGREGATE    |         | |*  4 |    XPATH EVALUATION |         | ---------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      4 - filter(TO_BINARY_DOUBLE("P"."C_01$")=2.0E+000D)   PL/SQL procedure successfully completed.   HELLODBA.COM>exec sql_explain('UPDATE /*+NO_XML_DML_REWRITE*/xmltest SET OBJECT_VALUE = deleteXML(OBJECT_VALUE, ''/product_details[@id=1]/product[@id=2]'')', 'BASIC PREDICATE'); Plan hash value: 3332225581   -------------------------------------- | Id  | Operation          | Name    | -------------------------------------- |   0 | UPDATE STATEMENT   |         | |   1 |  UPDATE            | XMLTEST | |   2 |   TABLE ACCESS FULL| XMLTEST | --------------------------------------   PL/SQL procedure successfully completed. NO_XML_DML_REWRITE 语法：NO_XML_DML_REWRITE 描述：禁止优化器对DML语句中的XML数据操作（DELETEXML、UPDATEXML、INSERTCHILDXML、INSERTCHILDXMLBEFORE和INSERTCHILDXMLAFTER）进行重写 见上例 XMLINDEX_REWRITE 语法：XMLINDEX_REWRITE             描述：指示优化器使用XML索引（XMLIndex）进行重写 HELLODBA.COM>create table t_xml_tab1 (ID NUMBER NOT NULL, XMLDATA XMLType) xmltype column "XMLDATA" STORE AS BINARY XML;   Table created.   HELLODBA.COM>create index t_xml_tab1_IDX_1 on t_xml_tab1(XMLDATA) indextype is xdb.xmlindex PARAMETERS ( 'PATHS ( INCLUDE (/ROOT))' );   Index created.   HELLODBA.COM>exec sql_explain('SELECT /*+ XMLINDEX_REWRITE */ COUNT(ID) FROM t_xml_tab1 t WHERE t.XMLDATA.EXISTSNODE(''/ROOT'') = 1', 'BASIC OUTLINE');   ------------------------------------------------------------------------ | Id  | Operation                     | Name                           | ------------------------------------------------------------------------ |   0 | SELECT STATEMENT              |                                | |   1 |  SORT AGGREGATE               |                                | |   2 |   NESTED LOOPS SEMI           |                                | |   3 |    TABLE ACCESS FULL          | T_XML_TAB1                     | |   4 |    TABLE ACCESS BY INDEX ROWID| SYS138768_T_XML_TAB_PATH_TABLE | |   5 |     INDEX RANGE SCAN          | SYS138768_T_XML_TAB_PIKEY_IX   | ------------------------------------------------------------------------ NO_XMLINDEX_REWRITE 语法：NO_XMLINDEX_REWRITE 描述：禁止优化器使用XML索引（XMLIndex）进行重写 HELLODBA.COM>exec sql_explain('SELECT /*+ NO_XMLINDEX_REWRITE */ COUNT(ID) FROM t_xml_tab1 t WHERE t.XMLDATA.EXISTSNODE(''/ROOT'') = 1', 'BASIC OUTLINE');   ----------------------------------------- | Id  | Operation          | Name       | ----------------------------------------- |   0 | SELECT STATEMENT   |            | |   1 |  SORT AGGREGATE    |            | |   2 |   TABLE ACCESS FULL| T_XML_TAB1 | ----------------------------------------- XMLINDEX_REWRITE_IN_SELECT 语法：XMLINDEX_REWRITE_IN_SELECT   描述：指示优化器将XML索引用于重写SELECT子句 HELLODBA.COM>exec sql_explain('SELECT /*+ XMLINDEX_REWRITE_IN_SELECT */XMLQuery(''$r/ROOT'' PASSING t.XMLDATA AS "r" RETURNING CONTENT) FROM t_xml_tab1 t WHERE t.XMLDATA.EXISTSNODE(''/ROOT'') = 1', 'BASIC OUTLINE');   ----------------------------------------------------------------------- | Id  | Operation                    | Name                           | ----------------------------------------------------------------------- |   0 | SELECT STATEMENT             |                                | |   1 |  SORT GROUP BY               |                                | |   2 |   TABLE ACCESS BY INDEX ROWID| SYS138768_T_XML_TAB_PATH_TABLE | |   3 |    INDEX RANGE SCAN          | SYS138768_T_XML_TAB_PIKEY_IX   | |   4 |  NESTED LOOPS SEMI           |                                | |   5 |   TABLE ACCESS FULL          | T_XML_TAB1                     | |   6 |   TABLE ACCESS BY INDEX ROWID| SYS138768_T_XML_TAB_PATH_TABLE | |   7 |    INDEX RANGE SCAN          | SYS138768_T_XML_TAB_PIKEY_IX   | ----------------------------------------------------------------------- NO_XMLINDEX_REWRITE_IN_SELECT 语法：NO_XMLINDEX_REWRITE_IN_SELECT 描述：指示优化器将XML索引仅用于WHERE子句过滤而不用于重写SELECT子句 HELLODBA.COM>exec sql_explain('SELECT /*+ NO_XMLINDEX_REWRITE_IN_SELECT */XMLQuery(''$r/ROOT'' PASSING t.XMLDATA AS "r" RETURNING CONTENT) FROM t_xml_tab1 t WHERE t.XMLDATA.EXISTSNODE(''/ROOT'') = 1', 'BASIC OUTLINE');   ----------------------------------------------------------------------- | Id  | Operation                    | Name                           | ----------------------------------------------------------------------- |   0 | SELECT STATEMENT             |                                | |   1 |  NESTED LOOPS SEMI           |                                | |   2 |   TABLE ACCESS FULL          | T_XML_TAB1                     | |   3 |   TABLE ACCESS BY INDEX ROWID| SYS138768_T_XML_TAB_PATH_TABLE | |   4 |    INDEX RANGE SCAN          | SYS138768_T_XML_TAB_PIKEY_IX   | ----------------------------------------------------------------------- CHECK_ACL_REWRITE 语法：CHECK_ACL_REWRITE            描述：未知。可能是指示优化器在对XML查询重写时，检查访问控制列表（Access Control List） NO_CHECK_ACL_REWRITE 语法：NO_CHECK_ACL_REWRITE         描述：未知。可能是指示优化器在对XML查询重写时，不检查访问控制列表（Access Control List） INLINE_XMLTYPE_NT 语法：INLINE_XMLTYPE_NT 描述：未知。可能是指示优化器将语句中内联XMLTYPE数据转换为嵌套表（Nested Table）。 XMLINDEX_SEL_IDX_TBL 语法：XMLINDEX_SEL_IDX_TBL 描述：未知。