Oracle索引重建到底会提高多少性能？

工作中往往会观察到索引重建带来的空间释放和应用性能提升。空间释放比较容易理解，也非常容易度量，那么索引重建到底会对应用的性能有多少影响那？首先我们会问：索引重建为什么会带来性能的提升？毫无疑问，这是因为索引重建后，与索引有关的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 90SQL> 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 85SQL> 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 processedSQL> 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 processedSQL> select * from t1 where rn=1000; RN
----------
NAME1
----------------------------------------------------------------------------------------------------
NAME2
----------------------------------------------------------------------------------------------------
1000
YTGFFEROGABGKFKQENMW
LBERYHDTRMAWGHV
已用时间: 00: 00: 00.01执行计划
----------------------------------------------------------
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"=1000）Note
-----
- dynamic sampling used for this statement （level=2）
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
-- 5 consistent gets
-- 4 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 processedSQL> alter index i1 rebuild online;索引已更改。已用时间: 00: 00: 05.41
SQL> analyze index i1 validate structure;索引已分析已用时间: 00: 00: 00.22
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 499999 0 1113 0 7998149 90已用时间: 00: 00: 00.03
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 processedSQL> 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
-- 4 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 processedSQL> select count（name1） from t1 where rn<100;COUNT（NAME1）
------------
49已用时间: 00: 00: 00.00执行计划
----------------------------------------------------------
Plan hash value: 3625400295-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2015 | 4 （0）| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 2015 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T1 | 49 | 98735 | 4 （0）| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I1 | 49 | | 3 （0）| 00:00:01 |
-------------------------------------------------------------------------------------Predicate Information （identified by operation id）:
--------------------------------------------------- 3 - access（"RN"<100）Note
-----
- dynamic sampling used for this statement （level=2）
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
-- 4 consistent gets --
-- 4 physical reads --
0 redo size
531 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更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2013-11/93063p2.htm相关阅读：由Oracle索引来理解ArcSDE索引 http://www.linuxidc.com/Linux/2012-10/72184.htmOracle索引技术之如何建立最佳索引 http://www.linuxidc.com/Linux/2012-09/70996.htmOracle索引列NULL值引发执行计划该表的测试示例 http://www.linuxidc.com/Linux/2012-09/69938.htmOracle索引主键影响查询速度 http://www.linuxidc.com/Linux/2011-12/48588.htmOracle索引扫描 http://www.linuxidc.com/Linux/2012-03/56644.htm

Oracle利用存储过程返回结果集开发报表细说ORA-08104错误相关资讯 Oracle索引 Oracle重建索引

Oracle跳跃式索引扫描测试（08月09日）
Oracle组合索引与回表（08/07/2015 18:11:53）
Oracle 索引基本原理（04/12/2015 18:03:58）

关于Oracle位图索引内部浅论（09/17/2015 19:23:59）
Oracle 索引的可见与隐藏（visible （07/18/2015 09:41:42）
Oracle索引合并coalesce操作（04/01/2015 20:21:34）

本文评论查看全部评论（0）