Oracle中常见的Hint(一)

落日映苍穹つ 2021-06-26 16:06 447阅读 0赞

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`中顺序一致。