HelloDBA [中文]
Search Internet Search HelloDBA
  Oracle Technology Site. email: fuyuncat@gmail.com  MSN: fuyuncat@hotmail.com Add to circle  acoug  acoug 

Oracle SQL Hints --- Quert Transformation Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

Date:  2013-01-08 04:15:21

Share to  Twitter Facebook GMail Blogger Orkut Google Bookmarks Sina Weibo QQ Renren Tieba Kaixin Douban Taobao

 

Query Transformation Hints

ANTIJOIN

Usage: ANTIJOIN([<@SubBlock>])

Description: Instructs the optimizer to perform any type of anti-join with the table in sub query.

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

Usage: COALESCE_SQ([<@Block>])

Description: Instructs the optimizer to perform sub query coalescent.

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

Usage: NO_COALESCE_SQ([<@Block>])

Description: Prevents the optimizer to perform sub query coalescent.

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

Usage: ELIMINATE_JOIN([<@Block>] <Table>)

Description: Instructs the optimizer to perform query transformation to 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

Usage: NO_ELIMINATE_JOIN([<@Block>] <Table>)

Description: Prevents the optimizer to perform query transformation to 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

Usage: ELIMINATE_OBY([<@Block>] <Table>)

Description: Instructs the optimizer to perform query transformation to eliminate 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

Usage: NO_ELIMINATE_OBY([<@Block>] <Table>)

Description: Prevents the optimizer to perform query transformation to eliminate 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

Usage: EXPAND_GSET_TO_UNION([<@Block>])

Description: Instructs the optimizer to transformation Grouping Sets 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

Usage: NO_EXPAND_GSET_TO_UNION([<@Block>])

Description: Prevents the optimizer to transformation Grouping Sets 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

Usage: EXPAND_TABLE([<@Block>] <Table>)

Description: Instructs the optimizer to perform query transformation to expand partitioned 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

Usage: NO_EXPAND_TABLE([<@Block>] <Table>)

Description: Prevents the optimizer to perform query transformation to expand partitioned 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

Usage: STAR

Description: Instructs the optimizer to use the old method in 8i to perform start query. The old method of star query utilizes nested-loop join.

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

Usage: STAR_TRANSFORMATION([<@Block>] [<Fact Table>] [SUBQUERIES(<Dimension Table1> < Dimension Table 2>[ < Dimension Table 3>...])])

Description: Instructs the optimizer to perform star query transformation.

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

Usage: NO_STAR_TRANSFORMATION([<@Block>])

Description: Prevents the optimizer to perform star query 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

Usage: FACT([<@Block>] <Table>)

Description: Instructs the optimizer to use the specified table as fact table when performing star query transformation.

STAR_TRANSFORMATION示例

NO_FACT

Usage: NO_FACT([<@Block>] <Table>)

Description: Prevents the optimizer to use the specified table as fact table when performing star query transformation.

Refer to the demo of STAR_TRANSFORMATION

FACTORIZE_JOIN

Usage: FACTORIZE_JOIN(<Data Set>(<Table>@<blok1> <Table>@<block2>[ <Table>@<block3> ...]))

Description: Instructs the optimizer to merge the sub queries in UNION/UNION-ALL to an inline view.

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

Usage: NO_FACTORIZE_JOIN(<Data Set>)

Description: Prevents the optimizer to merge the sub queries in UNION/UNION-ALL to an inline view.

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

Usage: MATERIALIZE

Description: Instructs the optimizer to materialize the inline view, which will generate a temp table based on the view.

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

Usage: INLINE                      

Description: Prevents the optimizer to materialize the inline view

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

Usage: MERGE([<@Block>] [<Table>]) or MERGE([<View>] [<Table>])

Description: Instructs the optimizer to perform query transformation to merge the specified view or sub query.

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

Usage: NO_MERGE([<@Block>] [<Table>]) or NO_MERGE([<view>] [<Table>])

Description: Prevents the optimizer to perform query transformation to merge the specified view or sub query.

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

Usage: NO_PRUNE_GSETS

Description: Prevents the optimizer to prune Grouping Sets query.

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

Usage: NO_QUERY_TRANSFORMATION     

Description: Prevents the optimizer to perform any 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

Usage: OR_EXPAND([<@Block>] <Table> <Column1> [<Column2> ...])

Description: Instructs the optimizer to perform OR EXPAND query transformation.

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

Usage: OUTER_JOIN_TO_INNER([<@Block>])

Description: Instructs the optimizer to perform query transformation to transform outer join to指示inner join.

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

Usage: NO_OUTER_JOIN_TO_INNER      

Description: Prevents the optimizer to perform query transformation to transform outer join to指示inner join.

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

Usage: ELIMINATE_OUTER_JOIN([<@Block>])

Description: Instructs the optimizer to perform query transformation to 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

Usage: NO_ELIMINATE_OUTER_JOIN     

Description: Prevents the optimizer to perform query transformation to 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

Usage: PLACE_DISTINCT              

Description: Instructs the optimizer to perform Distinct Placement query transformation.                 

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

Usage: NO_PLACE_DISTINCT           

Description: Prevents the optimizer to perform Distinct Placement query transformation.

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

Usage: PLACE_GROUP_BY([<@Block>] (<Table>) [<Temp View Number>])

Description: Instructs the optimizer to perform Group By Placement query transformation.

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