index_combine and index_john

and_equal是Oracle支持的一种特定操作，可以将多个单列索引进行合并（Index Merge）输出查询结果。
同时and_equal可以通过Hints来进行强制，最少指定两个索引，最多指定5个。

以下的执行计划是常见的and_equal执行方式：

SQL> select /*+ and_equal(t1 iu ii) */ username,password from t1 where username='EYGLE' and user_id=58;

USERNAME                       PASSWORD
------------------------------ ------------------------------
EYGLE                          B726E09FE21F8E83

执行计划
----------------------------------------------------------
Plan hash value: 1425017857

------------------------------------------------------------------------------------
| Id | Operation                   | Name | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    47 |     2   (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T1   |     1 |    47 |     2   (0)| 00:00:01 |
|   2 |   AND-EQUAL                 |      |       |       |            |          |
|* 3 |    INDEX RANGE SCAN         | II   |     1 |       |     1   (0)| 00:00:01 |
|* 4 |    INDEX RANGE SCAN         | IU   |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("USERNAME"='EYGLE' AND "USER_ID"=58)
   3 - access("USER_ID"=58)
   4 - access("USERNAME"='EYGLE')

但是从Oracle 10g开始，and_equal操作被废弃（depricated）掉，Oracle不再支持。这里废弃的含义并不是被彻底移除，而是说不再进行改进，通过hints仍然可以强制实现Index Merge的and_equal操作。

以上的输出就是来自Oracle 10.2.0.4，在使用rule提示时，Oracle会主动选择and_equal，所以不再支持是因为，这种方法限制过多，而且远不如复合索引来的灵活：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select /*+ rule */ username,password from t1 where username='EYGLE' and user_id=58;

USERNAME                       PASSWORD
------------------------------ ------------------------------
EYGLE                          B726E09FE21F8E83

执行计划
----------------------------------------------------------
Plan hash value: 3072843751

--------------------------------------------
| Id | Operation                   | Name |
--------------------------------------------
|   0 | SELECT STATEMENT            |      |
|   1 | TABLE ACCESS BY INDEX ROWID| T1   |
|   2 |   AND-EQUAL                 |      |
|* 3 |    INDEX RANGE SCAN         | IU   |
|* 4 |    INDEX RANGE SCAN         | II   |
--------------------------------------------

新的可选替代方案是NDEX_COMBINE，从9i开始，初始化参数 _b_tree_bitmap_plans设置为true，允许为B*Tree索引使用,bitmap convert:

SQL> select /*+ index_combine(t1 iu ii) */ username,password from t1 where username='EYGLE' and user_id=58;

USERNAME                       PASSWORD
------------------------------ ------------------------------
EYGLE                          B726E09FE21F8E83

执行计划
----------------------------------------------------------
Plan hash value: 1808973554

-----------------------------------------------------------------------------------------
| Id | Operation                        | Name | Rows | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |      |     1 |    47 |     2   (0)| 00:00:01 |
|   1 | TABLE ACCESS BY INDEX ROWID     | T1   |     1 |    47 |     2   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS    |      |       |       |            |          |
|   3 |    BITMAP AND                    |      |       |       |            |          |
|   4 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|* 5 |      INDEX RANGE SCAN            | II   |       |       |     1   (0)| 00:00:01 |
|   6 |     BITMAP CONVERSION FROM ROWIDS|      |       |       |            |          |
|* 7 |      INDEX RANGE SCAN            | IU   |       |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("USER_ID"=58)
   7 - access("USERNAME"='EYGLE')

and_equal, index_join, index_combine这三种都是oracle利用索引关联获得数据的方法,三者的目的都是为了最大限度的利用索引,减少回表的代价.但是三者的实现方法是有区别的,下面一一来分析.

and_equal
这种方式需要查询条件里面包括所有索引列,然后取得每个索引中得到的rowid列表,然后对这些列表做merge join,过滤出相同的rowid后再去表中获取数据或者直接从索引中获得数据.and_equal有一些限制,比如它只对单列索引有效,只对非唯一索引有效,使用到的索引不能超过5个,查询条件只能是”=”.在10g中,and_equal已经被废弃了,只能通过hint才能生效.

create table test as select * from dba_objects;

create index ind_test_owner on test(owner);

create index ind_test_object_name on test(object_name);

SQL 10G>select/*+ and_equal(test ind_test_owner ind_test_object_name)*/ owner,object_name from test where wner=’test’ and object_name=’test’;