实验环境：
zhsy_test表有20万行记录。
id选择性很好，该字段具有唯一性。a3字段选择性不好，只有3种值。
SQL语句：
select * from zhsy_test where id<1000 and a3='瘦弱';
select * from zhsy_test where a3='瘦弱' and id<1000;
index语句：
create index zh_id_ind on zhsy_test(id);
drop index zh_id_ind;
create index zh_ida3_ind on zhsy_test(id,a3);
drop index zh_ida3_ind;
create index zh_a3id_ind on zhsy_test(a3,id);
drop index zh_a3id_ind;
先将结论写在前面：
1.查询条件有多个字段时，组合索引，比只有其中一个字段的索引，具有更高的效率。
2.组合索引中，字段的顺序对执行计划有很大的影响。一般来说，应该把选择性强的字段放在前面。
3.SQL语句中WHERE后面所带条件的前后顺序，如条件中 A AND B与 B AND A，和组合索引中字段顺序没有关系。
4.当查询选择性较好的字段时，在组合索引字段不多的情况下，和单个字段索引的开销几乎相同。
5.组合索引中的索引顺序对条件字段有很大影响，如果查询条件中只有1个字段，该字段的选择性很好，但是在组合索引中，它并不位于第一位，在未对表分析之前，执行计划可能
还是会走全表扫描，对表进行了分析之后，才会走跳跃索引扫描，并且成本比顺序好的组合索引大得多。
实验过程：
1.只创建ID字段上的索引：create index zh_id_ind on zhsy_test(id);
trace内容：
已选择20行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 2641874720
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54 | 3294 | 70 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| ZHSY_TEST | 54 | 3294 | 70 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZH_ID_IND | 71 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A3"='瘦弱')
2 - access("ID"<1000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
65 consistent gets
0 physical reads
0 redo size
1389 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2.只创建组合索引，顺序为id,a3
trace内容：
已选择20行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 4248746236
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54 | 3294 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZHSY_TEST | 54 | 3294 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZH_IDA3_IND | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A3"='瘦弱' AND "ID"<1000)
filter("A3"='瘦弱')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
1519 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
3.只创建组合索引，顺序为a3,id
trace内容：
已选择20行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 2050955217
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1220 | 22 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZHSY_TEST | 20 | 1220 | 22 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZH_A3ID_IND | 20 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A3"='瘦弱' AND "ID"<1000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
1519 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
4.对调条件位置。SQL语句变成如下：
select * from zhsy_test where a3='瘦弱' and id<1000;
执行计划没有发生变化。
trace内容：
已选择20行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 2050955217
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1220 | 22 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZHSY_TEST | 20 | 1220 | 22 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZH_A3ID_IND | 20 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A3"='瘦弱' AND "ID"<1000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
25 consistent gets
0 physical reads
0 redo size
1519 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
5.重新建回条件2中的组合索引，顺序为id,a3
trace内容：
已选择20行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 4248746236
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 54 | 3294 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZHSY_TEST | 54 | 3294 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZH_IDA3_IND | 1 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A3"='瘦弱' AND "ID"<1000)
filter("A3"='瘦弱')
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
24 consistent gets
0 physical reads
0 redo size
1519 bytes sent via SQL*Net to client
396 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
6.只查询ID字段，只见ID字段上索引。 select * from zhsy_test where id<1000;
trace内容：
已选择61行。
已用时间: 00: 00: 00.28
执行计划
----------------------------------------------------------
Plan hash value: 2641874720
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61 | 3721 | 60 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZHSY_TEST | 61 | 3721 | 60 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZH_ID_IND | 61 | | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<1000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
68 consistent gets
0 physical reads
0 redo size
3452 bytes sent via SQL*Net to client
429 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
61 rows processed
7.只有联合索引，只查询ID字段。
执行计划的开销与只有ID字段索引的开销相同。
trace内容：
已选择61行。
已用时间: 00: 00: 00.20
执行计划
----------------------------------------------------------
Plan hash value: 4248746236
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 61 | 3721 | 60 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZHSY_TEST | 61 | 3721 | 60 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | ZH_IDA3_IND | 61 | | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<1000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
68 consistent gets
0 physical reads
0 redo size
3452 bytes sent via SQL*Net to client
429 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
61 rows processed
8.只建立联合索引，索引顺序为a3,id，查询条件只有id
尽管ID字段的选择性很好，但是仍旧走了全表扫描，没有用到索引。
已选择61行。
trace内容：
已用时间: 00: 00: 00.23
执行计划
----------------------------------------------------------
Plan hash value: 3670929120
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71 | 4331 | 251 (2)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| ZHSY_TEST | 71 | 4331 | 251 (2)| 00:00:04 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"<1000)
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1136 consistent gets
0 physical reads
0 redo size
3263 bytes sent via SQL*Net to client
429 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
61 rows processed
对表进行了分析之后，才走了跳跃索引扫描，并且成本还是比顺序要id,a3的组合索引大得多。
已选择61行。
已用时间: 00: 00: 00.21
执行计划
----------------------------------------------------------
Plan hash value: 1085764453
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 48 | 1536 | 100 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID| ZHSY_TEST | 48 | 1536 | 100 (0)| 00:00:02 |
|* 2 | INDEX SKIP SCAN | ZH_A3ID_IND | 49 | | 51 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"<1000)
filter("ID"<1000)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
82 consistent gets
0 physical reads
0 redo size
3452 bytes sent via SQL*Net to client
429 bytes received via SQL*Net from client
6 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
61 rows processed
