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