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

  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;

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) */ *

  from emp,dept

 where emp.deptno=dept.deptno;

select /*+ no_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;

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) */ *

  from emp,dept

 where emp.deptno=dept.deptno;

select /*+ use_nl(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;

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) */ *

  from emp,dept

 where emp.deptno=dept.deptno;

select /*+ no_use_nl(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;

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) */ *

  from emp,dept

 where emp.deptno=dept.deptno;

select /*+ use_hash(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;

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) */ *

  from emp,dept

 where emp.deptno=dept.deptno;

select /*+ no_use_hash(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;

NO_USE_NL Hint的用户和NO_USE_MERGE Hint的用法相同。

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) */ * 

  from emp

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

                       from dept

 where loc='CHICAGO');

8、NL_AJ

NL_AJ是针对子查询的Hint，它的含义是让优化器对相关目标表执行嵌套循环反连接。

格式如下：

/*+ NL_AJ */

使用范例：

select * 

  from emp

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

                       from dept

 where loc='CHICAGO');

NL_AJ的用户与MERGE_AJ Hint的用法相同。

9、HASH_AJ

HASH_AJ是针对子查询的Hint，它的含义是让优化器对相关目标表执行哈希反连接。

格式如下：

/*+ HASH_AJ */

使用范例：

select * 

  from emp

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

                       from dept

 where loc='CHICAGO');

HASH_AJ的用户与MERGE_AJ Hint的用法相同。

10、MERGE_SJ

MERGE_SJ是针对子查询的Hint，它的含义是让优化器对相关目标表执行排序合并半连接。

格式如下：

/*+ MERGE_SJ */

使用范例：

select * 

  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 */

使用范例：

select * 

  from dept d

 where exists(select /*+ nl_sj */1

                from emp e

  where e.deptno=d.deptno

    and e.sal>800);

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

12、HASH_SJ

HASH_SJ是针对子查询的Hint，它的含义是让优化器对相关目标表执行哈希半连接。

格式如下：

/*+ HASH_SJ */

使用范例：

select * 

  from dept d

 where exists(select /*+ hash_sj */1

                from emp e

  where e.deptno=d.deptno

    and e.sal>800);

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

  from emp,dept_view

 where emp.deptno=dept_view.deptno;

select empno,ename,dname 

  from emp,

       (select /*+ no_merge */*

     from dept

where local='CHICAGO') dept_view_inline

 where emp.deptno=dept_view_inline.deptno;

5、UNNEST

UNNEST是针对子查询的Hinit，它的含义是让优化器对目标SQL的子查询执行子查询展开(Subquery Unnesting)。

格式如下：

/*+ UNNEST */

使用范例：

select *

  from emp

 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 */

使用范例：

select *

  from emp

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

                        from dept

  where loc='CHICAGO');

/*+ 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

  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');

参考《基于Oracle的SQL优化》