Hint學習筆記

Hint概述
基於代價的最佳化器是很聰明的，在絕大多數情況下它會選擇正確的最佳化器，減輕了DBA的負擔。但有時它也聰明反被聰明誤，選擇了很差的執行計劃，使某個語句的執行變得奇慢無比。

此時就需要DBA進行人為的干預，告訴最佳化器使用我們指定的存取路徑或連線型別生成執行計劃，從 而使語句高效的執行。例如，如果我們認為對於一個特定的語句，執行全表掃描要比執行索引掃描更有效，則我們就可以指示最佳化器使用全表掃描。在Oracle 中，是透過為語句新增 Hints(提示)來實現干預最佳化器最佳化的目的。
不建議在程式碼中使用hint，在程式碼使用hint使得CBO無法根據實際的資料狀態選擇正確的執行計劃。畢竟 資料是不斷變化的， 10g以後的CBO也越來越完善，大多數情況下我們該讓Oracle自行決定採用什麼執行計劃。

Oracle Hints是一種機制，用來告訴最佳化器按照我們的告訴它的方式生成執行計劃。我們可以用Oracle Hints來實現：
1) 使用的最佳化器的型別
2) 基於代價的最佳化器的最佳化目標，是all_rows還是first_rows。
3) 表的訪問路徑，是全表掃描，還是索引掃描，還是直接利用rowid。
4) 表之間的連線型別
5) 表之間的連線順序
6) 語句的並行程度

除了”RULE”提示外，一旦使用的別的提示，語句就會自動的改為使用CBO最佳化器，此時如果你的資料字典中沒有統計資料，就會使用預設的統計資料。所以建議大家如果使用CBO或Hints提示，則最好對錶和索引進行定期的分析。

如何使用Hints:

Hints只應用在它們所在sql語句塊(statement block，由select、update、delete關鍵字標識)上，對其它SQL語句或語句的其它部分沒有影響。如：對於使用union操作的2個sql語句，如果只在一個sql語句上有Hints，則該Hints不會影響另一個sql語句。

我們可以使用註釋(comment)來為一個語句新增Hints，一個語句塊只能有一個註釋，而且註釋只能放在SELECT, UPDATE, or DELETE關鍵字的後面

使用Oracle Hints的語法：

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */

or

{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

註解：
1) DELETE、INSERT、SELECT和UPDATE是標識一個語句塊開始的關鍵字，包含提示的註釋只能出現在這些關鍵字的後面，否則提示無效。
2) “+”號表示該註釋是一個Hints，該加號必須立即跟在”/*”的後面，中間不能有空格。
3) hint是下面介紹的具體提示之一，如果包含多個提示，則每個提示之間需要用一個或多個空格隔開。
4) text 是其它說明hint的註釋性文字

5)使用表別名。如果在查詢中指定了表別名，那麼提示必須也使用表別名。例如：select /*+ index(e,dept_idx) */ * from emp e;
6)不要在提示中使用模式名稱：如果在提示中指定了模式的所有者，那麼提示將被忽略。例如：
select /*+ index(scott.emp,dept_idx) */ * from emp

注意：如果你沒有正確的指定Hints，Oracle將忽略該Hints，並且不會給出任何錯誤。
hint被忽略

如果CBO認為使用hint會導致錯誤的結果時，hint將被忽略，詳見下例
SQL> select /*+ index(t t_ind) */ count(*) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522
-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    57   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 50366 |    57   (2)| 00:00:01 |
-------------------------------------------------------------------

因為我們是對記錄求總數，且我們並沒有在建立索引時指定不能為空，索引如果CBO選擇在索引上進行count時，但索引欄位上的值為空時，結果將不準確，故CBO沒有選擇索引。
SQL>  select /*+ index(t t_ind) */ count(id) from t;
Execution Plan
----------------------------------------------------------
Plan hash value: 646498162
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |     5 |   285   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE  |       |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN| T_IND | 50366 |   245K|   285   (1)| 00:00:04 |
--------------------------------------------------------------------------

因為我們只對id進行count，這個動作相當於count索引上的所有id值，這個操作和對錶上的id欄位進行count是一樣的(組函式會忽略null值)

Hint的具體用法

和最佳化器相關的hint

1、/*+ ALL_ROWS */
表明對語句塊選擇基於開銷的最佳化方法,並獲得最佳吞吐量,使資源消耗最小化.
SELECT /*+ ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
2、/*+ FIRST_ROWS(n) */
表明對語句塊選擇基於開銷的最佳化方法,並獲得最佳響應時間,使資源消耗最小化.
SELECT /*+FIRST_ROWS(20) */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
3、/*+ RULE*/
表明對語句塊選擇基於規則的最佳化方法.
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO='SCOTT';
和訪問路徑相關的hint

1、/*+ FULL(TABLE)*/
表明對錶選擇全域性掃描的方法.
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO='SCOTT';
2、/*+ INDEX(TABLE INDEX_NAME) */
表明對錶選擇索引的掃描方法.
SELECT /*+INDEX(BSEMPMS SEX_INDEX) */ * FROM BSEMPMS WHERE SEX='M';
5、/*+ INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';
6、/*+ INDEX_COMBINE*/
為指定表選擇點陣圖訪問路經,如果INDEX_COMBINE中沒有提供作為引數的索引,將選擇出點陣圖索引的布林組合方式.
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */  * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE
7、/*+ INDEX_JOIN(TABLE INDEX_NAME1 INDEX_NAME2) */
當謂詞中引用的列都有索引的時候，可以透過指定採用索引關聯的方式，來訪問資料
select /*+ index_join(t t_ind t_bm) */ id from t where id=100 and object_name='EMPLOYEES'
8、/*+ INDEX_DESC(TABLE INDEX_NAME)*/
表明對錶選擇索引降序的掃描方法.

SELECT /*+INDEX_DESC(BSEMPMS PK_BSEMPMS) */ * FROM BSEMPMS WHERE DPT_NO='SCOTT';
9、/*+ INDEX_FFS(TABLE INDEX_NAME) */
對指定的表執行快速全索引掃描,而不是全表掃描的辦法.
SELECT /* + INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

10、/*+ INDEX_SS(T T_IND) */
從9i開始，oracle引入了這種索引訪問方式。當在一個聯合索引中，某些謂詞條件並不在聯合索引的第一列時，可以透過Index Skip Scan來訪問索引獲得資料。當聯合索引第一列的唯一值個數很少時，使用這種方式比全表掃描效率高。

SQL> create table t as select 1 id,object_name from dba_objects;
Table created.
SQL> insert into t select 2,object_name from dba_objects;      
50366 rows created.
SQL> insert into t select 3,object_name from dba_objects;      
50366 rows created.
SQL> insert into t select 4,object_name from dba_objects;      
50366 rows created.
SQL> commit;
Commit complete.
SQL> create index t_ind on t(id,object_name);
Index created.
SQL> exec dbms_stats.gather_table_stats('HR','T',cascade=>true);
PL/SQL procedure successfully completed.
執行全表掃描
SQL> select /*+ full(t) */ * from t where object_name='EMPLOYEES';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     5 |   135 |   215   (3)| 00:00:03 |
|*  1 |  TABLE ACCESS FULL| T    |     5 |   135 |   215   (3)| 00:00:03 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        942  consistent gets
          0  physical reads
          0  redo size
        538  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
不採用hint
SQL>  select * from t where object_name='EMPLOYEES';
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2869677071
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     5 |   135 |     5   (0)| 00:00:01 |
|*  1 |  INDEX SKIP SCAN | T_IND |     5 |   135 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("OBJECT_NAME"='EMPLOYEES')
       filter("OBJECT_NAME"='EMPLOYEES')
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         17  consistent gets
          1  physical reads
          0  redo size
        538  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed
當全表掃描掃描了942個塊，聯合索引只掃描了17個資料塊。可以看到聯合索引的第一個欄位的值重複率很高時，即使謂詞中沒有聯合索引的第一個欄位，依然會使用index_ss方式，效率遠遠高於全表掃描效率。但當 第一個欄位的值重複率很低時，使用 index_ss的效率要低於 全表掃描，讀者可以自行實驗
和表的關聯相關的hint

/*+ leading(table_1,table_2) */

在多表關聯查詢中，指定哪個表作為驅動表，即告訴最佳化器首先要訪問哪個表上的資料。
select /*+ leading(t,t1) */ t.* from t,t1 where t.id=t1.id;
/*+ order */

讓Oracle根據from後面表的順序來選擇驅動表，oracle建議使用leading，他更為靈活
select /*+ order */ t.* from t,t1 where t.id=t1.id;
/*+ use_nl(table_1,table_2) */
在多表關聯查詢中，指定使用nest loops方式進行多表關聯。

select /*+ use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ use_hash(table_1,table_2) */
在多表關聯查詢中，指定使用hash join方式進行多表關聯。

select /*+ use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;

在多表關聯查詢中，指定使用hash join方式進行多表關聯，並指定表t為驅動表。

select /*+ use_hash(t,t1) leading(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ use_merge(table_1,table_2) */
在多表關聯查詢中，指定使用merge join方式進行多表關聯。

select /*+ use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_nl(table_1,table_2) */
在多表關聯查詢中，指定不使用nest loops方式進行多表關聯。

select /*+ no_use_nl(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_hash(table_1,table_2) */
在多表關聯查詢中，指定不使用hash join方式進行多表關聯。

select /*+ no_use_hash(t,t1) */ t.* from t,t1 where t.id=t1.id;

/*+ no_use_merge(table_1,table_2) */
在多表關聯查詢中，指定不使用merge join方式進行多表關聯。

select /*+ no_use_merge(t,t1) */ t.* from t,t1 where t.id=t1.id;

其他常用的hint

/*+ parallel(table_name n) */

在sql中指定執行的並行度，這個值將會覆蓋自身的並行度

select /*+ parallel(t 4) */ count(*)  from t;

/*+ no_parallel(table_name) */

在sql中指定執行的不使用並行

select /*+ no_parallel(t) */ count(*)  from t;

/*+ append */以直接載入的方式將資料載入入庫

insert into t /*+ append */ select * from t;

/*+ dynamic_sampling(table_name n) */

設定sql執行時動態採用的級別，這個級別為0~10
select /*+ dynamic_sampling(t 4) */ * from t where id > 1234

/*+ cache(table_name) */
進行全表掃描時將table置於LRU列表的最活躍端，類似於table的cache屬性

select /*+ full(employees) cache(employees) */ last_name from employees
附錄hint表格

qb_name(qb)

explain plan for select /*+qb_name(qb) index(@qb t idx_daodao)*/* from daodao1 t where object_id >= 5; 

-----這裡表示qb模組走表t的idx_daodao索引  
 

在sql中指定執行的不使用並行

select /*+ no_parallel(t) */ count(*)  from t;

/*+ append */以直接載入的方式將資料載入入庫

insert into t /*+ append */ select * from t;

/*+ dynamic_sampling(table_name n) */

設定sql執行時動態採用的級別，這個級別為0~10
select /*+ dynamic_sampling(t 4) */ * from t where id > 1234

/*+ cache(table_name) */
進行全表掃描時將table置於LRU列表的最活躍端，類似於table的cache屬性

select /*+ full(employees) cache(employees) */ last_name from employees 6、/*+ INDEX_COMBINE*/
為指定表選擇點陣圖訪問路經,如果INDEX_COMBINE中沒有提供作為引數的索引,將選擇出點陣圖索引的布林組合方式.
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI) */  * FROM BSEMPMS
WHERE SAL<5000000 AND HIREDATE  

/*+ index_join(table index1 index2) */ 

qb_name(SB)

 from (select /*+ first_rows  LEADING("HJ"@"SB") no_expand */