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)的效率高。

组合索引字段的位置影响查询效率，所以在建索引的时候需注意.

建议:把等值条件的字段放在组合索引的前导列,范围字段放在第二列.