工作中往往会观察到索引重建带来的空间释放和应用性能提升。空间释放比较容易理解，也非常容易度量，那么索引重建到底会对应用的性能有多少影响那？首先我们会问：索引重建为什么会带来性能的提升？毫无疑问，这是因为索引重建后，与索引有关的io操作得到了降低。那么，索引io的降低在多大程度上影响了应用语句的执行效率？这恐怕需要具体问题具体分析了。
首先，我们来看一下多数情况下，索引重建的效果如何
SQL> create table t1 as select rownum rn,dbms_random.string(‘u’,20) name1,dbms_random.string(‘u’,15) name2 from dual connect by level < 1000000;
表已创建。
SQL> create index i1 on t1(rn);
索引已创建。
SQL> analyze index i1 validate structure;
索引已分析
SQL> select height,lf_rows,del_lf_rows,lf_blks,del_lf_rows btree_space,used_space,pct_used from index_stats;
HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BTREE_SPACE USED_SPACE PCT_USED
———- ———- ———– ———- ———– ———- ———-
3 999999 0 2226 0 16006445 90
SQL> delete from t1 where mod(rn,2) =1;
已删除500000行。
SQL> commit;
提交完成。
SQL> analyze index i1 validate structure;
索引已分析
SQL> select height,lf_rows,del_lf_rows,lf_blks,del_lf_rows btree_space,used_space,pct_used from index_stats;
HEIGHT LF_ROWS DEL_LF_ROWS LF_BLKS BTREE_SPACE USED_SPACE PCT_USED
———- ———- ———– ———- ———– ———- ———-
3 943027 443028 2226 443028 15094893 85
SQL> set timing on
SQL> set autotrace on
SQL> select * from t1 where rn=1;
未选定行
已用时间: 00: 00: 00.00
执行计划
———————————————————-
Plan hash value: 1704772559
————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 4017 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 4017 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1 | 1 | | 2 (0)| 00:00:01 |
————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“RN”=1)
Note
—–
– dynamic sampling used for this statement (level=2)
统计信息
———————————————————-
0 recursive calls
0 db block gets
— 3 consistent gets
— 3 physical reads
0 redo size
465 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> select * from t1 where rn=100;
RN
———-
NAME1
—————————————————————————————————-
NAME2
—————————————————————————————————-
100
IWKRROMDHLNJMXVQYRHE
VPTNTMMUJYJJQCM
已用时间: 00: 00: 00.00
执行计划
———————————————————-
Plan hash value: 1704772559
————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 4017 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 4017 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I1 | 1 | | 3 (0)| 00:00:01 |
————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(“RN”=100)
Note
—–
– dynamic sampling used for this statement (level=2)
统计信息
———————————————————-
0 recursive calls
0 db block gets
— 5 consistent gets
— 1 physical reads
0 redo size
696 bytes sent via SQL*Net to client
519 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 * from t1 where rn=1000;