nbsp;
-------------------------------------------------------------------------------------------
| 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|