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               |     &