oracle的hint探讨--add_equal,index_join,index_combine.doc

and_equal,index_join,index_combine这三种都是oracle利用索引关联获得数据的方法,三者的目的都是为了最大限度的利用索引,减少回表的代价.但是三者的实现方法是有区别的,下面一一来分析. and_equal: 这种方式需要查询条件里面包括所有索引列,然后取得每个索引中得到的rowid列表,然后对这些列表做merge join,过滤出相同的rowid后再去表中获取数据或者直接从索引中获得数据. and_equal有一些限制,比如它只对单列索引有效,只对非唯一索引有效,使用到的索引不能超过5个,查询条件只能是”=”.在10g中,and_equal已经被废弃了,只能通过hint才能生效. 涉及索引满足and_equal的限制条件的情况 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); SELECT /*+ and_equal(test ind_test_owner ind_test_object_name)*/ OWNER, OBJECT_NAME FROM TEST WHERE OWNER = TEST AND OBJECT_NAME = TEST; 由于涉及到的索引都为单列索引,且所有索引都在查询中出现,谓词也都是使用=关系符号连接,所以我们可以看到add_equal索引生效.执行计划如下所示. Execution Plan———————————————————- ——————————————————————————-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|棗棗棗棗棗棗棗棗棗棗棗棗棗-| 0 | SELECT STATEMENT | | 1 | 29 |ROM TEST WH 2 (0)||* 1 | AND-EQUAL | | | | ||* 2 | INDEX RANGE SCAN| IND_TEST_OWNER | 1 | | 1 (0)||* 3 | INDEX RANGE SCAN| IND_TEST_OBJECT_NAME | 2 | | 1 (0)|棗棗棗棗棗棗棗棗棗棗棗棗棗- 如果查询条件只包含owner SELECT /*+ and_equal(test ind_test_owner ind_test_object_name)*/ OWNER, OBJECT_NAME FROM TEST WHERE OWNER = TEST 本例中只使用到了hint中的一个索引关联的列owner,所以导致and_equal提示无效.执行计划中只使用了一个索引. Execution Plan———————————————————- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|———————————————————————————–| 0 | SELECT STATEMENT | | 1 | 29 | 2 (0)|| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 29 | 2 (0)||* 2 | INDEX RANGE SCAN | IND_TEST_OWNER | 1 | | 1 (0)|———————————————————————————– 查询条件是””的情况 SELECT /*+ and_equal(test ind_test_owner ind_test_object_name)*/ OWNER, OBJECT_NAME FROM TEST WHERE OWNER = TEST AND OBJECT_NAME TEST; 本例中由于查询谓词中对列object_name使用了符号,所以也会导致and_equal提示无效. Execution Plan———————————————————- —————————————————————棗棗棗棗?| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|棗棗棗棗棗棗棗棗棗棗棗棗棗棗棖| 0 | SELECT STATEMENT | | 1 | 40 | 2 (0)||* 1 | TALE ACCESS BY INDEX RBLE ACCESS BY INDEX ROWID| TEST |