Oracle常見的Hint(二)

一、與表連接配接方法相關的Hint

1、USE_MERGE

USE_MERGE是針對多個目标表的Hint，它的含義是讓優化器将我們指定的多個表作為被驅動表與其他表或結果集做排序合并連接配接。在USE_MERGE Hint中指定的目标表應該是排序合并連接配接的被驅動表，如果指定的表并不能作為排序合并連接配接的被驅動表，則此時Oracle要麼會忽略該Hint，要麼會忽略該表。

格式如下：

/*+ USE_MERGE(目标表1 目标表2 …… 目标表n) */  

/*+ USE_MERGE(目标表1,目标表2,……,目标表n) */ 

使用範例：

select /*+ use_merge(emp) */ *

  from emp,dept

 where emp.deptno=dept.deptno;

select /*+ use_merge(e j d t) */ 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;

執行計劃在表EMP、DEPT、JOBS、EMP_TEMP之間做表連接配接時全部走的是排序合并連接配接，這說明USE_MERGE Hint已經生效了。Hint中指定的表EMP并不能作為排序合并連接配接的被驅動表，但上述Hint中指定的其他三個表DEPT、JOBS、EMP_TEMP是可以作為排序合并連接配接的被驅動表的，是以Oracle隻是忽略了表EMP，而沒有忽略USE_MERGE Hint。

正是因為Oracle可能會忽略USE_MERGE Hint或忽略其中指定的被驅動表，是以我們通常會用LEADING Hint(或ORDERED Hint)配合USE_MERGE Hint使用，以讓優化器走出我們想要的執行計劃。

select /*+ leading(e) use_merge(j d t) */ e.ename,j.job,e.sal,d.deptno

2、NO_USE_MERGE

NO_USE_MERGE是針對多個目标表的Hint，它是USE_MERGE的反義Hint，其含義是不讓優化器将我們指定的多個表作為被驅動表與其他表或結果集做排序合并連接配接。

在NO_USE_MERGE Hint中指定的目标表應該是原先在排序合并連接配接中的被驅動表，否則Oracle要麼會忽略該NO_USE_MERGE Hint，要麼會忽略該表。正是因為Oracle可能會忽略NO_USE_MERGE Hint或忽略其中指定的被驅動表，是以我們通常會用LEADING Hint(或ORDERED Hint)配合NO_USE_MERGE Hint使用，以讓優化器走出我們想要的執行計劃。

/*+ NO_USE_MERGE(目标表1 目标表2 …… 目标表n) */  

/*+ NO_USE_MERGE(目标表1,目标表2,……,目标表n) */  

select /*+ no_use_merge(emp) */ *

select /*+ no_use_merge(e j d t) */ e.ename,j.job,e.sal,d.deptno

3、USE_NL

USE_NL是針對多個目标表的Hint，它的含義是讓優化器将我們指定的多個表作為驅動表與其他表或結果集做嵌套循環連接配接。在USE_NL Hint中指定的目标表應該是嵌套循環連接配接中的被驅動表，否則Oracle要麼會忽略該USE_NL Hint，要麼會忽略該表。正是因為Oracle可能會忽略USE_NL Hint或忽略其中指定的被驅動表，是以我們通常會用LEADING Hint(或ORDERED Hint)配合USE_NL Hint使用，以讓優化器走出我們想要的執行計劃。

/*+ USE_NL(目标表1 目标表2 …… 目标表n) */  

/*+ USE_NL(目标表1,目标表2,……,目标表n) */ 

select /*+ use_nl(dept) */ *

select /*+ use_nl(e j d t) */ e.ename,j.job,e.sal,d.deptno

USE_NL Hint的使用者和USE_MERGE Hint的用法相同。

4、NO_USE_NL

NO_USE_NL是針對多個目标表的Hint，它是USE_NL的反義Hint，其含義是不讓優化器将我們指定的多個表連接配接作為被驅動表與其他表或結果集做嵌套循環連接配接。在NO_USE_NL Hint中指定的目标表應該是嵌套循環連接配接中的被驅動表，否則Oracle要麼會忽略該NO_USE_NL Hint，要麼會忽略該表。正是因為Oracle可能會忽略NO_USE_NL Hint或忽略其中指定的被驅動表，是以我們通常會用LEADING Hint(或ORDERED Hint)配合NO_USE_NL Hint使用，以讓優化器走出我們想要的執行計劃。

/*+ NO_USE_NL(目标表1 目标表2 …… 目标表n) */  

/*+ NO_USE_NL(目标表1,目标表2,……,目标表n) */ 

select /*+ no_use_nl(dept) */ *

select /*+ no_use_nl(e j d t) */ e.ename,j.job,e.sal,d.deptno

NO_USE_NL Hint的使用者和NO_USE_MERGE Hint的用法相同。

5、USE_HASH

USE_HASH是針對多個目标表的Hint，它的含義是讓優化器将我們指定的多個表作為被驅動表與其他表或結果集做哈希連接配接。在USE_HASH Hint中指定的目标表應該是哈希連接配接中的被驅動表，否則Oracle要麼會忽略該USE_HASH Hint，要麼會忽略該表。正是因為Oracle可能會忽略USE_HASH Hint或忽略其中指定的被驅動表，是以我們通常會用LEADING Hint(或ORDERED Hint)配合USE_HASH Hint使用，以讓優化器走出我們想要的執行計劃。

/*+ USE_HASH(目标表1 目标表2 …… 目标表n) */  

/*+ USE_HASH(目标表1,目标表2,……,目标表n) */ 

select /*+ use_hash(emp) */ *

select /*+ use_hash(e j d t) */ e.ename,j.job,e.sal,d.deptno

USE_HASH Hint的使用者和USE_MERGE Hint的用法相同。

 6、NO_USE_HASH

 NO_USE_HASH是針對多個目标表的Hint，它是HASH的反義Hint，其含義是不讓優化器将我們指定的多個表作為被驅動表與其他表或結果集做哈希連接配接。在NO_USE_HASH Hint中指定的目标表應該是哈希連接配接中的被驅動表，否則Oracle要麼會忽略該NO_USE_HASH Hint，要麼會忽略該表。正是因為Oracle可能會忽略NO_USE_HASH Hint或忽略其中指定的被驅動表，是以我們通常會用LEADING Hint(或ORDERED Hint)配合NO_USE_HASH Hint使用，以讓優化器走出我們想要的執行計劃。

/*+ NO_USE_HASH(目标表1 目标表2 …… 目标表n) */  

/*+ NO_USE_HASH(目标表1,目标表2,……,目标表n) */ 

select /*+ no_use_hash(emp) */ *

select /*+ no_use_hash(e j d t) */ e.ename,j.job,e.sal,d.deptno

7、MERGE_AJ

MERGE_AJ是針對子查詢的Hint，它的含義是讓優化器對相關目标表執行排序合并反連接配接。

/*+ MERGE_AJ */

select * 

  from emp

 where deptno not in (select /*+ merge_aj */deptno

                       from dept

  where loc='CHICAGO');

上述SQL的中Hint的含義是讓優化器對目标表EMP和DEPT執行排序合并反連接配接。MERGE_AJ是針對子查詢的Hint，是以/*+ merge_aj */的位置在子查詢所在的Query Block中。

還可以寫成

select /*+ merge_aj(@zhaoxu) */ * 

 where deptno not in (select /*+ qb_name(zhaoxu) */deptno

8、NL_AJ

NL_AJ是針對子查詢的Hint，它的含義是讓優化器對相關目标表執行嵌套循環反連接配接。

/*+ NL_AJ */

 where deptno not in (select /*+ nl_aj */deptno

NL_AJ的使用者與MERGE_AJ Hint的用法相同。

9、HASH_AJ

HASH_AJ是針對子查詢的Hint，它的含義是讓優化器對相關目标表執行哈希反連接配接。

/*+ HASH_AJ */

 where deptno not in (select /*+ hash_aj */deptno

HASH_AJ的使用者與MERGE_AJ Hint的用法相同。

10、MERGE_SJ

MERGE_SJ是針對子查詢的Hint，它的含義是讓優化器對相關目标表執行排序合并半連接配接。

/*+ MERGE_SJ */

  from dept d

 where exists(select /*+ merge_sj */1

                from emp e

   where e.deptno=d.deptno

     and e.sal>800);

MERGE_SJ Hint的用法秘MERGE_AJ Hint的用法相同。

11、NL_SJ

NL_SJ是針對子查詢的Hint，它的含義是讓優化器對相關目标表執行嵌套循環半連接配接。

/*+ NL_SJ */

 where exists(select /*+ nl_sj */1

NL_SJ Hint的用法秘MERGE_AJ Hint的用法相同。

12、HASH_SJ

HASH_SJ是針對子查詢的Hint，它的含義是讓優化器對相關目标表執行哈希半連接配接。

/*+ HASH_SJ */

 where exists(select /*+ hash_sj */1

HASH_SJ Hint的用法秘MERGE_AJ Hint的用法相同。

二、與并行相關的Hint

1、PARALLEL

在Oracle 11gR2之前，PARALLEL是針對單個目标表的Hint，它的含義是讓優化器以指定的或者系統計算出來的并行度去并行通路目标表。從Oracle 11gR2開始，Oracle引入了自動并行，相應的，PARALLEL Hint也随之發生了變化。

在Oracle 11gR2中，PARALLEL Hint的作用範圍和用法均發生了變化，Oracle 11gR2中的PARALLEL Hint是針對整個目标SQL的Hint，其含義是讓優化器以指定的或者系統計算出來的并行度去并行執行目标SQL的執行計劃中所有可以被并行執行的執行步驟。當然，舊的針對單個目标表的PARALLEL Hint依然可以在Oracle 11gR2中使用，不過其優先級會比新的針對整個目标SQL的PARALLEL Hint低，即如果目标SQL中同時出現了新舊兩種格式的PARALLEL Hint，Oracle會選擇新的針對整個目标SQL的PARALLEL Hint，而忽略舊的針對單個目标表的PARALLEL Hint。

新的針對整個目标SQL的PARALLEL Hint的格式有如下4種：

格式1 /*+ PARALLEL */

格式2 /*+ PARALLEL(AUTO) */

格式3 /*+ PARALLEL(MANUAL) */

格式4 /*+ PARALLEL(指定的并行度) */

使用了格式1的目标SQL總是會以并行的方式執行，Oracle此時會計算出一個并行度，這個計算出來的并行度總是大于或等于2。

使用了格式2的目标SQL，Oracle會計算出一個并行度，但這個計算出來的并行度可能會是1，是以使用目标SQL不一定總是以并行的方式執行。

使用了格式3的目标SQL，能否并行執行完全取決于目标SQL中相關對象的并行度的設定。舉例來說，如果目标表的并行度的設定大于1時，則目标SQL會以并行的方式執行，而且并行執行的并行度就等于該目标表上的并行度設定。如果目标表并行度為1，則會以串行方式執行。

使用了格式4的目标SQL總是會以該Hint中指定的并行度去執行目标SQL。

舊的針對單個目标表的PARALLEL Hint的格式有如下2種：

格式1 /*+ PARALLEL(目标表 指定的并行度) */或/*+ PARALLEL(目标表,指定的并行度) */

格式2 /*+ PARALLEL(目标表 DEFAULT) */或/*+ PARALLEL(目标表,DEFAULT) */

使用了格式1的目标SQL總是會以該Hint中指定的并行度去通路目标表。

使用了格式2的目标SQL總是會根據相關系統參數計算出來的預設并行度去并行通路目标表。

針對整個目标SQL的PARALLEL Hint的使用範例：

select /*+ parallel */ ename from emp;

select /*+ parallel(auto) */ ename from emp;

select /*+ parallel(manual) */ ename from emp;

select /*+ parallel(6) */ ename from emp;

針對單個目标表的PARALLEL Hint的使用範例：

select /*+ parallel(emp 2) */ ename from emp;

select /*+ parallel(emp default) */ ename from emp;

可以從V$PQ_SLAVE中檢視目前系統并行執行所使用的并行子程序的詳情，視圖中的SESSIONS字段表示使用并行子程序的session的總數，即使是同一個session，如果它多次使用一個并行子程序，則該并行子程序在視圖中對應記錄的字段SESSIONS的值也會遞增。在特定的情況下，通過這個字段我可分析出目标SQL在執行的的實際并行度。

在Oracle 11gR2中并行Hint也可用于全局臨時表。

2、NO_PARALLEL

在Oracle 11gR2中，和PARALLEL Hint一樣，NO_PARALLEL Hint的作用範圍和用法均發生了變化，Oracle 11gR2中的NO_PARALLEL是針對整個目标構圖的Hint，它是PARALLEL Hint的反義Hint，其含義是不讓優化器并行執行目标SQL的執行計劃中所可以被并行執行的執行步驟。當然，舊的針對單個目标表的NO_PARALLEL Hint依然可以在Oracle 11gR2中使用。

新的針對整個目标SQL的NO_PARALLEL格式如下：

/*+ NO_PARALLEL */

舊的針對單個目标表的NO_PARALLEL格式如下

/*+ NO_PARALLEL(目标表) */

針對整個目标SQL的NO_PARALLEL使用範例：

select /*+ no_parallel */ * from emp;

針對單個目标表的NO_PARALLEL使用範例：

select /*+ no_parallel(emp) */ * from emp;

3、PARALLEL_INDEX

PARALLEL_INDEX是針對單個目标表的Hint，它的含義是讓優化器以指定的或者系統計算出來的并行度去對目标表上的目标分區索引執行并行索引掃描操作。

使用格式有如下5種：

格式1 /*+ PARALLEL_INDEX(目标表 目标分區索引 指定的并行度) */

格式2 /*+ PARALLEL_INDEX(目标表 目标分區索引 DEFAULT) */

格式3 /*+ PARALLEL_INDEX(目标表 目标分區索引1 目标分區索引2 …… 目标分區索引n 目标分區索引1的并行度 目标分區索引2的并行度 …… 目标分區索引n的并行度) */

格式4 /*+ PARALLEL_INDEX(目标表 目标分區索引1 目标分區索引2 …… 目标分區索引n DEFAULT DEFAULT …… DEFAULT) */

格式5 /*+ PARALLEL_INDEX(目标表) */

使用了格式1的目标SQL總是會以格式1中指定的并行度去并行通路該目标表上的目标分區索引

使用了格式2的目标SQL總是會以根據相關系統參數計算出來的預設并行度去并行通路該目标表上的目标分區索引。這裡優化器可能會對計算出來的預設并行度做一定的調整，即使用了格式2的目标SQL的實際并行度不一定就是目前系統的預設并行度。

可以在PARALLEL_INDEX Hint中指定多個目标索引，并分别指定它們各自對應的并行度(格式3)或将它們的各自對應的并行度統一指定為Oracle計算出來的預設值(格式4)或隻指定目标表(格式5，表示同時指定了該目标表上所有已存在的索引)，此時Oracle會分别計算對它們各自做并行掃描的成本，并從中選擇一個成本值最低的作為待并行掃描的目标索引。

Hint中的分隔符也可以用","。

select /*+ parallel_index(emp_par idx_par 3) */ emp from emp_par;

select /*+ parallel_index(emp_par idx_par default) */ emp from emp_par;

select /*+ index(emp_par idx_par_1) parallel_index(emp_par idx_par_1 idx_par_2 3 3) */ emp from emp_par;

4、NO_PARALLEL_INDEX

NO_PARALLEL_INDEX是針對單個目标表的Hint，它是PARALLEL_INDEX Hint的反義Hint，其含義是不讓優化器對Hint指定的位于目标表上的目标分區索引執行并行索引掃描操作。

格式1 /*+ NO_PARALLEL_INDEX(目标表 目标分區索引) */

格式2 /*+ NO_PARALLEL_INDEX(目标表 目标分區索引1 目标分區索引2 …… 目标分區索引n) */

格式3 /*+ NO_PARALLEL_INDEX(目标表) */

分隔符也可以用","。

select /*+ no_parallel_index(emp_par idx_par) */ empno from emp_par;

select /*+ no_parallel_index(emp_par idx_par1 idx_par_2) */ empno from emp_par;

select /*+ no_parallel_index(emp_par) */ empno from emp_par;

三、與查詢轉換相關的Hint

1、USE_CONCAT

USE_CONCAT是針對整個目标SQL的Hint，它的含義是讓優化器對目标SQL使用IN-List擴充(IN-List Expansion)或OR擴充(OR Expansion)。

/*+ USE_CONCAT */

select /*+ use_concat */ emp,ename from emp where empno in (7654,7698);

select /*+ use_concat */ * from emp where mgr=7902 and deptno=20;

執行計劃中對應的關鍵字是“CONCATENATION”。

2、NO_EXPAND

NO_EXPAND是針對整個目标SQL的Hint，它是USE_CONCAT的反義Hint，其含義是不讓優化器對目标SQL使用IN-List擴充或OR擴充。

/*+ NO_EXPAND */

select /*+ no_expand */ emp,ename from emp where empno in (7654,7698);

select /*+ no_expand */ * from emp where mgr=7902 and deptno=20;

執行計劃中不出現關鍵字是“CONCATENATION”。

3、MERGE

MERGE是針對單個目标視圖的Hint，它的含義是讓優化器對目标視圖執行視圖合并(View Mergeing)。

使用格式：

/*+ MERGE(目标視圖) */

如果目标視圖是一個内嵌視圖，則MERGE Hint也可以出現在其視圖定義語句所在的Query Block中，隻是此時Hint中就不應該再帶上該内嵌視圖的名稱，其格式應為/*+ MERGE */

select /*+ merge(dept_view) */ empno,ename,dname

  from emp,dept_view

 where emp.deptno=dept_view.deptno;

select empno,ename,dname 

  from emp,

       (select /*+ merge */*

      from dept

 where local='CHICAGO') dept_view_inline

 where emp.deptno=dept_view_inline.deptno;

4、NO_MERGE

NO_MERGE是針對單個目标視圖的Hint，它是MERGE的反義Hint，其含義是不上優化器對目标視圖執行視圖合并。

/*+ NO_MERGE(目标視圖) */

如果目标視圖是一個内嵌視圖，則MERGE Hint也可以出現在其視圖定義語句所在的Query Block中，隻是此時Hint中就不應該再帶上該内嵌視圖的名稱，其格式應為/*+ NO_MERGE */

select /*+ no_merge(dept_view) */ empno,ename,dname

       (select /*+ no_merge */*

5、UNNEST

UNNEST是針對子查詢的Hinit，它的含義是讓優化器對目标SQL的子查詢執行子查詢展開(Subquery Unnesting)。

/*+ UNNEST */

select *

 where deptno not in (select /*+ unnest */ deptno

                        from dept

   where loc='CHICAGO');

/*+ unnest */的位置應位于子查詢所在的Query Block中。

6、NO_UNNEST

NO_UNNEST是針對子查詢的Hint，它是UNNEST的反義Hint，其含義是不讓優化器對目标SQL中的子查詢使用子查詢展開。

/*+ NO_UNNEST */

 where deptno not in (select /*+ no_unnest */ deptno

/*+ no_unnest */的位置在子查詢所在的Query Block中。

7、EXPAND_TABLE

EXPAND_TABLE是針對單個目标表的Hint，它的含義是讓優化器在不考慮成本的情況下，對目标表SQL中的目标表執行表擴充。

/*+ EXPAND_TABLE(目标表) */

select /*+ expand_table(t1) */ t2.cust_city,sum(t1.amount_sold) amount_sold_total

  from sales t1,customers t2,products t3,channels t4

 where t1.cust_id=t2.cust_id

   and t1.prod_id=t3.prod_id

   and t1.channel_id=t4.channel_id

   and t2.country_id=52771

   and t3.prod_name='Mouse Pad'

   and t4.channel_desc='Internet'

   and time_id between to_date('2000-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS') and to_date('2004-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS');

8、NO_EXPAND_TABLE

NO_EXPAND_TABLE是針對單個目标表的Hint，它是EXPAND_TABLE的反義Hint，其含義是不讓優化器對目标SQL中的目标表使用表擴充。。

/*+ NO_EXPAND_TABLE(目标表) */

select /*+ no_expand_table(t1) */ t2.cust_city,sum(t1.amount_sold) amount_sold_total

      本文轉自hbxztc 51CTO部落格，原文連結：http://blog.51cto.com/hbxztc/1908650，如需轉載請自行聯系原作者