参照博客某社区600万用户数据导入MYSQL、MSSQL、Oracle数据库方法中“ 3.3为数据表添加年龄列（2012-6-13）”提到的方法创建测试数据库。
1.无索引状态
1.1统计 select count(*)
然后执行query1，统计年龄在20到30之间的人数。
set autotrace on select count(*) from csdnuser3 where age between 20 and 30;
查询与统计结果如下
View Code
COUNT(*) ---------- 1429651 执行计划 ---------------------------------------------------------- Plan hash value: 3799347318 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 13112 (1)| 00:02:38 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 21M| 13112 (1)| 00:02:38 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("AGE">=20 AND "AGE"<=30) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 5 recursive calls 0 db block gets 47795 consistent gets 47702 physical reads 0 redo size 422 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
再次执行query1，查询与统计结果如下
View Code
COUNT(*) ---------- 1429651 执行计划 ---------------------------------------------------------- Plan hash value: 3799347318 -------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 13112 (1)| 00:02:38 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 21M| 13112 (1)| 00:02:38 | -------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("AGE">=20 AND "AGE"<=30) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 47707 consistent gets 47702 physical reads 0 redo size 422 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
对照上述两次查询，我们可以发现：
- 两次physical reads=47702 没有改变，
- 第一次consistent gets=47795，第二次 consistent gets=47707，略微改变。
1.2查询 select *
我们查询所有年龄在20到30之间的人员，执行如下查询语句query2
autotrace traceonly select * from csdnuser3 where age between 20 and 30;
查询统计结果如下：
View Code
已选择1429651行。 执行计划 ---------------------------------------------------------- Plan hash value: 3659233856 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1717K| 681M| 13112 (1)| 00:02:38 | |* 1 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 681M| 13112 (1)| 00:02:38 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("AGE">=20 AND "AGE"<=30) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 198 recursive calls 0 db block gets 142441 consistent gets 0 physical reads 0 redo size 83189634 bytes sent via SQL*Net to client 1048826 bytes received via SQL*Net from client 95312 SQL*Net roundtrips to/from client 6 sorts (memory) 0 sorts (disk) 1429651 rows processed
在执行一次query2
查询统计结果如下：
View Code
已选择1429651行。 执行计划 ---------------------------------------------------------- Plan hash value: 3659233856 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1717K| 681M| 13112 (1)| 00:02:38 | |* 1 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 681M| 13112 (1)| 00:02:38 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("AGE">=20 AND "AGE"<=30) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 142322 consistent gets 0 physical reads 0 redo size 83189634 bytes sent via SQL*Net to client 1048826 bytes received via SQL*Net from client 95312 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1429651 rows processed
对比上述两次查询，我们可以发现：
因为查询的数据已经在缓存中，所以physical reads=0
consistent gets相对于第一次的142441，第二次为142322，略微有些想将。
2.为age字段添加索引
在age字段上添加索引
--创建索引 create index IX_CSDNUSER3_AGE on CSDNUSER3(AGE);
2.1统计 select count(*)
执行query1以后，查询分析结果如下：
View Code
COUNT(*) ---------- 1429651 执行计划 ---------------------------------------------------------- Plan hash value: 1842112716 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2912 (1)| 00:00:35 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX RANGE SCAN| IX_CSDNUSER3_AGE | 1717K| 21M| 2912 (1)| 00:00:35 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("AGE">=20 AND "AGE"<=30) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 9 recursive calls 0 db block gets 2887 consistent gets 2789 physical reads 0 redo size 422 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
再执行一次query1，查询分析结果如下：
View Code
COUNT(*) ---------- 1429651 执行计划 ---------------------------------------------------------- Plan hash value: 1842112716 -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 13 | 2912 (1)| 00:00:35 | | 1 | SORT AGGREGATE | | 1 | 13 | | | |* 2 | INDEX RANGE SCAN| IX_CSDNUSER3_AGE | 1717K| 21M| 2912 (1)| 00:00:35 | -------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("AGE">=20 AND "AGE"<=30) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 2790 consistent gets 0 physical reads 0 redo size 422 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
对比上述两次查询可以发现：
- consistent gets略微想将，最后在2790趋于稳定
- physical reads因为第一次的执行而产生缓存，所以第二次physical reads=0
- 相对于1.1中稳定的consistent gets=47707，在age字段上加了索引以后，现在的consistent gets=2790，查询效率明显挺高。这表明在age字段上加索引对于select count(*)类型的查询，可以提升查询效率。
2.2查询 select *
执行query2以后，查询分析结果如下：
View Code
已选择1429651行。 执行计划 ---------------------------------------------------------- Plan hash value: 3659233856 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1717K| 681M| 13112 (1)| 00:02:38 | |* 1 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 681M| 13112 (1)| 00:02:38 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("AGE">=20 AND "AGE"<=30) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 7 recursive calls 0 db block gets 142419 consistent gets 0 physical reads 0 redo size 83189634 bytes sent via SQL*Net to client 1048826 bytes received via SQL*Net from client 95312 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1429651 rows processed
再次执行query2，查询分析结果如下：
View Code
已选择1429651行。 执行计划 ---------------------------------------------------------- Plan hash value: 3659233856 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1717K| 681M| 13112 (1)| 00:02:38 | |* 1 | TABLE ACCESS FULL| CSDNUSER3 | 1717K| 681M| 13112 (1)| 00:02:38 | ------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("AGE">=20 AND "AGE"<=30) Note ----- - dynamic sampling used for this statement 统计信息 ---------------------------------------------------------- 0 recursive calls 0 db block gets 142322 consistent gets 0 physical reads 0 redo size 83189634 bytes sent via SQL*Net to client 1048826 bytes received via SQL*Net from client 95312 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1429651 rows processed
对比上述两次查询，我们可以发现：
- rows processed= 1429651 刚好就是查询结果一共的条目数
- consistent gets 两次有略微想将，最后在142322趋于稳定。当时相对于1.1中select count(*)类型的查询，此处 consistent gets更大。
- 相对于1.2中无索引的select *，我们发现consistent gets都是142322，表明在age字段上加索引，对于select *类型的查询，没有提高查询效率。
3.主键索引
为什么要在主键上加索引，这是因为表连接的时候效率提高。主键是cluster索引，而其他为非cluster索引。