在《品悟性能优化》一书，4.4.3章节里介绍了复合索引的两个特点：前缀性，可选性。何为前缀性，该书阐述为排除skip scan index的情况（索引前置列的取值不多，如性别），约束条件如果不包含复合索引的第一列，则该复合索引不会被用到；何为可选性，该书阐述为字段值越多，可选性越强，定位记录越少，查询效率越高。即查询返回记录少的列应该放在复合索引的前面。
而在《收获不止oracle》一书，5.2.1.9章节里也介绍了复合索引的两个特点：①在等值查询的情况下，复合索引的列无论那一列在前，性能都一样。②复合索引的两列，当一列是范围查询，一列是等值查询的情况下，等值查询列在前，范围查询列在后，这样的索引才是高效的。
根据上述对复合索引两位作者的不同见解之处，我们通过测试，辨别下实事的真相。测试环境是11.2.0.3单实例，oracle linux 5.4
SQL> create table t as select * from dba_objects;
Table created.
SQL> select count(*) from t;
COUNT(*)
----------
109971
SQL> select count(distinct object_type) from t;
COUNT(DISTINCTOBJECT_TYPE)
--------------------------
45
SQL> select count(distinct object_id) from t;
COUNT(DISTINCTOBJECT_ID)
------------------------
109971
SQL> create index ind_t_obj_id on t(object_id,object_type);
Index created.
SQL> create index ind_t_obj_ty on t(object_type,object_id);
Index created.
SQL> select /*+ index(t,ind_t_obj_ty) */ * from T where object_id=5585 and object_type=‘TABLE‘;
Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 11 | 2277 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_TY | 2 | | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"=‘TABLE‘ AND "OBJECT_ID"=5585)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1622 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select /*+ index(t,ind_t_obj_id) */ * from T where object_id=5585 and object_type=‘TABLE‘;
Execution Plan
----------------------------------------------------------
Plan hash value: 607336433
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 2277 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 11 | 2277 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_ID | 2 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=5585 AND "OBJECT_TYPE"=‘TABLE‘)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1622 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
我们这里看到等值查询结果是不一样的，证实了《品悟》一书的可选性是正确的。
SQL> select /*+ index(t,ind_t_obj_ty) */ * from T where object_id > 20 and object_id < 2000 and object_type=‘TABLE‘;
488 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2583045626
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 912 | 184K| 49 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 912 | 184K| 49 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_TY | 912 | | 6 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"=‘TABLE‘ AND "OBJECT_ID">20 AND "OBJECT_ID"<2000)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
85 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL*Net to client
872 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed
SQL> select /*+ index(t,ind_t_obj_id) */ * from T where object_id > 20 and object_id < 2000 and object_type=‘TABLE‘;
488 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 607336433
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 912 | 184K| 11 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 912 | 184K| 11 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_OBJ_ID | 9 | | 10 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">20 AND "OBJECT_TYPE"=‘TABLE‘ AND "OBJECT_ID"<2000)
filter("OBJECT_TYPE"=‘TABLE‘)
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
87 consistent gets
0 physical reads
0 redo size
51220 bytes sent via SQL*Net to client
872 bytes received via SQL*Net from client
34 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
488 rows processed
结果，不言而喻。以上两个sql在不用hint的时候，CBO都会自动选择走IND_T_OBJ_ID。所以，《收获不止oracle》一书的结论有待商榷（如果不是我测试有问题的话）。复合索引性能问题初探,布布扣,bubuko.com
复合索引性能问题初探
原文：http://blog.csdn.net/dbaheng/article/details/24299755
