本文章為學習筆記，為了方便查閱，收錄於此
大部分為以前學習基於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

ORACLE 部分HINT

相關文章