ITPub博客

ORACLE 部分HINT

原创 Oracle 作者:gaopengtttt 时间:2016-11-16 20:56:46 0 删除 编辑
本文章为学习笔记,为了方便查阅,收录于此
大部分为以前学习基于CBO的ORACLE优化一书(崔华著),加上自己的实验
如果记录有误请指出
1、gather_plan_statistics HINT 用于记录SQL执行时的额外信息,如果实际执行次数,执行时间,物理逻辑读等。
    select /*+  gather_plan_statistics */ count(*) from pp; 
    后执行如下的任何一句可以查看相应的信息
    select * from table(dbms_xplan.display_cursor(null,null,'iostats last')); (I/O)
    SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'memstats last'));(PGA)
    SELECT * FROM table(dbms_xplan.display_cursor(NULL, NULL, 'runstats_last'));
    select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
    select * from table(dbms_xplan.display_cursor(null,null,'last'));(执行计划)
    select * from table(dbms_xplan.display_cursor(null,null,'iostats last +memstats last +COST +BYTES +PEEKED_BINDS +ALIAS'));
    本HINT可以使用alter session set statistics_level = all进行代替
2、driving_site HINT 用于在分布式查询中SQL在哪里执行可以再本地或者在远端,如下的语句执行计划是不同的
 select /*+ driving_site(a) */ * from dual@sil a,dual b
 --------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|      |     1 |     4 |     4   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN  |      |     1 |     4 |     4   (0)| 00:00:01 |
|   2 |   REMOTE               | DUAL |     1 |     2 |     2   (0)| 00:00:01 |
|   3 |   BUFFER SORT          |      |     1 |     2 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------


 select /*+ driving_site(b) */ * from dual@sil a,dual b
 
 --------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | In
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |     4 |     4   (0)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|      |     1 |     4 |     4   (0)| 00:00:01 |
|   2 |   REMOTE             | DUAL |     1 |     2 |     2   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |      |     1 |     2 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL | DUAL |     1 |     2 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
3、qb_name 为查询块自定义名字(QUERY BLOCK )




select /*+ qb_name(testour) */ * from test where name3 is null;
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| TEST |     1 |   106 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - TESTOUR / TEST@TESTOUR
   
4、ALL_ROWS FIRST_ROWS(n) 指定优化器评估是评估语句执行资源消耗最少还是根据最快返回N行记录的消耗来评估,
注意这种情况FIRST_ROWS(n) 的COST远小于ALL_ROWS,可能导致错误的执行计划。


select /*+ first_rows(1) */ *  from testscn;


--------------------------------------------------------------------------------
Plan hash value: 3875681502
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   105K|  3806K|     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| TESTSCN |   105K|  3806K|     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TESTSCN@SEL$1
   
select  /*+ all_rows */ *  from testscn;


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3875681502
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   105K|  3806K|   103   (1)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| TESTSCN |   105K|  3806K|   103   (1)| 00:00:02 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TESTSCN@SEL$1


5、full 针对单表进行全表扫描


select  /*+ full(testscn) */ *  from testscn;


--------------------------------------------------------------------------------
Plan hash value: 3875681502
-----------------------------------------------------------------------------
| Id  | Operation         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |         |   105K|  3806K|   103   (1)| 00:00:02 |
|   1 |  TABLE ACCESS FULL| TESTSCN |   105K|  3806K|   103   (1)| 00:00:02 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TESTSCN@SEL$1


6、INDEX 针对单表,考虑使用索引。方式如下四种:


 使用索引testscn的TESTSCN_INDEX 
  select  /*+ index(testscn TESTSCN_INDEX  ) */ *  from testscn where id is not null and name is not null; 
 考虑testscn上的所有索引,考虑COST最低的或者INDEX链接操作等
  select  /*+ index(testscn) */ *  from testscn where id is not null and name is not null;
 考虑testscn上的TESTSCN_INDEX 和TESTSCN_INDEX2 所以,考虑COST最低的或者INDEX链接操作等
  select  /*+ index(testscn  TESTSCN_INDEX  testscn_index2 ) */ *  from testscn where id is not null and name is not null;
 考虑testscn上id和name列的索引,考虑COST最低的或者INDEX链接操作等
  select  /*+ index(testscn (id) (name)  ) */ *  from testscn where id is not null and name is not null;
  
  
--------------------------------------------------------------------------------
Plan hash value: 3572127329
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |   105K|  3806K|   583   (1
|*  1 |  TABLE ACCESS BY INDEX ROWID| TESTSCN       |   105K|  3806K|   583   (1
|*  2 |   INDEX FULL SCAN           | TESTSCN_INDEX |   105K|       |   224   (1
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TESTSCN@SEL$1
   2 - SEL$1 / TESTSCN@SEL$1
7、NO_INDEX针对单表,考虑不会用指定索引


  不使用索引TESTSCN_INDEX,但是使用testscn_index2索引
  select /*+ no_index(testscn TESTSCN_INDEX) index(testscn testscn_index2 ) */  *  from testscn where id=2 and name is not null;
  不使用索引TESTSCN_INDEX,TESTSCN_INDEX2索引
  select /*+ no_index(testscn TESTSCN_INDEX TESTSCN_INDEX2) */ *  from testscn where id=2 and name is not null;
  不使用testscn表上所有索引
  select /*+ no_index(testscn) */ *  from testscn where id=2 and name is not null;
  
  
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1122084783
--------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     4 |   148 |   627   (
|*  1 |  TABLE ACCESS BY INDEX ROWID| TESTSCN        |     4 |   148 |   627   (
|*  2 |   INDEX FULL SCAN           | TESTSCN_INDEX2 |   105K|       |   268   (
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TESTSCN@SEL$1
   2 - SEL$1 / TESTSCN@SEL$1
8、INDEX_DESC针对单表,与索引相反的方式进行扫描(升序为降序,降序为升序)


  不使用索引TESTSCN_INDEX,但是使用testscn_index2索引,并且扫描testscn_index2为降序
   select /*+ no_index(testscn TESTSCN_INDEX) index_DESC(testscn testscn_index2 ) */  *  from testscn where id=2 and name is not null;
  考虑testscn上的所有索引,考虑COST最低的或者INDEX链接操作等
  select  /*+ index_DESC(testscn) */ *  from testscn where id is not null and name is not null;
  考虑testscn上的TESTSCN_INDEX 和TESTSCN_INDEX2 所以,考虑COST最低的或者INDEX链接操作等,并且扫描为降序
  select  /*+ index_DESC(testscn  TESTSCN_INDEX  testscn_index2 ) */ *  from testscn where id is not null and name is not null;
  PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3045085307
--------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     4 |   148 |   627   (
|*  1 |  TABLE ACCESS BY INDEX ROWID| TESTSCN        |     4 |   148 |   627   (
|*  2 |   INDEX FULL SCAN DESCENDING| TESTSCN_INDEX2 |   105K|       |   268   (
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TESTSCN@SEL$1
   2 - SEL$1 / TESTSCN@SEL$1
   
9、INDEX_COMBINE 针对单个目标,让优化器对多个索引进行位图布尔运算,然后进行转化为ROWID,一般这样的执行计划代价较大
可以考虑更改
_b_tree_bitmap_plans为FALSE 来禁用B-TREE索引进行COMBINE转换操作




指定表TESTSCN上的TESTSCN_INDEX,TESTSCN_INDEX2进行COMBINE操作
select /*+ index_combine(testscn TESTSCN_INDEX TESTSCN_INDEX2)  */ *  from testscn where  id=2 and name='gaopeng'
指定表TESTSCN上的所有索引考虑进行COMBINE操作
select /*+ index_combine(testscn )  */ *  from testscn where  id=2 and name='gaopeng'


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2845944253
--------------------------------------------------------------------------------
| Id  | Operation                        | Name           | Rows  | Bytes | Cost
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                |     1 |    21 |   26
|   1 |  TABLE ACCESS BY INDEX ROWID     | TESTSCN        |     1 |    21 |   26
|   2 |   BITMAP CONVERSION TO ROWIDS    |                |       |       |
|   3 |    BITMAP AND                    |                |       |       |
|   4 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |
|*  5 |      INDEX RANGE SCAN            | TESTSCN_INDEX  |       |       |
|   6 |     BITMAP CONVERSION FROM ROWIDS|                |       |       |
|*  7 |      INDEX RANGE SCAN            | TESTSCN_INDEX2 |       |       |   26
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TESTSCN@SEL$1


10、INDEX_JOIN 针对单表,当通过多个索引扫描可以得到所有的查询内容和WHERE谓词条件及不需要回表操作。


select /*+ index_join(testscn TESTSCN_INDEX TESTSCN_INDEX2)  */  id,name  from testscn where  id=2 and name='gaopeng';
select /*+ index_join(testscn)  */  id,name  from testscn where  id=2 and name='gaopeng';
具体解释和前面一致


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 782341378
--------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     1 |    13 |   269   (1)| 00:
|*  1 |  VIEW              | index$_join$_001 |     1 |    13 |   269   (1)| 00:
|*  2 |   HASH JOIN        |                  |       |       |            |
|*  3 |    INDEX RANGE SCAN| TESTSCN_INDEX    |     1 |    13 |     1   (0)| 00:
|*  4 |    INDEX RANGE SCAN| TESTSCN_INDEX2   |     1 |    13 |   268   (1)| 00:
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2AEE34FF / TESTSCN@SEL$1
   2 - SEL$2AEE34FF
   3 - SEL$2AEE34FF / indexjoin$_alias$_001@SEL$2AEE34FF
   4 - SEL$2AEE34FF / indexjoin$_alias$_002@SEL$2AEE34FF
   
11、AND_EQUAL 针对单表,其成立条件为WHERE条件中有针对不同列的单值条件,并且这些列上都有单值索引,其最大个数为5
select /*+ AND_EQUAL(testscn TESTSCN_INDEX TESTSCN_INDEX2)  */  *  from testscn where  id=2 and name='gaopeng'
具体解释和前面一致
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1514933407
--------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CP
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    21 |   269   (
|*  1 |  TABLE ACCESS BY INDEX ROWID| TESTSCN        |     1 |    21 |   269   (
|   2 |   AND-EQUAL                 |                |       |       |
|*  3 |    INDEX RANGE SCAN         | TESTSCN_INDEX  |     1 |       |     1   (
|*  4 |    INDEX RANGE SCAN         | TESTSCN_INDEX2 | 99991 |       |   267   (
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TESTSCN@SEL$1
   3 - SEL$1 / TESTSCN@SEL$1


如果我们使用ID列上的联合索引:
CREATE INDEX TESTSCN_INDEX3 ON TESTSCN(ID,DEPT);
然后
select /*+ AND_EQUAL(testscn TESTSCN_INDEX3 TESTSCN_INDEX2)  */  *  from testscn where  id=2 and name='gaopeng';
具体解释和前面一致
其执行计划为:
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 9349066
--------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    21 |     2   (0
|*  1 |  TABLE ACCESS BY INDEX ROWID| TESTSCN       |     1 |    21 |     2   (0
|*  2 |   INDEX RANGE SCAN          | TESTSCN_INDEX |     1 |       |     1   (0
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TESTSCN@SEL$1
   2 - SEL$1 / TESTSCN@SEL$1
可以看到AND_EQUAL不能生效。因为(并且这些列上都有单值索引) 条件不符合。


12、INDEX_FFS 针对单表,当索引中包含了所有查询内容和WHERE谓词条件,不需要回表可以使用。
select /*+ INDEX_FFS(testscn TESTSCN_INDEX3 TESTSCN_INDEX2)  */  id,dept  from testscn where  id=2 and dept='gaopeng';
select /*+ INDEX_FFS(testscn TESTSCN_INDEX3)  */  id,dept  from testscn where  id=2 and dept='gaopeng';
select /*+ INDEX_FFS(testscn)  */  id,dept  from testscn where  id=2 and dept='gaopeng';
具体解释和前面一致




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 72126314
--------------------------------------------------------------------------------
| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                |     1 |    13 |    93   (2)| 00:
|*  1 |  INDEX FAST FULL SCAN| TESTSCN_INDEX3 |     1 |    13 |    93   (2)| 00:
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / TESTSCN@SEL$1
   
13、ordered 针对多个表进行连接的HINT,他会按照FROM后的顺序,第一表作为驱动结果集。


select /*+ ordered */ *  from  dept1 d,dept2 b,emp1 e  where b.deptno=e.deptno and  e.deptno=d.deptno ;


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3516109060
-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |    14 |  2058 |    13   (8)| 00:00:01 |
|*  1 |  HASH JOIN            |       |    14 |  2058 |    13   (8)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|       |    16 |   960 |     9   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPT1 |     4 |   120 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |       |     4 |   120 |     6   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | DEPT2 |     4 |   120 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | EMP1  |    14 |  1218 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / D@SEL$1
   5 - SEL$1 / B@SEL$1
   
14、leading 针对多个表进行连接的HINT,强制LEADING中的表至左向右,第一个为驱动表,如果未在LEADING中出现由ORACLE自动判断。
select /*+ leading (b d )*/ *  from  dept1 d,dept2 b,emp1 e  where b.deptno=e.deptno and  e.deptno=d.deptno ;


-------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |    14 |  2058 |    13   (8)| 00:00:01 |
|*  1 |  HASH JOIN            |       |    14 |  2058 |    13   (8)| 00:00:01 |
|   2 |   MERGE JOIN CARTESIAN|       |    16 |   960 |     9   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | DEPT2 |     4 |   120 |     3   (0)| 00:00:01 |
|   4 |    BUFFER SORT        |       |     4 |   120 |     6   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL | DEPT1 |     4 |   120 |     2   (0)| 00:00:01 |
|   6 |   TABLE ACCESS FULL   | EMP1  |    14 |  1218 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   3 - SEL$1 / B@SEL$1
   5 - SEL$1 / D@SEL$1
15、use_merge针对多个目标表,指定一个或者多个表为被驱动表


select /*+ leading (b) use_merge(d e)*/ *  from  dept1 d,dept2 b,emp1 e  where b.deptno=e.deptno and  e.deptno=d.deptno ;


Plan hash value: 2466184505
------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |    14 |  2058 |    12  (25)| 00:00:01 |
|   1 |  MERGE JOIN          |       |    14 |  2058 |    12  (25)| 00:00:01 |
|   2 |   MERGE JOIN         |       |    14 |  1638 |     8  (25)| 00:00:01 |
|   3 |    SORT JOIN         |       |     4 |   120 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| DEPT2 |     4 |   120 |     3   (0)| 00:00:01 |
|*  5 |    SORT JOIN         |       |    14 |  1218 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| EMP1  |    14 |  1218 |     3   (0)| 00:00:01 |
|*  7 |   SORT JOIN          |       |     4 |   120 |     4  (25)| 00:00:01 |
|   8 |    TABLE ACCESS FULL | DEPT1 |     4 |   120 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   4 - SEL$1 / B@SEL$1
   6 - SEL$1 / E@SEL$1
   8 - SEL$1 / D@SEL$1
   
16、no_use_merge:针对多个目标表,指定多个被驱动表不能使用MERGE JION


select /*+ leading(dept) no_use_merge(emp) */  * from dept,emp where emp.deptno=dept.deptno;




17、USE_NL:针对多个目标表,指定一个或者多个表为被驱动表,当USE_NL有多个表的时候ORACLE自动选择连接顺序


select /*+ leading (e b) use_nl(d)*/ *  from  dept1 d,dept2 b,emp1 e  where b.deptno=e.deptno and  e.deptno=d.deptno 
select /*+ leading (e ) use_nl(b d)*/ *  from  dept1 d,dept2 b,emp1 e  where b.deptno=e.deptno and  e.deptno=d.deptno 


SQL> select * from table(dbms_xplan.display(null,null,' advanced'));


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 111324804


-----------------------------------------------------------------------------
| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |    14 |  1092 |    24   (5)| 00:00:01 |
|   1 |  NESTED LOOPS       |       |    14 |  1092 |    24   (5)| 00:00:01 |
|*  2 |   HASH JOIN         |       |    14 |   812 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMP1  |    14 |   532 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPT2 |     4 |    80 |     3   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL | DEPT1 |     1 |    20 |     1   (0)| 00:00:01 |


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------


Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1
   3 - SEL$1 / E@SEL$1
   4 - SEL$1 / B@SEL$1
   5 - SEL$1 / D@SEL$1
   
18、NO_USE_NL:针对多个目标表,指定多个被驱动表不能使用NEST LOOP 连接方式


select /*+ leading(emp)  no_use_nl(dept) */  * from emp,dept where  emp.deptno=dept.deptno and empno=7369;


19、use_hash:针对多个目标表,指定一个或者多个表为被驱动表,当USE_HASH有多个表的时候ORACLE自动选择连接顺序


 select /*+leading(emp) use_hash(dept) */ * from emp,dept where emp.deptno=dept.deptno;
 select /*+leading(emp) use_hash(dept1 dept) */ * from emp,dept,DEPT1 where emp.deptno=dept.deptno and emp.deptno=dept1.deptno;


PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1123238657


---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    14 |   812 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    14 |   812 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------




PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------


   1 - SEL$1
   2 - SEL$1 / EMP@SEL$1
   3 - SEL$1 / DEPT@SEL$1


20、NO_USE_HASH:针对多个目标表,指定多个被驱动表不能使用hash join 连接方式


select /*+ no_use_hash(emp) */ *  from emp,dept where  emp.deptno=dept.deptno ;


21、use_conact:针对目标SQL的HINT,让优化器对目标SQL使用IN-LIST扩展或者OR扩展
alter session set events '10142 trace name context forever';
alter session set events '10157 trace name context forever';


select /*+ use_concat */ * from emp where empno in (7654,7698);


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2259546459
--------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Tim
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     2 |    76 |     2   (0)| 00:
|   1 |  CONCATENATION               |        |       |       |            |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:
|*  3 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:
|   4 |   TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    38 |     1   (0)| 00:
|*  5 |    INDEX UNIQUE SCAN         | PK_EMP |     1 |       |     0   (0)| 00:
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1_1 / EMP@SEL$1
   3 - SEL$1_1 / EMP@SEL$1
   4 - SEL$1_2 / EMP@SEL$1_2
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   5 - SEL$1_2 / EMP@SEL$1_2
   
22、
no_expand:针对目标SQL的HINT,让优化器对目标SQL不使用IN-LIST扩展或者OR扩展,是USE_CONCAT的反义


SQL> explain plan for select /*+ no_expand */ * from emp where empno in (7654,7698);
Explained


SQL> select * from table(dbms_xplan.display(null,null,' advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3202029772
--------------------------------------------------------------------------------
| Id  | Operation                        | Name   | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |        |     2 |    76 |     2   (0)|
|   1 |  TABLE ACCESS BY INDEX ROWID     | EMP    |     2 |    76 |     2   (0)|
|   2 |   BITMAP CONVERSION TO ROWIDS    |        |       |       |            |
|   3 |    BITMAP OR                     |        |       |       |            |
|   4 |     BITMAP CONVERSION FROM ROWIDS|        |       |       |            |
|*  5 |      INDEX RANGE SCAN            | PK_EMP |       |       |     0   (0)|
|   6 |     BITMAP CONVERSION FROM ROWIDS|        |       |       |            |
|*  7 |      INDEX RANGE SCAN            | PK_EMP |       |       |     0   (0)|
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1 / EMP@SEL$1


23、
no_merge:针对单个目标视图的HINT,让优化器不使用视图合并


select *
  from emp,
       (select /*+  no_merge */
         *
          from dept
         where loc = 'CHICAGO') dept_view_inline
 where emp.deptno = dept_view_inline.deptno;
 


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2910064727
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |     5 |   340 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN          |      |     5 |   340 |     7  (15)| 00:00:01 |
|   2 |   VIEW              |      |     1 |    30 |     3   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| DEPT |     1 |    20 |     3   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL | EMP  |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$2 / DEPT_VIEW_INLINE@SEL$1
   3 - SEL$2 / DEPT@SEL$2
   4 - SEL$1 / EMP@SEL$1
   
24、




no_merge:针对单个目标视图的HINT,让优化器使用视图合并


select *
  from emp,
       (select /*+  merge */
         *
          from dept
         where loc = 'CHICAGO') dept_view_inline
 where emp.deptno = dept_view_inline.deptno;




PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 844388907
--------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |   290 |     6  (17)| 00
|   1 |  MERGE JOIN                  |         |     5 |   290 |     6  (17)| 00
|*  2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     2   (0)| 00
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / DEPT@SEL$2
   3 - SEL$F5BB74E1 / DEPT@SEL$2
   5 - SEL$F5BB74E1 / EMP@SEL$1
   
25、no_unnest 针对子查询的HINT,不让优化器使用子查询展开操作
select *
  from emp
 where deptno not in (select /*+ no_unnest */
                       deptno
                        from dept
                       where loc = 'CHICAGO');


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1499841400
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     9 |   342 |    12   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / EMP@SEL$1
   3 - SEL$2 / DEPT@SEL$2
   
 26、unnest 针对子查询的HINT,让优化器使用子查询展开操作
 
 select *
  from emp
 where deptno not in (select /*+ unnest */
                       deptno
                        from dept
                       where loc = 'CHICAGO');
 
 PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3248063469
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     9 |   441 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI SNA|      |     9 |   441 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     1 |    11 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$5DA710D3
   2 - SEL$5DA710D3 / EMP@SEL$1
   3 - SEL$5DA710D3 / DEPT@SEL$2

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7728585/viewspace-2128587/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
wxh gp_22389860

注册时间:2008-10-13

  • 博文量
    633
  • 访问量
    2778774