Oracle中常見的Hint(一)

風靈使發表於2018-11-23

Oracle中的Hint可以用來調整SQL的執行計劃，提高SQL執行效率。下面分類介紹Oracle資料庫中常見的Hint。這裡描述的是Oracle11g R2中的常見Hint，Oracle資料庫中各個版本中的Hint都不盡相同，所以這裡講述的的Hint可能並不適用於Oracle早期的版本。

一、與優化器模式相關的Hint

1、`ALL_ROWS`

ALL_ROWS是針對整個目標SQL的Hint，它的含義是讓優化器啟用CBO，而且在得到目標SQL的執行計劃時會選擇那些吞吐量最佳的執行路徑。這裡的“吞吐量最佳”是指資源消耗量(即對I/O、CPU等硬體資源的消耗量)最小，也就是說在ALL_ROWS Hint生效的情況下，優化器會啟用CBO而且會依據各個執行路徑的資源消耗量來計算它們各自的成本。

ALL_ROWS Hint的格式如下：

/*+ ALL_ROWS */

使用範例：

select /*+ all_rows */ empno,ename,sal,job
from emp where empno=7396;

從Oracle10g開始，ALL_ROWS就是預設的優化器模式，啟用的就是CBO。

scott@TEST>show parameter optimizer_mode
 
NAME                     TYPE                 VALUE
------------------------------------ --------------------------------- ------------------------------
optimizer_mode               string                   ALL_ROWS

如果目標SQL中除了ALL_ROWS之外還使用了其他與執行路徑、表連線相關的Hint，優化器會優先考慮ALL_ROWS。

2、`FIRST_ROWS(n)`

FIRST_ROWS(n)是針對整個目標SQL的Hint，它的含義是讓優化器啟用CBO模式，而且在得到目標SQL的執行計劃時會選擇那些能以最快的響應時間返回頭n條記錄的執行路徑，也就是說在FIRST_ROWS(n) Hint生效的情況下，優化器會啟用CBO，而且會依據返回頭n條記錄的響應時間來決定目標SQL的執行計劃。

FIRST_ROWS(n)格式如下：

/*+ FIRST_ROWS(n) */

使用範例

select /*+ first_rows(10) */ empno,ename,sal,job
from emp where deptno=30;

上述SQL中使用了/*+ first_rows(10) */，其含義是告訴優化器我們想以最短的響應時間返回滿足條件"deptno=30"的前10條記錄。

注意，FIRST_ROWS(n) Hint和優化器模式FIRST_ROWS_n不是一一對應的。優化器模式FIRST_ROWS_n中的n只能是1、10、100、1000。但FIRST_ROWS(n) Hint中的n還可以是其他值。

scott@TEST>alter session set optimizer_mode=first_rows_9;
ERROR:
ORA-00096: invalid value FIRST_ROWS_9 for parameter optimizer_mode, must be from among first_rows_1000, first_rows_100, first_rows_10, first_rows_1, first_rows, all_rows, choose,rule
 
scott@TEST>set autotrace traceonly
scott@TEST>select /*+ first_rows(9) */ empno from emp;
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 179099197
 
---------------------------------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |     |   9 |    36 |   1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |  9 |    36 |   1   (0)| 00:00:01 |
---------------------------------------------------------------------------

如果在UPDATE、DELETE或者含如下內容的查詢語句中使用了FIRST_ROWS(n) Hint，則該Hint會被忽略：

集合運算(如UNION,INTERSACT,MINUS,UNION ALL等)
GROUP BY
FOR UPDATE
聚合函式(比如SUM等)
DISTINCT
ORDER BY(對應的排序列上沒有索引)

這裡優化器會忽略FIRST_ROWS(n) Hint是因為對於上述型別的SQL而言，Oracle必須訪問所有的行記錄後才能返回滿足條件的頭n行記錄，即在上述情況下，使用該Hint是沒有意義的。

3、`RULE`

RULE是針對整個目標SQL的Hint，它表示對目標SQL啟用RBO。

格式如下：

/*+ RULE */

使用範例：

select /*+ rule */ empno,ename,sal,job
from emp where deptno=30;

RULE不能與除DRIVING_SITE以外的Hint聯用，當RULE與除DRIVING_SITE以外的Hint聯用時，其他Hint可能會失效；當RULE與DRIVING_SITE聯用時，它自身可能會失效，所以RULE Hint最好是單獨使用。

一般情況下，並不推薦使用RULE Hint。一來是因為Oracle早就不支援RBO了，二來啟用RBO後優化器在執行目標SQL時可選擇的執行路徑將大大減少，很多執行路徑RBO根本就不支援(比如雜湊連線)，就也就意味著啟用RBO後目標SQL跑出正確執行計劃的概率將大大降低。

因為很多執行路徑RBO根本就不支援，所以即使在目標SQL中使用了RULE Hint，如果出現瞭如下這些情況(包括但不限於)，RULE Hint依然會被Oracle忽略。

目標SQL除RULE之外還聯合使用了其他Hint(如DRIVING_SITE)。
目標SQL使用了並行執行
目標SQL所涉及的物件有IOT
目標SQL所涉及的物件有分割槽表
…

二、與表訪問相關的Hint

1、`FULL`

FULL是針對單個目標表的Hint，它的含義是讓優化器對目標表執行全表掃描。

格式如下：

/*+ FULL(目標表) */

使用範例：

select /*+ full(emp) */ empno,ename,sal,job
from emp where deptno=30;

上述SQL中Hint的含義是讓優化器對目標表EMP執行全表掃描操作，而不考慮走表EMP上的任何索引(即使列EMPNO上有主鍵索引)。

2、`ROIWD`

ROIWD是針對單個目標表的Hint，它的含義是讓優化器對目標表執行RWOID掃描。只有目標SQL中使用了含ROWID的where條件時ROWID Hint才有意義。

格式如下：

/*+ ROWID(目標表) */

使用範例：

select /*+ rowid(emp) */ empno,ename,sal,job
from emp where rowid='AAAR3xAAEAAAACXAAA';

Oracle 11g R2中即使使用了ROWID Hint，Oracle還是會將讀到的塊快取在Buffer Cache中。

三、與索引訪問相關的Hint

1、`INDEX`

INDEX是針對單個目標表的Hint，它的含義是讓優化器對目標表的的目標索引執行索引掃描操作。

INDEX Hint中的目標索引幾乎可以是Oracle資料庫中所有型別的索引(包括B樹索引、點陣圖索引、函式索引等)。

INDEX Hint的模式有四種：

格式1 /*+ INDEX(目標表目標索引) */
格式2 /*+ INDEX(目標表目標索引1 目標索引2 …… 目標索引n) */
格式3 /*+ INDEX(目標表 (目標索引1的索引列名) (目標索引2的索引列名) …… (目標索引n的索引列名)) */
格式4 /*+ INDEX(目標表) */

格式1表示僅指定了目標表上的一個目標索引，此時優化器只會考慮對這個目標索引執行索引掃描操作，而不會去考慮全表掃描或者對該目標表上的其他索引執行索引掃描操作。

格式2表示指定了目標表上的n個目標索引，此時優化器只會考慮對這n個目標索引執行索引掃描操作，而不會去考慮全表掃描或者對該目標表上的其他索引執行索引掃描操作。注意，優化器在考慮這n個目標索引時，可能是分別計算出單獨掃描各個目標索引的成本後，再選擇其中成本值最低的索引；也可能是先分別掃描目標索引中的兩個或多個索引，然後再對掃描結果執行合併操作。當然，後面這種可能性的前提條件是優化器計算出來這樣做的成本值是最低的。

格式三也是表是指定了目標表上的n個目標索引，只不過此時是用指定目標索引的索引列名來代替對應的目標索引名。如果目標索引是複合索引，則在用於指定該索引列名的括號內也可以指定該目標索引的多個索引列，各個索引列之間用空格分隔就可以了。

格式的表示指定了目標表上所有已存在的索引，此時優化器只會考慮對該目標表上所有已存在的索引執行索引掃描操作，而不會去考慮全表掃描操作。注意，這裡優化器在考慮該目標表上所有已存在的索引時，可能是分別計算出單獨掃描這些索引的成本後再選擇其中成本值最低的索引；也可能是先分別掃描這些索引中的兩個或多個索引，然後再對掃描結果執行合併操作。當然，後面這種可能性的前提條件是優化器計算出來這樣做的成本值是最低的。

使用範例：

select /*+ index(emp pk_emp) */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;
  
select /*+ index(emp pk_emp idx_emp_mgr idx_emp_dept) */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;
  
select /*+ index(emp (empno) (mgr) (deptno)) */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;
  
select /*+ index */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;

2、`NO_INDEX`

NO_INDEX是針對單個目標表的Hint，它是INDEX的反義Hint，其含義是讓優化器不對目標表上的目標索引執行掃描操作。

INDEX Hint中的目標索引也幾乎可以是Oracle資料庫中所有型別的索引(包括B樹索引、點陣圖索引、函式索引等)。

格式有如下三種：

格式1 /*+ NO_INDEX(目標表目標索引) */
格式2 /*+ NO_INDEX(目標表目標索引1 目標索引2 …… 目標索引n) */
格式3 /*+ NO_INDEX(目標表) */

格式1表示僅指定了目標表上的一個目標索引，此時優化器只是不會考慮對這個目標索引執行索引掃描操作，但還是會考慮全表掃描或者對該目標表上的其他索引執行索引掃描操作。

格式2表示指定了目標表上的n個目標索引，此時優化器只是不會考慮對這n個目標索引執行索引掃描操作，但還是會考慮全表掃描或者對該目標表上的其他索引執行索引掃描操作。

格式3表示指定了目標表上的所有已存在的索引，即此時優化器不會考慮對該目標表上所有已存在的索引執行索引掃描操作，這相當於對目標表指定了全表掃描。

使用範例：

select /*+ no_index(emp pk_emp) */ empno,ename,sal,job
  from emp
where empno=7369 and mgr=7902 and deptno=20;
  
select /*+ no_index(emp pk_emp idx_emp_mgr idx_emp_dept) */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;
  
select /*+ no_index */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;

3、`INDEX_DESC`

INDEX_DESC是針對單個目標表的Hint，它的含義是讓優化器對目標表上的目標索引執行索引降序掃描操作。如果目標索引是升序的，則INDEX_DESC Hint會使Oracle以降序的方式掃描該索引；如果目標索引是降序的，則INDEX_DESC Hint會使Oracle以升序的方式掃描該索引。

格式有三種：

格式1 /*+ INDEX_DESC(目標表目標索引) */
格式2 /*+ INDEX_DESC(目標表目標索引1 目標索引2 …… 目標索引n) */
格式3 /*+ INDEX_DESC(目標表) */

上述3種格式的含義和INDEX中對應格式的含義相同。

使用範例：

select /*+ index_desc(emp pk_emp) */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;
  
select /*+ index_desc(emp pk_emp idx_emp_mgr idx_emp_dept) */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;
  
select /*+ index_desc */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;

例項：

scott@TEST>select /*+ index_desc(emp,pk_emp) */ empno from emp;
 
     EMPNO
----------
      7934
      7902
      7900
      7876
      7844
      7839
      7788
      7782
      7698
      7654
      7566
      7521
      7499
      7369
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1838043032
 
-------------------------------------------------------------------------------------
| Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |     |    10 |  40 |   1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN DESCENDING| PK_EMP |     10 |  40 |   1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------
......

4、`INDEX_COMBINE`

INDEX_COMBINE是針對單個目標表的Hint，它的含義是讓優化器對目標表上的多個目標索引執行點陣圖布林運算。Oracle資料庫裡有一個對映函式(Mapping Function)，它可以例項B*Tree索引中的ROWID和對應點陣圖索引中的點陣圖之間的互相轉換，所以INDEX_COMBINE Hint並不侷限於點陣圖索引，它的作用物件也可以是B*Tree索引。

格式有如下兩種

格式1 /*+ INDEX_COMBINE(目標表目標索引1 目標索引2 …… 目標索引n) */
格式2 /*+ INDEX_COMBINE(目標表) */

格式1表示指定了目標表上的n個目標索引，此時優化器會考慮對這n個目標索引中的兩個或多個執行點陣圖布林運算。

格式2表示指定了目標表上所有已存在的索引，此時優化器會考慮對該表上已存在的所有索引中的兩個或多個執行點陣圖布林運算。

使用範例：

select /*+ index_combine(emp pk_emp idx_emp_mgr) */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 ;
  
select /*+ index_combine(emp pk_emp idx_emp_mgr idx_emp_deptno) */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;
  
select /*+ index_combine(emp) */ empno,ename,sal,job
  from emp
 where empno=7369 and mgr=7902 and deptno=20;

下面看一個例項，在表EMP上建立兩個索引


scott@TEST>create index idx_emp_mgr on emp(mgr);
 
Index created.
 
scott@TEST>create index idx_emp_dept on emp(deptno);
 
Index created.
 
scott@TEST>select /*+ index_combine(emp pk_emp idx_emp_mgr idx_emp_deptno) */ empno,ename,sal,job
  2   from emp
  3   where empno=7369 and mgr=7902 and deptno=20;
 
     EMPNO ENAME               SAL JOB
---------- ------------------------------ ---------- ---------------------------
      7369 SMITH              800 CLERK
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1816402415
 
-------------------------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows | Bytes | Cost (%CPU)| Time    |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |     |     1 |    29 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID   | EMP        |     1 |    29 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS  |     |   |   |        |     |
|   3 |    BITMAP AND           |     |   |   |        |     |
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |   |   |        |     |
|*  5 |      INDEX RANGE SCAN         | PK_EMP |   |   |     0   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|      |   |   |        |     |
|*  7 |      INDEX RANGE SCAN         | IDX_EMP_MGR    |   |   |     1   (0)| 00:00:01 |
|   8 |     BITMAP CONVERSION FROM ROWIDS|      |   |   |        |     |
|*  9 |      INDEX RANGE SCAN         | IDX_EMP_DEPT |    |   |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
......

從上面的執行計劃中可以看到關鍵字“BITMAP CONVERSION FROM ROWIDS”、“BITMAP AND”和“BITMAP CONVERSION TO ROWIDS”，這說明Oracle先分別對上述三個單鍵值的B*Tree索引IDX_EMP_MGR、IDX_EMP_DEPT和PK_EMP用對映函式將其中的ROWID轉換成了點陣圖，然後對轉換後的點陣圖執行了BITMAP AND(點陣圖按位與)布林運算，最後將布林運算的結果再次用對映函式轉換成了ROWID並回表得到最終的執行結果。能走出這樣的執行計劃顯然是因為INDEX_COMBINE Hint生效了。

用對映函式將ROWID轉換成了點陣圖，然後再執行布林運算，最後將布林運算的結果再次用對映函式轉換成了ROWID並回表得到最終的執行結果，這個過程在實際生產環境中的執行效率可能是有問題的，可以使用隱含引數_B_TREE_BITMAP_PLANS禁掉該過程中的ROWID到點陣圖的轉換：

alter session set “_b_tree_bitmap_plans”=false;

scott@TEST>alter session set "_b_tree_bitmap_plans"=false;
 
Session altered.
 
scott@TEST>select /*+ index_combine(emp pk_emp idx_emp_mgr idx_emp_deptno) */ empno,ename,sal,job
  2    from emp
  3   where empno=7369 and mgr=7902 and deptno=20;
 
     EMPNO ENAME               SAL JOB
---------- ------------------------------ ---------- ---------------------------
      7369 SMITH              800 CLERK
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2949544139
 
--------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |        |    1 |    29 |      1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |       1 |    29 |      1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN     | PK_EMP |       1 |       |    0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
......

從上面的執行計劃中可以看出沒有出現BITMAP相關的關鍵字，即INDEX_COMBINE Hint被Oracle忽略了。

5、`INDEX_FFS`

INDEX_FFS是針對單個目標表的Hint，它的含義是讓優化器對目標表上的目標索引執行索引快速全掃描操作。注意，索引快速全掃描能成立的前提條件是SELECT語句中所有的查詢列都存在於目標索引中，即通過掃描目標索引就可以得到所有的查詢列而不用回表。

格式有如下三種：

格式1 /*+ INDEX_FFS(目標表目標索引) */
格式2 /*+ INDEX_FFS(目標表目標索引1 目標索引2 …… 目標索引n) */
格式3 /*+ INDEX_FFS(目標表) */

上述3種格式的含義和INDEX中對應格式的含義相同。

使用範例：

select /*+ index_ffs(emp pk_emp) */ empno
  from emp;
 
select /*+ index_ffs(emp idx_emp_1 idx_emp_2) */ empno
  from emp
 where mgr=7902 and deptno=20;
--create index idx_emp_1 on emp(mgr,deptno,1);
--create index idx_emp_2 on emp(mgr,deptno,2);
 
select /*+ index_ffs(emp) */ empno
  from emp;

看下面的例項：

scott@TEST>select empno from emp;
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 179099197
 
---------------------------------------------------------------------------
| Id  | Operation    | Name   | Rows  | Bytes | Cost (%CPU)| Time    |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT |     |    10 |    40 | 1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | PK_EMP |    10 |    40 |    1   (0)| 00:00:01 |
---------------------------------------------------------------------------
......
scott@TEST>select /*+ index_ffs(emp) */empno from emp;
 
14 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 366039554
 
-------------------------------------------------------------------------------
| Id  | Operation        | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |    10 |    40 |     2  (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| PK_EMP |    10 |    40 |     2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
......

6、`INDEX_JOIN`

INDEX_JOIN是針對單個目標表的Hint，它的含義是讓優化器對目標表上的多個目標索引執行INDEX JOIN操作。INDEX JOIN能成立的前提條件是SELECT語句中所有的查詢列都存在於目標表上的多個目標索引中，即通過掃描這些索引就可以得到所有的查詢列而不用回表。

格式如下：

格式1 /*+ INDEX_JOIN(目標表目標索引1 目標索引2 …… 目標索引n) */
格式2 /*+ INDEX_JOIN */

上述兩種格式的含義與INDEX_COMBINE Hint中對應格式的含義相同。

使用範例：

select /*+ index_join(emp pk_emp idx_emp_mgr) */ empno,mgr
  from emp
 where empno>7369 and mgr<7902;
  
select /*+ index_join(emp) */ empno,mgr
  from emp
 where empno>7369 and mgr<7902;

來看下面的例項：

	
scott@TEST>select empno,mgr
  2    from emp
  3   where empno>7369 and mgr<7902;
 
12 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2059184959
 
-------------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |    10 |    80 | 2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP   |    10 |    80 | 2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | IDX_EMP_MGR |    11 |    |   1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
......
scott@TEST>select /*+ index_join(emp) */ empno,mgr
  2    from emp
  3   where empno>7369 and mgr<7902;
 
12 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3030719951
 
---------------------------------------------------------------------------------------
| Id  | Operation      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    10 |    80 |     3  (34)| 00:00:01 |
|*  1 |  VIEW         | index$_join$_001 |    10 |    80 |     3  (34)| 00:00:01 |
|*  2 |   HASH JOIN     |           |       |       |       |       |
|*  3 |    INDEX RANGE SCAN| IDX_EMP_MGR      |    10 |    80 |     2  (50)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| PK_EMP         |    10 |    80 |     2  (50)| 00:00:01 |
---------------------------------------------------------------------------------------
......

7、`AND_EQUAL`

AND_EQUAL是針對單個目標表的Hint，它的含義是讓優化器對目標表上的多個目標索引執行INDEX MERGE操作。INDEX MERGE能成立的前提條件是目標SQL的where條件裡出現了多個針對不同單列的等值條件，並且這些列上都有單鍵值的索引。另外，在Oracle資料庫裡，能夠做INDEX MERGE的索引數量的最大值是5。

格式如下：

/*+ AND_EQUAL(目標表 目標索引1 目標索引2 …… 目標索引n)*/

使用範例：

select /*+ and_equal(emp idx_emp_mgr idx_emp_dept) */ empno,mgr
  from emp
 where deptno=20 and mgr=7902;

看下面的例項：

	
scott@TEST>select empno,mgr
  2    from emp
  3   where deptno=20 and mgr=7902;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2059184959
 
-------------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Rows  | Bytes | Cost (%CPU)| Time   |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |   1 |    11 |   2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP   |   1 |    11 |   2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN      | IDX_EMP_MGR |   2 |      |   1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
......
scott@TEST>select /*+ and_equal(emp idx_emp_mgr idx_emp_dept) */ empno,mgr
  2    from emp
  3   where deptno=20 and mgr=7902;
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3295440569
 
--------------------------------------------------------------------------------------------
| Id  | Operation           | Name    | Rows  | Bytes | Cost (%CPU)| Time      |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |  1 |  11 |    3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP    |  1 |  11 |    3   (0)| 00:00:01 |
|   2 |   AND-EQUAL          |          |    |    |     |      |
|*  3 |    INDEX RANGE SCAN        | IDX_EMP_MGR  |  2 |     |  1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN        | IDX_EMP_DEPT |   5 |     |  1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
......

四、與表連線順序相關的Hint

1、`ORDERED`

ORDERED是針對多個目標表的Hint，它的含義是讓優化器對多個目標表執行表連線操作時，執照它們在目標SQL的where條件中出現的順序從左到右依次進行連線。

格式如下：

/*+ ORDERED */

使用範例：

	
select /*+ ordered */ e.ename,j.job,e.sal,d.deptno
  from emp e,jobs j,dept d 
 where e.empno=j.empno
   and e.deptno=d.deptno
   and d.loc='CHICAGO'
 order by e.ename;

例項：

scott@TEST>select e.ename,j.job,e.sal,d.deptno
  2    from emp e,jobs j,dept d 
  3   where e.empno=j.empno
  4     and e.deptno=d.deptno
  5     and d.loc='CHICAGO'
  6   order by e.ename;
 
6 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 4113290228
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     5 |   235 |     9  (23)| 00:00:01 |
|   1 |  SORT ORDER BY                 |              |     5 |   235 |     9  (23)| 00:00:01 |
|*  2 |   HASH JOIN                    |              |     5 |   235 |     8  (13)| 00:00:01 |
|   3 |    NESTED LOOPS                |              |       |       |            |          |
|   4 |     NESTED LOOPS               |              |     5 |   140 |     4   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL         | DEPT         |     1 |    11 |     3   (0)| 00:00:01 |
|*  6 |      INDEX RANGE SCAN          | IDX_EMP_DEPT |     5 |       |     0   (0)| 00:00:01 |
|   7 |     TABLE ACCESS BY INDEX ROWID| EMP          |     5 |    85 |     1   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL           | JOBS         |    14 |   266 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
......
scott@TEST>select /*+ ordered */ e.ename,j.job,e.sal,d.deptno
  2    from emp e,jobs j,dept d 
  3   where e.empno=j.empno
  4     and e.deptno=d.deptno
  5     and d.loc='CHICAGO'
  6   order by e.ename;
 
6 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3031293267
 
-----------------------------------------------------------------------------------------
| Id  | Operation                      | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |        |     5 |   235 |    11  (28)| 00:00:01 |
|   1 |  SORT ORDER BY                 |        |     5 |   235 |    11  (28)| 00:00:01 |
|*  2 |   HASH JOIN                    |        |     5 |   235 |    10  (20)| 00:00:01 |
|   3 |    MERGE JOIN                  |        |    14 |   504 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP    |    14 |   238 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | PK_EMP |    14 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                  |        |    14 |   266 |     4  (25)| 00:00:01 |
|   7 |      TABLE ACCESS FULL         | JOBS   |    14 |   266 |     3   (0)| 00:00:01 |
|*  8 |    TABLE ACCESS FULL           | DEPT   |     1 |    11 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
......
scott@TEST>select /*+ ordered */ e.ename,j.job,e.sal,d.deptno
  2    from emp e,dept d,jobs j
  3   where e.empno=j.empno
  4     and e.deptno=d.deptno
  5     and d.loc='CHICAGO'
  6   order by e.ename;
 
6 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 1175157407
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |     5 |   235 |    11  (28)| 00:00:01 |
|   1 |  SORT ORDER BY                 |              |     5 |   235 |    11  (28)| 00:00:01 |
|*  2 |   HASH JOIN                    |              |     5 |   235 |    10  (20)| 00:00:01 |
|   3 |    MERGE JOIN                  |              |     5 |   140 |     6  (17)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMP          |    14 |   238 |     2   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | IDX_EMP_DEPT |    14 |       |     1   (0)| 00:00:01 |
|*  6 |     SORT JOIN                  |              |     1 |    11 |     4  (25)| 00:00:01 |
|*  7 |      TABLE ACCESS FULL         | DEPT         |     1 |    11 |     3   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL           | JOBS         |    14 |   266 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
......

從上面的執行計劃可以看出不使用ordered Hint時表掃描的順序是DEPT->EMP->JOBS，但是使用ordered Hint後，表掃描的順序變為了EMP->JOBS->DEPT與目標SQL中的順序一致了，在修改了目標SQL文字之後表的掃描順序也相應地變為了EMP->DEPT->JOBS。

2、`LEADING`

LEADING是針對多個目標表的Hint，它的含義是讓優化器將我們指定的多個表的連線結果作為目標SQL表連線過程中的驅動結果集，並且將LEADING Hint中從左至右出現的第一個目標表作為整個表連線過程中的首個驅動表。

LEADING比ORDERED要溫和一些，因為它只是指定了首個驅動表和驅動結果集，沒有像ORDERED那樣完全指定了表連線的順序，也就是說LEADING給了優化器更大的調整餘地。

當LEADING Hint中指定的表並不能作為目標SQL的連線過程中的驅動表或者驅動結果集時，Oracle會忽略該Hint。

格式如下：

/*+ LEADING(目標表1 目標表2 …… 目標表n) */

使用範例：

select /*+ leading(t e) */ e.ename,j.job,e.sal,d.deptno
  from emp e,jobs j,dept d,emp_temp t 
 where e.empno=j.empno
   and e.deptno=d.deptno
   and d.loc='CHICAGO'
   and e.ename=t.ename
 order by e.ename;

例項：

--不使用Hint
scott@TEST>select e.ename,j.job,e.sal,d.deptno
  2    from emp e,jobs j,dept d,emp_temp t 
  3   where e.empno=j.empno
  4     and e.deptno=d.deptno
  5     and d.loc='CHICAGO'
  6     and e.ename=t.ename
  7   order by e.ename;
 
6 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 558051962
 
------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     5 |   270 |    12  (17)| 00:00:01 |
|   1 |  SORT ORDER BY                  |              |     5 |   270 |    12  (17)| 00:00:01 |
|*  2 |   HASH JOIN                     |              |     5 |   270 |    11  (10)| 00:00:01 |
|*  3 |    HASH JOIN                    |              |     5 |   235 |     8  (13)| 00:00:01 |
|   4 |     NESTED LOOPS                |              |       |       |            |          |
|   5 |      NESTED LOOPS               |              |     5 |   140 |     4   (0)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL         | DEPT         |     1 |    11 |     3   (0)| 00:00:01 |
|*  7 |       INDEX RANGE SCAN          | IDX_EMP_DEPT |     5 |       |     0   (0)| 00:00:01 |
|   8 |      TABLE ACCESS BY INDEX ROWID| EMP          |     5 |    85 |     1   (0)| 00:00:01 |
|   9 |     TABLE ACCESS FULL           | JOBS         |    14 |   266 |     3   (0)| 00:00:01 |
|  10 |    TABLE ACCESS FULL            | EMP_TEMP     |    14 |    98 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
......
--使用LEADING Hint
scott@TEST>select /*+ leading(t e) */ e.ename,j.job,e.sal,d.deptno
  2    from emp e,jobs j,dept d,emp_temp t 
  3   where e.empno=j.empno
  4     and e.deptno=d.deptno
  5     and d.loc='CHICAGO'
  6     and e.ename=t.ename
  7   order by e.ename;
 
6 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 937897748
 
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     5 |   270 |    15  (20)| 00:00:01 |
|   1 |  SORT ORDER BY        |          |     5 |   270 |    15  (20)| 00:00:01 |
|*  2 |   HASH JOIN           |          |     5 |   270 |    14  (15)| 00:00:01 |
|*  3 |    HASH JOIN          |          |     5 |   175 |    10  (10)| 00:00:01 |
|*  4 |     HASH JOIN         |          |    14 |   336 |     7  (15)| 00:00:01 |
|   5 |      TABLE ACCESS FULL| EMP_TEMP |    14 |    98 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| EMP      |    14 |   238 |     3   (0)| 00:00:01 |
|*  7 |     TABLE ACCESS FULL | DEPT     |     1 |    11 |     3   (0)| 00:00:01 |
|   8 |    TABLE ACCESS FULL  | JOBS     |    14 |   266 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------
......
--使用Ordered Hint
scott@TEST>select /*+ ordered */ e.ename,j.job,e.sal,d.deptno
  2    from emp e,jobs j,dept d,emp_temp t 
  3   where e.empno=j.empno
  4     and e.deptno=d.deptno
  5     and d.loc='CHICAGO'
  6     and e.ename=t.ename
  7   order by e.ename;
 
6 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2459794491
 
--------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |     5 |   270 |    14  (22)| 00:00:01 |
|   1 |  SORT ORDER BY                  |          |     5 |   270 |    14  (22)| 00:00:01 |
|*  2 |   HASH JOIN                     |          |     5 |   270 |    13  (16)| 00:00:01 |
|*  3 |    HASH JOIN                    |          |     5 |   235 |    10  (20)| 00:00:01 |
|   4 |     MERGE JOIN                  |          |    14 |   504 |     6  (17)| 00:00:01 |
|   5 |      TABLE ACCESS BY INDEX ROWID| EMP      |    14 |   238 |     2   (0)| 00:00:01 |
|   6 |       INDEX FULL SCAN           | PK_EMP   |    14 |       |     1   (0)| 00:00:01 |
|*  7 |      SORT JOIN                  |          |    14 |   266 |     4  (25)| 00:00:01 |
|   8 |       TABLE ACCESS FULL         | JOBS     |    14 |   266 |     3   (0)| 00:00:01 |
|*  9 |     TABLE ACCESS FULL           | DEPT     |     1 |    11 |     3   (0)| 00:00:01 |
|  10 |    TABLE ACCESS FULL            | EMP_TEMP |    14 |    98 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
......

從上面的執行計劃可以看出不使用Hint時表掃描順序是DEPT->EMP->JOBS->EMP_TEMP；使用LEADING Hint時表掃描順序是EMP_TEMP->EMP->DEPT->JOBS，EMP_TEMP做首個驅動表和表EMP的連線結果做為驅動結果集，與Hint要求一致。；使用Ordered Hint時表掃描順序是EMP->JOBS->DEPT->EMP_TEMP，與SQL中順序一致。

oracle常見hint
2017-11-23
Oracle
常見Oracle HINT的用法
2010-03-18
Oracle
Oracle常見hint(leading)
2016-11-09
Oracle
【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（一）
2021-02-11
Oracle
windows 中常見的一些埠
2018-04-18
Windows
Oracle中常見的33個等待事件小結
2016-04-18
Oracle事件
ORACLE進階之一：HINT
2012-02-10
Oracle
常用的Oracle Hint
2010-02-10
Oracle
Oracle Hint的用法
2009-09-22
Oracle
Oracle面試過程中常見的二十個問題
2010-11-25
Oracle面試
oracle hint
2010-12-11
Oracle
spring中常見的一些註解
2019-02-25
Spring
ORACLE的HINT詳解
2018-11-22
Oracle
（轉）Oracle Hint的用法
2010-11-23
Oracle
ORACLE使用HINT的方法
2007-01-30
Oracle
Oracle索引HINT的使用
2011-11-08
Oracle索引
Oracle中的sql hint
2024-03-07
OracleSQL
Java中常見的異常
2007-08-23
Java
ORACLE 部分HINT
2016-11-16
Oracle
oracle hint (續)
2008-09-26
Oracle
Oracle Hint 精華
2009-03-19
Oracle
總結HTTP中常見的一些名詞
2020-10-27
HTTP
zt_Oracle hint driving_site Hint的用法
2013-01-31
Oracle
【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（五）
2021-02-15
Oracle
【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（四）
2021-02-14
Oracle
【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（三）
2021-02-13
Oracle
【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（二）
2021-02-12
Oracle
不太常用的oracle hint整理
2011-12-01
Oracle
Java中常見的final類
2016-12-01
Java
資料分析中常見的錯誤是什麼（一）
2019-02-27
扒一扒我們生活中常見的品牌小程式
2018-03-14
使用HttpClient過程中常見的一些問題
2011-04-29
HTTPclient
oracle之hint概述
2017-04-13
Oracle
oracle hint簡述
2015-06-01
Oracle
oracle hint (續-0)
2008-09-26
Oracle
oracle hint_no_index
2012-12-21
OracleIndex
Oracle Hint 精華文章
2010-02-14
Oracle
Oracle Hint 詳解
2013-03-31
Oracle

Oracle中常見的Hint(一)

一、與優化器模式相關的Hint

1、ALL_ROWS

2、FIRST_ROWS(n)

3、RULE

二、與表訪問相關的Hint

1、FULL

2、ROIWD

三、與索引訪問相關的Hint

1、INDEX

2、NO_INDEX

3、INDEX_DESC

4、INDEX_COMBINE

5、INDEX_FFS

6、INDEX_JOIN

7、AND_EQUAL

四、與表連線順序相關的Hint

1、ORDERED

2、LEADING

相關文章

1、`ALL_ROWS`

2、`FIRST_ROWS(n)`

3、`RULE`

1、`FULL`

2、`ROIWD`

1、`INDEX`

2、`NO_INDEX`

3、`INDEX_DESC`

4、`INDEX_COMBINE`

5、`INDEX_FFS`

6、`INDEX_JOIN`

7、`AND_EQUAL`

1、`ORDERED`

2、`LEADING`