ORACLE 部分HINT

本文章为学习笔记，为了方便查阅，收录于此
大部分为以前学习基于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