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

日期： 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             |                  |       |       |            |          |<