HelloDBA [English]
搜索Internet 搜索 HelloDBABA
  Oracle技术站。email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com   acoug  acoug 

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

[English]

作者: fuyuncat

来源: www.HelloDBA.com

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

分享到  新浪微博 腾讯微博 人人网 i贴吧 开心网 豆瓣 淘宝 推特 Facebook GMail Blogger Orkut Google Bookmarks

《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数据操作(DELETEXMLUPDATEXMLINSERTCHILDXMLINSERTCHILDXMLBEFOREINSERTCHILDXMLAFTER)进行重写

见上例

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

描述:未知。

Top

Copyright ©2005,HelloDBA.Com 保留一切权利

申明
by fuyuncat