共计 60419 个字符，预计需要花费 152 分钟才能阅读完成。
Oracle 中的 Hint 可以用来调整 SQL 的执行计划，提高 SQL 执行效率。下面分类介绍 Oracle 数据库中常见的 Hint。这里描述的是 Oracle11gR2 中的常见 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 11gR2 中即使使用了 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 的 SQL 优化》
官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF005
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-03/141964p2.htm
一、与表连接方法相关的 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’);
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-03/141964.htm
Oracle 中的 Hint 可以用来调整 SQL 的执行计划，提高 SQL 执行效率。下面分类介绍 Oracle 数据库中常见的 Hint。这里描述的是 Oracle11gR2 中的常见 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 11gR2 中即使使用了 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 的 SQL 优化》
官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41573/hintsref.htm#PFGRF005
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
更多详情见请继续阅读下一页的精彩内容：http://www.linuxidc.com/Linux/2017-03/141964p2.htm
