Oracle SQL Hints --- Hierarchy Query And XML Query Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 05:12:05

Share to 

Hierarchy Query Hints CONNECT_BY_CB_WHR_ONLY       Usage: CONNECT_BY_CB_WHR_ONLY([<@Block>]) Description: Unknown. It might instruct the optimizer to consider the WHERE clause only when performing CONNECT BY operation. There is an optimizer parameter, _optimizer_connect_by_cb_whr_only, to control this feature. NO_CONNECT_BY_CB_WHR_ONLY Usage: NO_CONNECT_BY_CB_WHR_ONLY([<@Block>]) Description: Unknown. It might prevent the optimizer to consider the WHERE clause only when performing CONNECT BY operation. CONNECT_BY_COMBINE_SW Usage: CONNECT_BY_COMBINE_SW([<@Block>]) Description: Instructs the optimizer to consider the predication in START WITH when performing CONNECT BY operation. 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 Usage: NO_CONNECT_BY_COMBINE_SW([<@Block>]) Description: Prevents the optimizer to consider the predication in START WITH when performing CONNECT BY operation. 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 Usage: CONNECT_BY_COST_BASED([<@Block>]) Description: Instructs the optimizer to transform CONNECT BY based on cost. Demo (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 Usage: NO_CONNECT_BY_COST_BASED([<@Block>]) Description: Instructs the optimizer to transform CONNECT BY based on cost. Demo (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 Usage: CONNECT_BY_ELIM_DUPS([<@Block>]) Description: Instructs the SQL executor to eliminate duplicated data when perform CONNECT BY operation. 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 Usage: NO_CONNECT_BY_ELIM_DUPS([<@Block>]) Description: Prevents the SQL executor to eliminate duplicated data when perform CONNECT BY operation. 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 Usage: CONNECT_BY_FILTERING([<@Block>]) Description: Instructs the SQL executor to filter data when perform CONNECT BY operation. 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 Usage: NO_CONNECT_BY_FILTERING([<@Block>]) Description: Prevents the SQL executor to filter data when perform CONNECT BY operation. 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 Usage: COST_XML_QUERY_REWRITE escription: Instructs the optimizer to perform XML query rewrite based on cost. By default, the optimizer will perform XML query rewrite based on rules. We can the reason why cost based rewrite failed from the trace file of 19027 event. 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 Usage: NO_COST_XML_QUERY_REWRITE    Description: Prevent the optimizer to perform XML query rewrite based on cost. Refer to the demo of COST_XML_QUERY_REWRITE FORCE_XML_QUERY_REWRITE Usage: FORCE_XML_QUERY_REWRITE      Description: Force the optimizer to rewrite the XML query. 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 Usage: NO_XML_QUERY_REWRITE Description: Forbidden the optimizer to rewrite the XML query. 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 Usage: XML_DML_RWT_STMT             Description: 指示优化器对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 Usage: NO_XML_DML_REWRITE Description: 禁止优化器对DML语句中的XML数据操作（DELETEXML、UPDATEXML、INSERTCHILDXML、INSERTCHILDXMLBEFORE和INSERTCHILDXMLAFTER）进行重写 见上例 XMLINDEX_REWRITE Usage: XMLINDEX_REWRITE             Description: 指示优化器使用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 Usage: NO_XMLINDEX_REWRITE Description: 禁止优化器使用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 Usage: XMLINDEX_REWRITE_IN_SELECT   Description: Instructs the optimizer using XML index into rewrite SELECT clause. 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 Usage: NO_XMLINDEX_REWRITE_IN_SELECT Description: Prevents the optimizer using XML index into rewrite SELECT clause. 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 Usage: CHECK_ACL_REWRITE            Description: Unknown. It might instruct the optimizer to check the Access Control List when rewriting XML query. NO_CHECK_ACL_REWRITE Usage: NO_CHECK_ACL_REWRITE         Description: Unknown. It might prevent the optimizer to check the Access Control List when rewriting XML query. INLINE_XMLTYPE_NT Usage: INLINE_XMLTYPE_NT Description: Unknown. It might to instruct the optimizer to transform the inline XMLTYPE to Nested Table. XMLINDEX_SEL_IDX_TBL Usage: XMLINDEX_SEL_IDX_TBL Description: Unknown