Oracle索引组合字段的位置不同，当查询条件不能覆盖索引时，影响查询效率。查询条件是不是索引字段的第一列影响执行计划，实验验证
实验1：查询条件为组合索引的第一列
--创建测试表 create table test_C as select * from user_objects where 1=2; --创建测试表 create table test_a as select * from user_objects where 1=1; --创建组合索引 create index test_a_1 on test_a(object_id,object_type); set linesize 1000; set autotrace trace; select * from test_a where object_id=161728; **************************************************** 执行计划 ---------------------------------------------------------- Plan hash value: 3036018411 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 116 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_A | 1 | 116 | 3 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | TEST_A_1 | 1 | | 2 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=161728) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 1933 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
查询条件object_id为索引的前导列。执行计划：INDEX RANGE SCAN，cost是3，逻辑读是3.
drop index test_a_1; drop index test_a_2; --创建索引2，查询条件在索引中的位置是第二列 create index test_a_2 on test_a(object_name,object_ID); --再次查询 select * from test_a where object_id=161728; **************************************************** 执行计划 ---------------------------------------------------------- Plan hash value: 219338936 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 116 | 7 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST_A | 1 | 116 | 7 (0)| 00:00:01 | |* 2 | INDEX SKIP SCAN | TEST_A_2 | 1 | | 6 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("OBJECT_ID"=161728) filter("OBJECT_ID"=161728) 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 8 consistent gets 0 physical reads 0 redo size 1933 bytes sent via SQL*Net to client 500 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
查询条件object_id为索引的第二列。执行计划：INDEX SKIP SCAN，cost是7，逻辑读是8.
同样object_id作为查询条件，当object_id作为索引的第一列或者作为索引的第二列，它们在cost与逻辑读上是有区别的，也就是索引跳跃扫描(INDEX SKIP SCAN)没有索引范围扫描(INDEX RANGE SCAN)的效率高。
组合索引字段的位置影响查询效率，所以在建索引的时候需注意.
建议:把等值条件的字段放在组合索引的前导列,范围字段放在第二列.