Oracle SQL Hints --- Quert Transformation Hints

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 |