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