1. 首页
  2. IT资讯

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

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

主题测试文章,只做测试使用。发布者:深沉的少年,转转请注明出处:http://www.cxybcw.com/183428.html

联系我们

13687733322

在线咨询:点击这里给我发消息

邮件:1877088071@qq.com

工作时间:周一至周五,9:30-18:30,节假日休息

QR code