Oracle SQL提示含义与示例 --- 查询转换提示

[English]

作者： fuyuncat

来源： www.HelloDBA.com

日期： 2013-01-08 04:15:21

分享到 

《Oracle 高性能SQL引擎剖析：Oracle SQL 优化与调优技术详解》一书的附录部分。作为对该书的补充，帮助读者理解和掌握“提示”这一项在SQL优化中使用的这一重要辅助手段。 查询转换提示 ANTIJOIN 语法：ANTIJOIN([<子查询块>]) 描述：指示优化器将主查询中的表与子查询中的表做反关联（优化器自己决定选择哪一种关联方式）操作；如果提示出现在子查询中，则不需要参数指定查询块标识； HELLODBA.COM>exec sql_explain('select /*+antijoin(@inv)*/* from t_tables t where not exists (select /*+qb_name(inv)*/1 from t_users u where t.owner=u.username)', 'BASIC OUTLINE')   ----------------------------------------- | Id  | Operation          | Name       | ----------------------------------------- |   0 | SELECT STATEMENT   |            | |   1 |  NESTED LOOPS ANTI |            | |   2 |   TABLE ACCESS FULL| T_TABLES   | |   3 |   INDEX UNIQUE SCAN| T_USERS_UK | ----------------------------------------- COALESCE_SQ 语法：COALESCE_SQ([<@查询块>]) 描述：指示优化器进行子查询合并 HELLODBA.COM>begin   2    sql_explain('SELECT /*+qb_name(mn) COALESCE_SQ(@SUB1) COALESCE_SQ(@SUB2)*/d.* FROM t_datafiles d   3         where exists(select /*+qb_name(sub1)*/1 from t_tablespaces ts where .tablespace_name=ts.tablespace_name and ts.block_size=:A)   4         and exists(select /*+qb_name(sub2)*/1 from t_tablespaces ts where d.tablespace_name=ts.tablespace_name)',   5      'BASIC OUTLINE PREDICATE');   6  end;   7  / Plan hash value: 3571377291   -------------------------------------------- | Id  | Operation          | Name          | -------------------------------------------- |   0 | SELECT STATEMENT   |               | |*  1 |  HASH JOIN SEMI    |               | |   2 |   TABLE ACCESS FULL| T_DATAFILES   | |*  3 |   TABLE ACCESS FULL| T_TABLESPACES | -------------------------------------------- NO_COALESCE_SQ 语法：NO_COALESCE_SQ([<@查询块>]) 描述：禁止优化器进行子查询合并 HELLODBA.COM>begin   2    sql_explain('SELECT /*+qb_name(mn) NO_COALESCE_SQ(@SUB1) NO_COALESCE_SQ(@SUB2)*/d.* FROM t_datafiles d   3         where exists(select /*+qb_name(sub1)*/1 from t_tablespaces ts where d.tablespace_name=ts.tablespace_name and ts.block_size=:A)   4         and exists(select /*+qb_name(sub2)*/1 from t_tablespaces ts where d.tablespace_name=ts.tablespace_name)',   5      'BASIC OUTLINE PREDICATE');   6  end;   7  / Plan hash value: 3088377872   ----------------------------------------------- | Id  | Operation           | Name            | ----------------------------------------------- |   0 | SELECT STATEMENT    |                 | |*  1 |  HASH JOIN SEMI     |                 | |   2 |   NESTED LOOPS SEMI |                 | |   3 |    TABLE ACCESS FULL| T_DATAFILES     | |*  4 |    INDEX UNIQUE SCAN| T_TABLESPACE_PK | |*  5 |   TABLE ACCESS FULL | T_TABLESPACES   | ----------------------------------------------- ELIMINATE_JOIN 语法：ELIMINATE_JOIN([<@查询块>] <表>) 描述：指示优化器进行消除管理的查询转换 HELLODBA.COM>exec sql_explain('SELECT /*+ELIMINATE_JOIN(TS)*/t.* FROM t_tables t, t_tablespaces ts where t.tablespace_name=ts.tablespace_name','TYPICAL OUTLINE');   ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |  1842 |   334K|     6   (0)| 00:00:07 | |*  1 |  TABLE ACCESS FULL| T_TABLES |  1842 |   334K|     6   (0)| 00:00:07 | ------------------------------------------------------------------------------ NO_ELIMINATE_JOIN 语法：NO_ELIMINATE_JOIN([<@查询块>] <表>) 描述：禁止优化器进行消除管理的查询转换 HELLODBA.COM>exec sql_explain('SELECT /*+NO_ELIMINATE_JOIN(TS)*/t.* FROM t_tables t, t_tablespaces ts where t.tablespace_name=ts.tablespace_name','TYPICAL OUTLINE');   -------------------------------------------------------------------------------------- | Id  | Operation          | Name            | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |                 |  1842 |   379K|   142   (1)| 00:00:01 | |   1 |  NESTED LOOPS      |                 |  1842 |   379K|   142   (1)| 00:00:01 | |*  2 |   TABLE ACCESS FULL| T_TABLES        |  1842 |   366K|   142   (1)| 00:00:01 | |*  3 |   INDEX UNIQUE SCAN| T_TABLESPACE_PK |     1 |     7 |     0   (0)| 00:00:01 | -------------------------------------------------------------------------------------- ELIMINATE_OBY 语法：ELIMINATE_OBY([<@查询块>] <表>) 描述：指示优化器进行消除ORDER BY的查询转换 示例： HELLODBA.COM>exec sql_explain('select /*+qb_name(m) ELIMINATE_OBY(@inv)*/count(password) from (select /*+qb_name(inv)*/* from t_users order by user_id)', 'TYPICAL OUTLINE');   ------------------------------------------------------------------------------ | Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT   |         |     1 |    17 |    19   (0)| 00:00:01 | |   1 |  SORT AGGREGATE    |         |     1 |    17 |            |          | |   2 |   TABLE ACCESS FULL| T_USERS |    43 |   731 |    19   (0)| 00:00:01 | ------------------------------------------------------------------------------ NO_ELIMINATE_OBY 语法：NO_ELIMINATE_OBY([<@查询块>] <表>) 描述：禁止优化器进行消除ORDER BY的查询转换 HELLODBA.COM>exec sql_explain('select /*+qb_name(m) NO_ELIMINATE_OBY(@inv)*/count(password) from (select /*+qb_name(inv)*/* from t_users order by user_id)', 'TYPICAL OUTLINE');   -------------------------------------------------------------------------------------------- | Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT              |            |     1 |    17 |     4   (0)| 00:00:01 | |   1 |  SORT AGGREGATE               |            |     1 |    17 |            |          | |   2 |   VIEW                        |            |    43 |   731 |     4   (0)| 00:00:01 | |   3 |    TABLE ACCESS BY INDEX ROWID| T_USERS    |    43 |  3698 |     4   (0)| 00:00:01 | |   4 |     INDEX FULL SCAN           | T_USERS_PK |    43 |       |     1   (0)| 00:00:01 | -------------------------------------------------------------------------------------------- EXPAND_GSET_TO_UNION 语法：EXPAND_GSET_TO_UNION([<@查询块>]) 描述：指示优化器对语句进行集合分组查询重写； HELLODBA.COM>exec sql_explain('select /*+EXPAND_GSET_TO_UNION REWRITE*/owner, status, count(object_name) from t_objects group by owner, rollup(status)', 'TYPICAL OUTLINE'); Plan hash value: 1905288239   ------------------------------------------------------------------------------------------------- | Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                |    54 |  1890 |   333   (1)| 00:00:02 | |   1 |  VIEW                          |                |    54 |  1890 |   333   (1)| 00:00:02 | |   2 |   UNION-ALL                    |                |       |       |            |          | |   3 |    SORT GROUP BY NOSORT        |                |    32 |   832 |   322   (1)| 00:00:02 | |   4 |     INDEX FULL SCAN            | T_OBJECTS_IDX1 | 47585 |  1208K|   322   (1)| 00:00:02 | |   5 |    MAT_VIEW REWRITE ACCESS FULL| MV_OBJECTS_GP  |    22 |   242 |    11   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- NO_EXPAND_GSET_TO_UNION 语法：NO_EXPAND_GSET_TO_UNION([<@查询块>]) 描述：禁止优化器对语句进行集合分组查询重写； 示例： HELLODBA.COM>exec sql_explain('select /*+NO_EXPAND_GSET_TO_UNION REWRITE*/owner, status, count(object_name) from t_objects group by owner, rollup(status)', 'TYPICAL OUTLINE');   --------------------------------------------------------------------------------------- | Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |                |    32 |   416 |   322   (1)| 00:00:02 | |   1 |  SORT GROUP BY ROLLUP|                |    32 |   416 |   322   (1)| 00:00:02 | |   2 |   INDEX FULL SCAN    | T_OBJECTS_IDX1 | 47585 |   604K|   322   (1)| 00:00:02 | --------------------------------------------------------------------------------------- EXPAND_TABLE 语法：EXPAND_TABLE([<@查询块>] <表>) 描述：指示优化器使用表扩张对分区表查询进行转换 HELLODBA.COM>exec sql_explain('select /*+EXPAND_TABLE(o)*/* from t_objects_list o','BASIC OUTLINE');   -------------------------------------------------- | Id  | Operation               | Name           | -------------------------------------------------- |   0 | SELECT STATEMENT        |                | |   1 |  VIEW                   | VW_TE_1        | |   2 |   UNION-ALL             |                | |   3 |    PARTITION LIST ALL   |                | |   4 |     TABLE ACCESS FULL   | T_OBJECTS_LIST | |   5 |    PARTITION LIST SINGLE|                | |   6 |     TABLE ACCESS FULL   | T_OBJECTS_LIST | -------------------------------------------------- NO_EXPAND_TABLE 语法：NO_EXPAND_TABLE([<@查询块>] <表>) 描述：禁止优化器使用表扩张对分区表查询进行转换 HELLODBA.COM>exec sql_explain('select /*+NO_EXPAND_TABLE(o)*/* from t_objects_list o','BASIC OUTLINE');   --------------------------------------------- | Id  | Operation          | Name           | --------------------------------------------- |   0 | SELECT STATEMENT   |                | |   1 |  PARTITION LIST ALL|                | |   2 |   TABLE ACCESS FULL| T_OBJECTS_LIST | --------------------------------------------- STAR 语法：STAR 描述：提示优化器采用星型查询（旧的方式，8i）。这种星型查询，是通过嵌套循环实现的， HELLODBA.COM>begin   2    sql_explain('select /*+ QB_NAME(Q) STAR OPTIMIZER_FEATURES_ENABLE(''8.1.7'') */   3                   u.user_id, i.table_type, t.degree, count(R_CONSTRAINT_NAME)   4                 from t_constraints c, t_tables t, t_users u, t_indexes i   5                 where c.table_name = t.table_name and c.owner = t.owner   6                 and c.r_owner =  u.username and c.constraint_name = i.index_name   7                 and t.status = :A and u.default_tablespace = :B and i.index_type = :C   8                 group by u.user_id, i.table_type, t.degree',   9      'TYPICAL OUTLINE');  10  end;  11  / Plan hash value: 2020912536   -------------------------------------------------------------------------------------- | Id  | Operation                       | Name               | Rows  | Bytes | Cost  | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                |                    |    13 |  1703 |   109 | |   1 |  SORT GROUP BY                  |                    |    13 |  1703 |   109 | |*  2 |   HASH JOIN                     |                    |    58 |  7598 |   101 | |   3 |    NESTED LOOPS                 |                    |    58 |  5626 |    83 | |   4 |     NESTED LOOPS                |                    |    58 |  3248 |    25 | |*  5 |      TABLE ACCESS FULL          | T_USERS            |     6 |   108 |     1 | |   6 |      TABLE ACCESS BY INDEX ROWID| T_CONSTRAINTS      |    10 |   380 |     4 | |*  7 |       INDEX RANGE SCAN          | T_CONSTRAINTS_IDX4 |    59 |       |     2 | |*  8 |     TABLE ACCESS BY INDEX ROWID | T_TABLES           |     1 |    41 |     1 | |*  9 |      INDEX UNIQUE SCAN          | T_TABLES_PK        |     1 |       |       | |* 10 |    TABLE ACCESS FULL            | T_INDEXES          |   648 | 22032 |    17 | -------------------------------------------------------------------------------------- STAR_TRANSFORMATION 语法：STAR_TRANSFORMATION([<@查询块>] [<事实表>] [SUBQUERIES(<维度表1> <维度表2>[ <维度表3>...])]) 描述：指示优化器进行星型转换 HELLODBA.COM>alter session set star_transformation_enabled=true;   Session altered.   HELLODBA.COM>exec sql_explain('select /*+ QB_NAME(Q) STAR_TRANSFORMATION FACT(T) NO_FACT(TS)*/* from t_tables t, t_tablespaces ts, t_users u where t.tablespace_name=ts.tablespace_name and t.owner=u.username','TYPICAL OUTLINE');   ------------------------------------------------------------------------------------------------------ | Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT                     |               |  1840 |   691K|  1515   (1)| 00:00:07 | |*  1 |  HASH JOIN                           |               |  1840 |   691K|  1515   (1)| 00:00:07 | |   2 |   TABLE ACCESS FULL                  | T_TABLESPACES |    15 |  1425 |    11   (0)| 00:00:01 | |*  3 |   HASH JOIN                          |               |  1840 |   521K|  1503   (1)| 00:00:07 | |   4 |    TABLE ACCESS FULL                 | T_USERS       |    43 |  3698 |    19   (0)| 00:00:01 | |   5 |    TABLE ACCESS BY INDEX ROWID       | T_TABLES      |  1842 |   366K|  1484   (1)| 00:00:06 | |   6 |     BITMAP CONVERSION TO ROWIDS      |               |       |       |            |          | |   7 |      BITMAP AND                      |               |       |       |            |          | |   8 |       BITMAP MERGE                   |               |       |       |            |          | |   9 |        BITMAP KEY ITERATION          |               |       |       |            |          | |  10 |         TABLE ACCESS FULL            | T_USERS       |    43 |  3698 |    19   (0)| 00:00:01 | |  11 |         BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          | |* 12 |          INDEX RANGE SCAN            | T_TABLES_IDX1 |       |       |     1   (0)| 00:00:01 | |  13 |       BITMAP MERGE                   |               |       |       |            |          | |  14 |        BITMAP KEY ITERATION          |               |       |       |            |          | |  15 |         TABLE ACCESS FULL            | T_TABLESPACES |    15 |  1425 |    11   (0)| 00:00:01 | |  16 |         BITMAP CONVERSION FROM ROWIDS|               |       |       |            |          | |* 17 |          INDEX RANGE SCAN            | T_TABLES_IDX3 |       |       |     1   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------ NO_STAR_TRANSFORMATION 语法：NO_STAR_TRANSFORMATION([<@查询块>]) 描述：禁止优化器进行星型转换 HELLODBA.COM>exec sql_explain('select /*+ QB_NAME(Q) NO_STAR_TRANSFORMATION(@Q)*/* from t_tables t, t_tablespaces ts, t_users u where t.tablespace_name=ts.tablespace_name and t.owner=u.username','TYPICAL OUTLINE');   ------------------------------------------------------------------------------------- | Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT    |               |  1840 |   691K|   173   (2)| 00:00:01 | |*  1 |  HASH JOIN          |               |  1840 |   691K|   173   (2)| 00:00:01 | |   2 |   TABLE ACCESS FULL | T_TABLESPACES |    15 |  1425 |    11   (0)| 00:00:01 | |*  3 |   HASH JOIN         |               |  1840 |   521K|   161   (1)| 00:00:01 | |   4 |    TABLE ACCESS FULL| T_USERS       |    43 |  3698 |    19   (0)| 00:00:01 | |*  5 |    TABLE ACCESS FULL| T_TABLES      |  1842 |   366K|   142   (1)| 00:00:01 | ------------------------------------------------------------------------------------- FACT 语法：FACT([<@查询块>] <表>) 描述：指示优化器在进行星型转换时，采用指定表作为事实表 见STAR_TRANSFORMATION示例 NO_FACT 语法：NO_FACT([<@查询块>] <表>) 描述：优化器在进行星型转换时，禁止采用指定表作为事实表 见STAR_TRANSFORMATION示例 FACTORIZE_JOIN 语法：FACTORIZE_JOIN(<数据集>(<表>@<子查询1> <表>@<子查询2>[ <表>@<子查询3> ...])) 描述：指示优化器将UNION/UNION-ALL查询中的子查询合并为一个内联视图； HELLODBA.COM>begin   2    sql_explain('   3         select  /*+ FACTORIZE_JOIN(@SET$1(O@SB1 O@SB2)) qb_name(sb1) */ u.username, u.created, o.object_name from t_objects o, t_users u   4         where o.owner=u.username and u.lock_date=:A   5         union all   6         select /*+ qb_name(sb2) */ u.username, u.created, o.object_name from t_objects o, t_users u   7         where o.owner=u.username and u.lock_date=:B',   8      'TYPICAL OUTLINE');   9  end;  10  /   -------------------------------------------------------------------------------------------- | Id  | Operation             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |                    | 81522 |  5891K|   193   (3)| 00:00:02 | |*  1 |  HASH JOIN            |                    | 81522 |  5891K|   193   (3)| 00:00:02 | |   2 |   VIEW                | VW_JF_SET$A6672D85 |    26 |  1118 |     6   (0)| 00:00:01 | |   3 |    UNION-ALL          |                    |       |       |            |          | |*  4 |     TABLE ACCESS FULL | T_USERS            |    13 |   325 |     3   (0)| 00:00:01 | |*  5 |     TABLE ACCESS FULL | T_USERS            |    13 |   325 |     3   (0)| 00:00:01 | |   6 |   INDEX FAST FULL SCAN| T_OBJECTS_IDX8     | 72116 |  2183K|   185   (2)| 00:00:02 | -------------------------------------------------------------------------------------------- NO_FACTORIZE_JOIN 语法：NO_FACTORIZE_JOIN(<数据集>) 描述：禁止优化器将UNION/UNION-ALL查询中的子查询合并为一个内联视图； HELLODBA.COM>begin   2    sql_explain('   3         select  /*+ NO_FACTORIZE_JOIN(@SET$1) qb_name(sb1) */ u.username, u.created, o.object_name from t_objects o, t_users u   4         where o.owner=u.username and u.lock_date=:A   5         union all   6         select /*+ qb_name(sb2) */ u.username, u.created, o.object_name from t_objects o, t_users u   7         where o.owner=u.username and u.lock_date=:B',   8      'TYPICAL OUTLINE');   9  end;  10  /   ----------------------------------------------------------------------------------------- | Id  | Operation              | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |                | 81522 |  4458K|   379  (52)| 00:00:04 | |   1 |  UNION-ALL             |                |       |       |            |          | |*  2 |   HASH JOIN            |                | 40761 |  2229K|   190   (3)| 00:00:02 | |*  3 |    TABLE ACCESS FULL   | T_USERS        |    13 |   325 |     3   (0)| 00:00:01 | |   4 |    INDEX FAST FULL SCAN| T_OBJECTS_IDX8 | 72116 |  2183K|   185   (2)| 00:00:02 | |*  5 |   HASH JOIN            |                | 40761 |  2229K|   190   (3)| 00:00:02 | |*  6 |    TABLE ACCESS FULL   | T_USERS        |    13 |   325 |     3   (0)| 00:00:01 | |   7 |    INDEX FAST FULL SCAN| T_OBJECTS_IDX8 | 72116 |  2183K|   185   (2)| 00:00:02 | ----------------------------------------------------------------------------------------- MATERIALIZE 语法：MATERIALIZE 描述：指示优化器将内联视图实体化————执行过程中会创建基于视图的临时表。 HELLODBA.COM>exec sql_explain('with v as (select /*+ MATERIALIZE qb_name(wv) */* from t_objects o where object_id<:A) select count(*) from v', 'BASIC OUTLINE');   ------------------------------------------------------------------ | Id  | Operation                  | Name                        | ------------------------------------------------------------------ |   0 | SELECT STATEMENT           |                             | |   1 |  TEMP TABLE TRANSFORMATION |                             | |   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D6601_F201F06C | |   3 |    TABLE ACCESS FULL       | T_OBJECTS                   | |   4 |   SORT AGGREGATE           |                             | |   5 |    VIEW                    |                             | |   6 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D6601_F201F06C | ------------------------------------------------------------------ INLINE 语法：INLINE                        描述：禁止优化器将内联视图实体化； HELLODBA.COM>alter session set "_with_subquery"=materialize;   Session altered.   HELLODBA.COM>exec sql_explain('with v as (select /*+ INLINE qb_name(wv) */* from t_objects o where object_id<:A) selectcount(*) from v', 'BASIC OUTLINE');   ------------------------------------------------ | Id  | Operation             | Name           | ------------------------------------------------ |   0 | SELECT STATEMENT      |                | |   1 |  SORT AGGREGATE       |                | |   2 |   INDEX FAST FULL SCAN| T_OBJECTS_IDX8 | ------------------------------------------------ MERGE 语法：MERGE([<@查询块>] [<表>]) 或 MERGE([<视图>] [<表>]) 描述：指示优化器对子查询或者视图进行合并转换。 HELLODBA.COM>exec sql_explain('select /*+ qb_name(M) merge(@inv) */* from t_tables t, (select /*+ qb_name(inv)*/* from t_objects o where object_type = :A) v where t.owner=v.owner and t.table_name=v.object_name', 'BASIC OUTLINE');   ------------------------------------------------------- | Id  | Operation                    | Name           | ------------------------------------------------------- |   0 | SELECT STATEMENT             |                | |   1 |  HASH JOIN                   |                | |   2 |   TABLE ACCESS BY INDEX ROWID| T_OBJECTS      | |   3 |    INDEX RANGE SCAN          | T_OBJECTS_IDX7 | |   4 |   TABLE ACCESS FULL          | T_TABLES       | -------------------------------------------------------  NO_MERGE 语法：NO_MERGE([<@查询块>] [<表>]) 或 NO_MERGE([<视图>] [<表>]) 描述：禁止优化器对子查询或者视图进行合并转换。 HELLODBA.COM>exec sql_explain('select /*+ qb_name(M) no_merge(@inv) */* from t_tables t, (select /*+ qb_name(inv)*/* from t_objects o where object_type = :A) v where t.owner=v.owner and t.table_name=v.object_name', 'BASIC OUTLINE');   -------------------------------------------------------- | Id  | Operation                     | Name           | -------------------------------------------------------- |   0 | SELECT STATEMENT              |                | |   1 |  HASH JOIN                    |                | |   2 |   VIEW                        |                | |   3 |    TABLE ACCESS BY INDEX ROWID| T_OBJECTS      | |   4 |     INDEX RANGE SCAN          | T_OBJECTS_IDX7 | |   5 |   TABLE ACCESS FULL           | T_TABLES       | -------------------------------------------------------- NO_PRUNE_GSETS 语法：NO_PRUNE_GSETS 描述：禁止优化器对集合分组查询进行裁剪 HELLODBA.COM>exec sql_explain('select /*+ qb_name(m) */v.owner, v.table_name, v.constraint_type, cns_cnt from (select /*+ NO_PRUNE_GSETS qb_name(gv) */owner, table_name, constraint_type, count(constraint_name) cns_cnt from t_constraints c group by cube(owner, table_name, constraint_type)) v where v.owner = ''DEMO''','BASIC OUTLINE');   --------------------------------------------------------------- | Id  | Operation                        | Name               | --------------------------------------------------------------- |   0 | SELECT STATEMENT                 |                    | |   1 |  VIEW                            |                    | |   2 |   FILTER                         |                    | |   3 |    SORT GROUP BY                 |                    | |   4 |     GENERATE CUBE                |                    | |   5 |      SORT GROUP BY               |                    | |   6 |       TABLE ACCESS BY INDEX ROWID| T_CONSTRAINTS      | |   7 |        INDEX RANGE SCAN          | T_CONSTRAINTS_IDX3 | --------------------------------------------------------------- NO_QUERY_TRANSFORMATION 语法：NO_QUERY_TRANSFORMATION      描述：禁止一切查询转换的发生； HELLODBA.COM>exec sql_explain('select /*+NO_QUERY_TRANSFORMATION*/* from (select * from t_tables)', 'BASIC');   --------------------------------------- | Id  | Operation          | Name     | --------------------------------------- |   0 | SELECT STATEMENT   |          | |   1 |  VIEW              |          | |   2 |   TABLE ACCESS FULL| T_TABLES | --------------------------------------- OR_EXPAND 语法：OR_EXPAND([<@查询块>] <表> <字段1> [<字段2> ...]) 描述：指示优化器采用“或”扩展查询转换。 HELLODBA.COM>exec sql_explain('select /*+OR_EXPAND(o created)*/* from t_objects o where created = :A or (owner = :B and object_name=:C)', 'TYPICAL OUTLINE');   ----------------------------------------------------------------------------------------------- | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |                |    32 |  3456 |     4   (0)| 00:00:05 | |   1 |  CONCATENATION               |                |       |       |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID| T_OBJECTS      |     1 |   108 |     3   (0)| 00:00:04 | |*  3 |    INDEX SKIP SCAN           | T_OBJECTS_IDX1 |     1 |       |     2   (0)| 00:00:03 | |*  4 |   TABLE ACCESS BY INDEX ROWID| T_OBJECTS      |    31 |  3348 |     1   (0)| 00:00:02 | |*  5 |    INDEX RANGE SCAN          | T_OBJECTS_IDX5 |    31 |       |     1   (0)| 00:00:02 | ----------------------------------------------------------------------------------------------- OUTER_JOIN_TO_INNER 语法：OUTER_JOIN_TO_INNER([<@查询块>]) 描述：指示优化器进行查询转换，将外关联转换为内关联 HELLODBA.COM>exec sql_explain('select /*+qb_name(M) OUTER_JOIN_TO_INNER(@M)*/ t.owner, u.user_id from t_tables t, t_users u where t.owner=u.username(+) and u.created < :A', 'TYPICAL OUTLINE');   ---------------------------------------------------------------------------------------------- | Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |               |   343 |  9947 |     4   (0)| 00:00:01 | |   1 |  NESTED LOOPS                |               |   343 |  9947 |     4   (0)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| T_USERS       |     2 |    44 |     2   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | T_USERS_IDX1  |     2 |       |     1   (0)| 00:00:01 | |*  4 |   INDEX RANGE SCAN           | T_TABLES_IDX1 |   150 |  1050 |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- NO_OUTER_JOIN_TO_INNER 语法：NO_OUTER_JOIN_TO_INNER       描述：禁止优化器进行查询转换，将外关联转换为内关联 HELLODBA.COM>exec sql_explain('select /*+qb_name(M) NO_OUTER_JOIN_TO_INNER*/ t.owner, u.user_id from t_tables t, t_users u where t.owner=u.username(+) and u.created < :A', 'TYPICAL OUTLINE');   ---------------------------------------------------------------------------------------- | Id  | Operation              | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT       |               |  2696 | 78184 |     7  (15)| 00:00:01 | |*  1 |  FILTER                |               |       |       |            |          | |*  2 |   HASH JOIN RIGHT OUTER|               |  2696 | 78184 |     7  (15)| 00:00:01 | |   3 |    TABLE ACCESS FULL   | T_USERS       |    31 |   682 |     3   (0)| 00:00:01 | |   4 |    INDEX FAST FULL SCAN| T_TABLES_IDX1 |  2696 | 18872 |     3   (0)| 00:00:01 | ---------------------------------------------------------------------------------------- ELIMINATE_OUTER_JOIN 语法：ELIMINATE_OUTER_JOIN([<@查询块>]) 描述：指示优化器进行查询转换，消除外关联（10g）； HELLODBA.COM>exec sql_explain('select /*+qb_name(M) ELIMINATE_OUTER_JOIN(@M)*/ t.owner, u.user_id from t_tables t, t_users u where t.owner=u.username(+) and u.created < :A', 'TYPICAL OUTLINE');   ---------------------------------------------------------------------------------------------- | Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |               |   103 |  2575 |     4   (0)| 00:00:01 | |   1 |  NESTED LOOPS                |               |   103 |  2575 |     4   (0)| 00:00:01 | |   2 |   TABLE ACCESS BY INDEX ROWID| T_USERS       |     2 |    38 |     2   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | T_USERS_IDX1  |     2 |       |     1   (0)| 00:00:01 | |*  4 |   INDEX RANGE SCAN           | T_TABLES_IDX1 |    48 |   288 |     1   (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- NO_ELIMINATE_OUTER_JOIN 语法：NO_ELIMINATE_OUTER_JOIN      描述：禁止优化器进行查询转换，消除外关联（10g）； HELLODBA.COM>exec sql_explain('select /*+qb_name(M) NO_ELIMINATE_OUTER_JOIN(@M)*/ t.owner, u.user_id from t_tables t, t_users u where t.owner=u.username(+) and u.created < :A', 'TYPICAL OUTLINE');   ---------------------------------------------------------------------------------------------- | Id  | Operation                 | Name             | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT          |                  |  2071 | 51775 |    13  (16)| 00:00:01 | |*  1 |  FILTER                   |                  |       |       |            |          | |*  2 |   HASH JOIN RIGHT OUTER   |                  |  2071 | 51775 |    13  (16)| 00:00:01 | |   3 |    VIEW                   | index$_join$_002 |    43 |   817 |     6  (17)| 00:00:01 | |*  4 |     HASH JOIN             |                  |       |       |            |          | |*  5 |      HASH JOIN            |                  |       |       |            |          | |   6 |       INDEX FAST FULL SCAN| T_USERS_IDX1     |    43 |   817 |     1   (0)| 00:00:01 | |   7 |       INDEX FAST FULL SCAN| T_USERS_PK       |    43 |   817 |     1   (0)| 00:00:01 | |   8 |      INDEX FAST FULL SCAN | T_USERS_UK       |    43 |   817 |     1   (0)| 00:00:01 | |   9 |    INDEX FULL SCAN        | T_TABLES_IDX1    |  2071 | 12426 |     6   (0)| 00:00:01 | ---------------------------------------------------------------------------------------------- PLACE_DISTINCT 语法：PLACE_DISTINCT               描述：指示优化器进行DISTINCT配置查询转换； HELLODBA.COM>exec sql_explain('select /*+full(u) full(t) place_distinct*/distinct t.tablespace_name, u.account_status from t_tables t, t_users u where t.owner=u.username', 'BASIC OUTLINE');   ------------------------------------------------- | Id  | Operation             | Name            | ------------------------------------------------- |   0 | SELECT STATEMENT      |                 | |   1 |  HASH UNIQUE          |                 | |   2 |   HASH JOIN           |                 | |   3 |    TABLE ACCESS FULL  | T_USERS         | |   4 |    VIEW               | VW_DTP_1B35BA0F | |   5 |     HASH UNIQUE       |                 | |   6 |      TABLE ACCESS FULL| T_TABLES        | ------------------------------------------------- NO_PLACE_DISTINCT 语法：NO_PLACE_DISTINCT            描述：禁止优化器进行DISTINCT配置查询转换； HELLODBA.COM>exec sql_explain('select /*+full(u) full(t) no_place_distinct*/distinct t.tablespace_name, u.account_status from t_tables t, t_users u where t.owner=u.username', 'BASIC');   ---------------------------------------- | Id  | Operation           | Name     | ---------------------------------------- |   0 | SELECT STATEMENT    |          | |   1 |  HASH UNIQUE        |          | |   2 |   HASH JOIN         |          | |   3 |    TABLE ACCESS FULL| T_USERS  | |   4 |    TABLE ACCESS FULL| T_TABLES | ---------------------------------------- PLACE_GROUP_BY 语法：PLACE_GROUP_BY([<@查询块>] (<表>) [<临时视图编号>]) 描述：指示优化器进行GROUP BY配置查询转换； 示例： HELLODBA.COM>exec sql_explain('SELECT /*+ qb_name(m) place_group_by(@m (t@m) 10000) */owner, max(maxbytes) FROM t_tables t, t_datafiles d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.owner', 'BASIC OUTLINE'); Plan hash value: 1494419902   ---------------------------------------------- | Id  | Operation             | Name         | ---------------------------------------------- |   0 | SELECT STATEMENT      |              | |   1 |  HASH GROUP BY        |              | |   2 |   HASH JOIN           |              | |   3 |    TABLE ACCESS FULL  | T_DATAFILES  | |   4 |    VIEW               | VW_GBF_10000 | |   5 |     HASH GROUP BY     |              | |   6 |      TABLE ACCESS FULL| T_TABLES     | ---------------------------------------------- NO_PLACE_GROUP_BY 语法：NO_PLACE_GROUP_BY([<@查询块>] (<表>) ) 描述：禁止优化器进行GROUP BY配置查询转换； HELLODBA.COM>exec sql_explain('SELECT /*+ qb_name(m) no_place_group_by(@m) */owner, max(maxbytes) FROM t_tables t, t_datafiles d WHERE t.tablespace_name = d.tablespace_name GROUP BY t.owner', 'BASIC OUTLINE');   ----------------------------------------------------- | Id  | Operation                | Name             | ----------------------------------------------------- |   0 | SELECT STATEMENT         |                  | |   1 |  HASH GROUP BY           |                  | |   2 |   HASH JOIN              |                  | |   3 |    TABLE ACCESS FULL     | T_DATAFILES      | |   4 |    VIEW                  | index$_join$_001 | |   5 |     HASH JOIN            |                  | |   6 |      INDEX FAST FULL SCAN| T_TABLES_IDX3    | |   7 |      INDEX FAST FULL SCAN| T_TABLES_IDX1    | ----------------------------------------------------- PRECOMPUTE_SUBQUERY 语法：PRECOMPUTE_SUBQUERY          描述：指示优化器预先计算子查询，根据结果再对查询进行优化。这一提示可能会导致查询结果的变化。 示例（以下查询根据子查询的计算结果改变了执行计划，消除了关联操作）： HELLODBA.COM>exec sql_explain('select /*+ PRECOMPUTE_SUBQUERY(@inv) */* from t_tables t where status in (select /*+qb_name(inv)*/status from t_indexes i where status is not null)', 'TYPICAL');   ------------------------------------------------------------------------------ | Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------ |   0 | SELECT STATEMENT  |          |  2070 |   412K|   142   (1)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| T_TABLES |  2070 |   412K|   142   (1)| 00:00:01 | ------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("STATUS"='N/A' OR "STATUS"='UNUSABLE' OR "STATUS"='VALID') PULL_PRED 语法：PULL_PRED([<@查询块>] <视图> [<谓词位置1> ...]) 描述：指示优化器将视图中的复杂谓词提取到查询块当中； HELLODBA.COM>begin   2     sql_explain('   3     SELECT /*+qb_name(outv) PULL_PRED(@OUTV V 2)*/ owner, table_name, rownum   4     FROM   5     (SELECT /*+qb_name(inv)*/t.owner, t.table_name, t.last_analyzed   6       FROM t_tables t, t_datafiles d   7       WHERE t.tablespace_name = d.tablespace_name   8       AND (t.last_analyzed) < (SELECT /*+qb_name(subq1)*/ MAX(created) FROM t_objects o)   9       AND (d.user_blocks) > (SELECT /*+qb_name(subq2)*/ MAX(LEAF_BLOCKS) FROM t_indexes i)  10       ORDER BY 1  11     )v','TYPICAL PREDICATE');  12  end;  13  / ----------------------------------------------------------------------------------------------------- | Id  | Operation                        | Name             | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                 |                  |    92 |  4324 |   500   (1)| 00:00:02 | |   1 |  COUNT                           |                  |       |       |            |          | |*  2 |   VIEW                           |                  |    92 |  4324 |   148   (3)| 00:00:01 | |   3 |    SORT ORDER BY                 |                  |    92 |  4692 |   148   (3)| 00:00:01 | |   4 |     MERGE JOIN                   |                  |    92 |  4692 |   145   (2)| 00:00:01 | |   5 |      TABLE ACCESS BY INDEX ROWID | T_DATAFILES      |    14 |   168 |     2   (0)| 00:00:01 | |   6 |       INDEX FULL SCAN            | T_DATAFILES_IDX1 |    14 |       |     1   (0)| 00:00:01 | |*  7 |      SORT JOIN                   |                  |    92 |  3588 |   143   (2)| 00:00:01 | |*  8 |       TABLE ACCESS FULL          | T_TABLES         |    92 |  3588 |   142   (1)| 00:00:01 | |   9 |        SORT AGGREGATE            |                  |     1 |     8 |            |          | |  10 |         INDEX FULL SCAN (MIN/MAX)| T_OBJECTS_IDX5   | 47585 |   371K|     2   (0)| 00:00:01 | |  11 |    SORT AGGREGATE                |                  |     1 |     3 |            |          | |  12 |     TABLE ACCESS FULL            | T_INDEXES        |  5833 | 17499 |   352   (1)| 00:00:02 | -----------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("USER_BLOCKS"> (SELECT /*+ QB_NAME ("SUBQ2") */ MAX("LEAF_BLOCKS") FROM               "T_INDEXES" "I"))    7 - access("T"."TABLESPACE_NAME"="D"."TABLESPACE_NAME")        filter("T"."TABLESPACE_NAME"="D"."TABLESPACE_NAME")    8 - filter("T"."TABLESPACE_NAME" IS NOT NULL AND "T"."LAST_ANALYZED"< (SELECT /*+ QB_NAME               ("SUBQ1") */ MAX("CREATED") FROM "T_OBJECTS" "O")) NO_PULL_PRED 语法：NO_PULL_PRED([<@查询块>] <视图> [<谓词位置1> ...])) 描述：禁止优化器抽取视图的复杂谓词条件。如果没有指定谓词位置，则禁止进行复杂谓词提取转换。 HELLODBA.COM>begin   2     sql_explain('   3     SELECT /*+qb_name(outv) NO_PULL_PRED(@OUTV V 2)*/ owner, table_name, rownum   4     FROM   5     (SELECT /*+qb_name(inv)*/t.owner, t.table_name, t.last_analyzed   6       FROM t_tables t   7       WHERE (t.last_analyzed) < (SELECT /*+qb_name(subq1)*/ MAX(created) FROM t_objects o)   8       AND (t.sample_size) > (SELECT /*+qb_name(subq2)*/ MAX(sample_size) FROM t_indexes i)   9       AND owner like ''A%''  10       ORDER BY 1  11     )v','TYPICAL');  12  end;  13  /   ------------------------------------------------------------------------------------------------ | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT              |                |     1 |    43 |   357   (1)| 00:00:02 | |   1 |  COUNT                        |                |       |       |            |          | |*  2 |   VIEW                        |                |     1 |    43 |   355   (1)| 00:00:02 | |*  3 |    TABLE ACCESS BY INDEX ROWID| T_TABLES       |     1 |    35 |     3   (0)| 00:00:01 | |*  4 |     INDEX RANGE SCAN          | T_TABLES_IDX1  |     2 |       |     2   (0)| 00:00:01 | |   5 |     SORT AGGREGATE            |                |     1 |     3 |            |          | |   6 |      TABLE ACCESS FULL        | T_INDEXES      |  5833 | 17499 |   352   (1)| 00:00:02 | |   7 |    SORT AGGREGATE             |                |     1 |     8 |            |          | |   8 |     INDEX FULL SCAN (MIN/MAX) | T_OBJECTS_IDX5 | 47585 |   371K|     2   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("LAST_ANALYZED"< (SELECT /*+ QB_NAME ("SUBQ1") */ MAX("CREATED") FROM               "T_OBJECTS" "O"))    3 - filter("T"."SAMPLE_SIZE"> (SELECT /*+ QB_NAME ("SUBQ2") */ MAX("SAMPLE_SIZE")               FROM "T_INDEXES" "I"))    4 - access("OWNER" LIKE 'A%')        filter("OWNER" LIKE 'A%') OLD_PUSH_PRED 语法：OLD_PUSH_PRED([<@查询块>] <视图>) 描述：指示优化器使用旧关联谓词推入技术； HELLODBA.COM>alter session set "_OPTIMIZER_COST_BASED_TRANSFORMATION"=off;   Session altered.   HELLODBA.COM>exec sql_explain('SELECT /*+ NO_MERGE(v) OLD_PUSH_PRED(v) */* FROM t_tables t, v_objects_sys v WHERE t.owner =v.owner(+) and t.table_name = v.object_name(+) AND t.tablespace_name = :A', 'BASIC PREDICATE');   ------------------------------------------------------------- | Id  | Operation                      | Name               | ------------------------------------------------------------- |   0 | SELECT STATEMENT               |                    | |   1 |  NESTED LOOPS OUTER            |                    | |   2 |   TABLE ACCESS BY INDEX ROWID  | T_TABLES           | |*  3 |    INDEX RANGE SCAN            | T_TABLES_IDX3      | |   4 |   PARTITION HASH SINGLE        |                    | |*  5 |    VIEW PUSHED PREDICATE       | V_OBJECTS_SYS      | |   6 |     TABLE ACCESS BY INDEX ROWID| T_OBJECTS          | |*  7 |      INDEX RANGE SCAN          | T_OBJECTS_IDX_PART | ------------------------------------------------------------- PUSH_PRED 语法：PUSH_PRED([<@查询块>] <视图> [<谓词位置1> ...]) 描述：指示优化器将关联谓词推入到视图当中； HELLODBA.COM>exec sql_explain('SELECT /*+ NO_MERGE(v) PUSH_PRED(v) */* FROM t_tables t, v_objects_sys v WHERE t.owner =v.owner(+) and t.table_name = v.object_name(+) AND t.tablespace_name = :A', 'TYPICAL'); Plan hash value: 4224448473   ------------------------------------------------------------------------------------------------- | Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT               |                |  2033 |   559K|   758   (1)| 00:00:04 | |   1 |  NESTED LOOPS OUTER            |                |  2033 |   559K|   758   (1)| 00:00:04 | |   2 |   TABLE ACCESS BY INDEX ROWID  | T_TABLES       |   184 | 37536 |    21   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN            | T_TABLES_IDX3  |   184 |       |     1   (0)| 00:00:01 | |   4 |   VIEW PUSHED PREDICATE        | V_OBJECTS_SYS  |     1 |    78 |     4   (0)| 00:00:01 | |*  5 |    FILTER                      |                |       |       |            |          | |   6 |     TABLE ACCESS BY INDEX ROWID| T_OBJECTS      |     1 |    77 |     4   (0)| 00:00:01 | |*  7 |      INDEX RANGE SCAN          | T_OBJECTS_IDX8 |     1 |       |     3   (0)| 00:00:01 | ------------------------------------------------------------------------------------------------- NO_PUSH_PRED 语法：NO_PUSH_PRED([<@查询块>] <视图>) 描述：禁止优化器将关联谓词推入到视图当中； HELLODBA.COM>exec sql_explain('SELECT /*+ NO_PUSH_PRED(v) */* FROM t_tables t, v_objects_sys v WHERE t.owner =v.owner(+) and t.table_name = v.object_name(+) AND t.tablespace_name = :A', 'BASIC PREDICATE');   ------------------------------------------------------- | Id  | Operation                    | Name           | ------------------------------------------------------- |   0 | SELECT STATEMENT             |                | |   1 |  NESTED LOOPS OUTER          |                | |   2 |   TABLE ACCESS BY INDEX ROWID| T_TABLES       | |*  3 |    INDEX RANGE SCAN          | T_TABLES_IDX3  | |   4 |   TABLE ACCESS BY INDEX ROWID| T_OBJECTS      | |*  5 |    INDEX RANGE SCAN          | T_OBJECTS_IDX8 | -------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      3 - access("T"."TABLESPACE_NAME"=:A)    5 - access("OWNER"(+)='SYS' AND "T"."TABLE_NAME"="OBJECT_NAME"(+))        filter("T"."OWNER"="OWNER"(+)) PUSH_SUBQ 语法：PUSH_SUBQ([<子查询块>]) 描述：指示优化器在做优化时提前评估子查询 HELLODBA.COM>exec sql_explain('select /*+push_subq(@inv)*/* from t_objects o where created > (select /*+qb_name(inv)*/max(created) from t_users)', 'TYPICAL');   -------------------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              |  3606 |   352K|   296   (3)| 00:00:03 | |*  1 |  TABLE ACCESS FULL          | T_OBJECTS    |  3606 |   352K|   295   (3)| 00:00:03 | |   2 |   SORT AGGREGATE            |              |     1 |     8 |            |          | |   3 |    INDEX FULL SCAN (MIN/MAX)| T_USERS_IDX1 |     1 |     8 |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("CREATED"> (SELECT /*+ PUSH_SUBQ QB_NAME ("INV") */ MAX("CREATED")               FROM "T_USERS" "T_USERS")) NO_PUSH_SUBQ 语法：NO_PUSH_SUBQ([<子查询块>])  描述：禁止优化器在做优化时提前评估子查询 HELLODBA.COM>exec sql_explain('select /*+no_push_subq(@inv)*/* from t_objects o where created > (select /*+qb_name(inv)*/max(created) from t_users)', 'TYPICAL');   -------------------------------------------------------------------------------------------- | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT            |              | 72116 |  7042K|   299   (4)| 00:00:03 | |*  1 |  FILTER                     |              |       |       |            |          | |   2 |   TABLE ACCESS FULL         | T_OBJECTS    | 72116 |  7042K|   298   (4)| 00:00:03 | |   3 |   SORT AGGREGATE            |              |     1 |     8 |            |          | |   4 |    INDEX FULL SCAN (MIN/MAX)| T_USERS_IDX1 |     1 |     8 |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("CREATED"> (SELECT /*+ NO_PUSH_SUBQ QB_NAME ("INV") */ MAX("CREATED")               FROM "T_USERS" "T_USERS")) REWRITE 语法：REWRITE([<@查询块>] [<物化视图>]) 描述：指示优化器将查询块重写为对兼容的物化视图的查询； HELLODBA.COM>exec sql_explain('select /*+qb_name(m) rewrite(@m MV_TABLES)*/ t.owner, t.table_name from t_tables t, t_objects o where t.owner = o.owner and t.table_name = o.object_name and o.object_type = ''TABLE'' and t.tablespace_name is not null and created>:A', 'TYPICAL OUTLINE');   ------------------------------------------------------------------------------------------ | Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT             |           |   119 |  4284 |     7   (0)| 00:00:01 | |*  1 |  MAT_VIEW REWRITE ACCESS FULL| MV_TABLES |   119 |  4284 |     7   (0)| 00:00:01 | ------------------------------------------------------------------------------------------ NO_REWRITE 语法：NO_REWRITE([<@查询块>]) 描述：禁止优化器将查询块重写为对物化视图的查询； HELLODBA.COM>exec sql_explain('select /*+qb_name(m) no_rewrite(@m)*/t.owner, t.table_name from t_tables t, t_objects o where t.owner = o.owner and t.table_name = o.object_name and o.object_type = ''TABLE'' and t.tablespace_name is not null and created>:A', 'TYPICAL OUTLINE');   ----------------------------------------------------------------------------------------------------- | Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                   |                |   126 | 10458 |    64   (5)| 00:00:01 | |   1 |  NESTED LOOPS                      |                |       |       |            |          | |   2 |   NESTED LOOPS                     |                |   126 | 10458 |    64   (5)| 00:00:01 | |   3 |    TABLE ACCESS BY INDEX ROWID     | T_OBJECTS      |   126 |  6048 |    58   (6)| 00:00:01 | |   4 |     BITMAP CONVERSION TO ROWIDS    |                |       |       |            |          | |   5 |      BITMAP AND                    |                |       |       |            |          | |   6 |       BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          | |   7 |        SORT ORDER BY               |                |       |       |            |          | |*  8 |         INDEX RANGE SCAN           | T_OBJECTS_IDX5 |  2523 |       |     3   (0)| 00:00:01 | |   9 |       BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          | |  10 |        SORT ORDER BY               |                |       |       |            |          | |* 11 |         INDEX RANGE SCAN           | T_OBJECTS_IDX7 |  2523 |       |    25   (0)| 00:00:01 | |* 12 |    INDEX UNIQUE SCAN               | T_TABLES_PK    |     1 |       |     0   (0)| 00:00:01 | |* 13 |   TABLE ACCESS BY INDEX ROWID      | T_TABLES       |     1 |    35 |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------------- NO_MULTIMV_REWRITE 语法：NO_MULTIMV_REWRITE           描述：禁止优化器将查询重写为对多个物化视图的查询（默认为允许）； HELLODBA.COM>create materialized view mv_objects_sys enable query rewrite   2  as select * from t_objects where owner ='SYS';   Materialized view created.   HELLODBA.COM>create materialized view mv_objects_demo enable query rewrite   2  as select * from t_objects where owner ='DEMO';   Materialized view created.   HELLODBA.COM>exec sql_explain('select /*+ */* from t_objects where owner in (''SYS'', ''DEMO'')', 'BASIC');   ---------------------------------------------------------- | Id  | Operation                      | Name            | ---------------------------------------------------------- |   0 | SELECT STATEMENT               |                 | |   1 |  VIEW                          |                 | |   2 |   UNION-ALL                    |                 | |   3 |    MAT_VIEW REWRITE ACCESS FULL| MV_OBJECTS_SYS  | |   4 |    MAT_VIEW REWRITE ACCESS FULL| MV_OBJECTS_DEMO | ----------------------------------------------------------   HELLODBA.COM>exec sql_explain('select /*+ NO_MULTIMV_REWRITE */* from t_objects where owner in (''SYS'', ''DEMO'')', 'BASIC'); Plan hash value: 3629755566   --------------------------------------- | Id  | Operation         | Name      | --------------------------------------- |   0 | SELECT STATEMENT  |           | |   1 |  TABLE ACCESS FULL| T_OBJECTS | --------------------------------------- NO_BASETABLE_MULTIMV_REWRITE 语法：NO_BASETABLE_MULTIMV_REWRITE 描述：禁止优化器将查询重写为对物化视图以及其基础表的复合查询（默认为允许）； HELLODBA.COM>exec sql_explain('select /*+ REWRITE */* from t_objects where owner in (''SYS'', ''SYSTEM'')', 'BASIC');   --------------------------------------------------------- | Id  | Operation                      | Name           | --------------------------------------------------------- |   0 | SELECT STATEMENT               |                | |   1 |  VIEW                          |                | |   2 |   UNION-ALL                    |                | |   3 |    MAT_VIEW REWRITE ACCESS FULL| MV_OBJECTS_SYS | |   4 |    TABLE ACCESS FULL           | T_OBJECTS      | ---------------------------------------------------------   HELLODBA.COM>exec sql_explain('select /*+ REWRITE NO_BASETABLE_MULTIMV_REWRITE */* from t_objects where owner in (''SYS'', ''SYSTEM'')', 'BASIC');   --------------------------------------- | Id  | Operation         | Name      | --------------------------------------- |   0 | SELECT STATEMENT  |           | |   1 |  TABLE ACCESS FULL| T_OBJECTS | --------------------------------------- REWRITE_OR_ERROR 语法：REWRITE_OR_ERROR             描述：指示优化器将查询块重写为对兼容的物化视图的查询，重写失败则抛出错误； HELLODBA.COM>explain plan for select /*+ REWRITE_OR_ERROR */* from t_objects where owner in ('SYS', 'SYSTEM'); explain plan for select /*+ REWRITE_OR_ERROR */* from t_objects where owner in ('SYS', 'SYSTEM')                      * ERROR at line 1: ORA-30393: a query block in the statement did not rewrite SET_TO_JOIN 语法：SET_TO_JOIN([<@查询块>]) 描述：指示优化器将数据集合操作转换为关联查询； HELLODBA.COM>exec sql_explain('select /*+SET_TO_JOIN(@"SET$1")*/ owner from t_tables intersect select owner from t_objects', 'TYPICAL OUTLINE');   ------------------------------------------------------------------------------------------------ | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------------------------ |   0 | SELECT STATEMENT              |                |    21 |   210 |    16  (75)| 00:00:17 | |   1 |  HASH UNIQUE                  |                |    21 |   210 |    16  (75)| 00:00:17 | |*  2 |   HASH JOIN                   |                |    17M|   168M|     6  (34)| 00:00:06 | |   3 |    INDEX FAST FULL SCAN       | T_TABLES_IDX1  |  2071 | 10355 |     2   (0)| 00:00:03 | |   4 |    BITMAP CONVERSION TO ROWIDS|                | 47585 |   232K|     2   (0)| 00:00:03 | |   5 |     BITMAP INDEX FULL SCAN    | T_OBJECTS_IDX4 |       |       |            |          | ------------------------------------------------------------------------------------------------ NO_SET_TO_JOIN 语法：NO_SET_TO_JOIN([<@查询块>]) 描述：禁止优化器将数据集合操作转换为关联查询； HELLODBA.COM>exec sql_explain('select /*+NO_SET_TO_JOIN*/ owner from t_tables intersect select owner from t_objects', 'BASIC OUTLINE');   --------------------------------------------------------- | Id  | Operation                      | Name           | --------------------------------------------------------- |   0 | SELECT STATEMENT               |                | |   1 |  INTERSECTION                  |                | |   2 |   SORT UNIQUE NOSORT           |                | |   3 |    INDEX FULL SCAN             | T_TABLES_IDX1  | |   4 |   SORT UNIQUE                  |                | |   5 |    BITMAP CONVERSION TO ROWIDS |                | |   6 |     BITMAP INDEX FAST FULL SCAN| T_OBJECTS_IDX4 | --------------------------------------------------------- TRANSFORM_DISTINCT_AGG 语法：TRANSFORM_DISTINCT_AGG(<@查询块>) 描述：指示优化器做DISTINCT聚集函数转换 HELLODBA.COM>alter session set "_optimizer_distinct_agg_transform"=false;   Session altered.   HELLODBA.COM>exec sql_explain('select /*+qb_name(M) TRANSFORM_DISTINCT_AGG(@M)*/owner, avg(avg_row_len), count(distinct table_name) from t_tables group by owner', 'TYPICAL OUTLINE');   --------------------------------------------------------------------------------- | Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------- |   0 | SELECT STATEMENT     |          |    18 |   900 |    29   (7)| 00:00:01 | |   1 |  HASH GROUP BY       |          |    18 |   900 |    29   (7)| 00:00:01 | |   2 |   VIEW               | VW_DAG_0 |  2696 |   131K|    29   (7)| 00:00:01 | |   3 |    HASH GROUP BY     |          |  2696 | 83576 |    29   (7)| 00:00:01 | |   4 |     TABLE ACCESS FULL| T_TABLES |  2696 | 83576 |    27   (0)| 00:00:01 | --------------------------------------------------------------------------------- NO_TRANSFORM_DISTINCT_AGG 语法：NO_TRANSFORM_DISTINCT_AGG    描述：禁止优化器做DISTINCT聚集函数转换 HELLODBA.COM>exec sql_explain('select /*+NO_TRANSFORM_DISTINCT_AGG*/owner, avg(avg_row_len), count(distinct table_name) from t_tables group by owner', 'TYPICAL OUTLINE');   ------------------------------------------------------------------------------- | Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     | ------------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |          |    18 |   558 |    29   (7)| 00:00:01 | |   1 |  SORT GROUP BY     |          |    18 |   558 |    29   (7)| 00:00:01 | |   2 |   TABLE ACCESS FULL| T_TABLES |  2696 | 83576 |    27   (0)| 00:00:01 | ------------------------------------------------------------------------------- UNNEST 语法：UNNEST([<子查询块>]) 描述：指示优化器对指定子查询进行子查询反嵌套查询转换 HELLODBA.COM>exec sql_explain('select /*+ UNNEST(@INV) */distinct object_name from t_objects o where object_name not in (select /*+qb_name(inv)*/table_name from t_tables t)', 'TYPICAL OUTLINE');   ---------------------------------------------------------------------------------------- | Id  | Operation             | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ---------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |                |  2030 | 85260 |   344   (5)| 00:00:02 | |   1 |  HASH UNIQUE          |                |  2030 | 85260 |   344   (5)| 00:00:02 | |*  2 |   HASH JOIN RIGHT ANTI|                | 44305 |  1817K|   335   (2)| 00:00:02 | |   3 |    INDEX FULL SCAN    | T_TABLES_PK    |  2071 | 37278 |    11   (0)| 00:00:01 | |   4 |    INDEX FULL SCAN    | T_OBJECTS_IDX1 | 47585 |  1115K|   322   (1)| 00:00:02 | ---------------------------------------------------------------------------------------- NO_UNNEST 语法：NO_UNNEST([<子查询块>]) 描述：禁止优化器对指定子查询进行子查询反嵌套查询转换 HELLODBA.COM>exec sql_explain('select /*+ NO_UNNEST(@INV) */distinct object_name from t_objects o where object_name not in (select /*+qb_name(inv)*/table_name from t_tables t)', 'BASIC PREDICATE');   --------------------------------------------- | Id  | Operation          | Name           | --------------------------------------------- |   0 | SELECT STATEMENT   |                | |   1 |  HASH UNIQUE       |                | |*  2 |   INDEX FULL SCAN  | T_OBJECTS_IDX1 | |*  3 |    INDEX RANGE SCAN| T_TABLES_PK    | ---------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter( NOT EXISTS (SELECT /*+ NO_UNNEST QB_NAME ("INV") */ 0               FROM "T_TABLES" "T" WHERE "TABLE_NAME"=:B1))    3 - access("TABLE_NAME"=:B1) USE_CONCAT 语法：USE_CONCAT([<@查询块>]) 描述：指示优化器使用拼接获取多个OR关系式连接的谓词条件结果 HELLODBA.COM>exec sql_explain('select /*+qb_name(M) USE_CONCAT(@M)*/* from t_objects o where created = :A or (owner = :B and object_name=:C)', 'TYPICAL OUTLINE');   ----------------------------------------------------------------------------------------------- | Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     | ----------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |                |    28 |  2800 |     6   (0)| 00:00:01 | |   1 |  CONCATENATION               |                |       |       |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID| T_OBJECTS      |     1 |   100 |     4   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | T_OBJECTS_IDX8 |     1 |       |     3   (0)| 00:00:01 | |*  4 |   TABLE ACCESS BY INDEX ROWID| T_OBJECTS      |    27 |  2700 |     2   (0)| 00:00:01 | |*  5 |    INDEX RANGE SCAN          | T_OBJECTS_IDX5 |    27 |       |     1   (0)| 00:00:01 | ----------------------------------------------------------------------------------------------- NO_EXPAND 语法：NO_EXPAND([<@查询块>]) 描述：禁止优化器使用拼接获取多个OR关系式连接的谓词条件结果 HELLODBA.COM>exec sql_explain('select /*+qb_name(M) NO_EXPAND(@M)*/* from t_objects o where created = :A or (owner = :B and object_name=:C)', 'TYPICAL OUTLINE');   --------------------------------------------------------------------------------------------------- | Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                 |                |    27 |  2700 |    11  (10)| 00:00:01 | |   1 |  TABLE ACCESS BY INDEX ROWID     | T_OBJECTS      |    27 |  2700 |    11  (10)| 00:00:01 | |   2 |   BITMAP CONVERSION TO ROWIDS    |                |       |       |            |          | |   3 |    BITMAP OR                     |                |       |       |            |          | |   4 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          | |*  5 |      INDEX RANGE SCAN            | T_OBJECTS_IDX5 |       |       |     1   (0)| 00:00:01 | |   6 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |            |          | |   7 |      SORT ORDER BY               |                |       |       |            |          | |*  8 |       INDEX RANGE SCAN           | T_OBJECTS_IDX8 |       |       |     3   (0)| 00:00:01 | --------------------------------------------------------------------------------------------------- USE_TTT_FOR_GSETS 语法：USE_TTT_FOR_GSETS            描述：指示优化器将集合分组查询转换为对多个系统临时表的直接插入和加载操作。 HELLODBA.COM>exec sql_explain('select /*+ USE_TTT_FOR_GSETS qb_name(gv) */owner, count(constraint_name) cns_cnt from t_constraints c group by cube(owner)','BASIC');   --------------------------------------------------------------------- | Id  | Operation                     | Name                        | --------------------------------------------------------------------- |   0 | SELECT STATEMENT              |                             | |   1 |  TEMP TABLE TRANSFORMATION    |                             | |   2 |   LOAD AS SELECT              |                             | |   3 |    SORT GROUP BY NOSORT ROLLUP|                             | |   4 |     INDEX FULL SCAN           | T_CONSTRAINTS_IDX3          | |   5 |   VIEW                        |                             | |   6 |    TABLE ACCESS FULL          | SYS_TEMP_0FD9D6605_F2042F13 | --------------------------------------------------------------------- NO_ORDER_ROLLUPS 语法：NO_ORDER_ROLLUPS             描述：未知。可能是在旧版本的优化器当中，控制优化器对ROLLUP语句进行查询转换，避免排序。 OPAQUE_XCANONICAL 语法：OPAQUE_XCANONICAL 描述：未知。估计是用于正则表达式的提示。 LIKE_EXPAND 语法：LIKE_EXPAND 描述：未知