Oracle SQL Hints --- Quert Transformation Hints

[中文]

Author:  fuyuncat

Source:  www.HelloDBA.com

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

Share to 

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.