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([<@查询块>])