重建索引indexrebuildonlinevsofflinevsindexcoalescevsindexshrikspace
重建索引：ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD:
http://blog.csdn.net/pan_tian/article/details/46563897
深入理解重建索引(原创):
http://czmmiao.iteye.com/blog/1481957
alter index coalesce和alter index rebuild的区别:
http://blog.csdn.net/techchan/article/details/6693275
Alter index coalesce VS shrink space：
http://www.askmaclean.com/archives/alter-index-coalesce-vs-shrink-space.html
什么时候需要重建索引
1、 删除的空间没有重用，导致 索引出现碎片
2、 删除大量的表数据后，空间没有重用，导致 索引"虚高"
3、索引的 clustering_facto 和表不一致
也有人认为当索引树高度超过4的时候需要进行重建，但是如果表数量级较大，自然就不会有较高的树，而且重建不会改变索引树高度，除非是由于大量引起的索引树“虚高”，重建才会改善性能，当然这又回到了索引碎片的问题上了。
关于索引是否需要重建，Oracle有这么一句话:
Generally speaking, the need to rebuild b-tree indexes is very rare, basically because a b-tree index is largely self-managed or self-balanced.
另外找到了一篇《When should one perform a rebuild?》分析的比较好的文章
Firstly, if the index value were to have monotonically increasing values
then any deleted space could be a problem as this space may not be reused
(making feature 3 above redundant). However, if sufficient entries are
deleted resulting in index nodes being fully emptied (say via a bulk delete)
then feature 4 would kick in and the deleted space could be reused. The
question now becomes one of *when* would the equivalent amount of index
entries be reinserted from the time of the deletions, as index scans (in all
it's manifestations) would be impacted during this interim period. So
monotonically increasing values *and* sparse deletions would present one
case for an index rebuild. These types of indexes can be identified as
having predominately 90-10 splits rather than the usual 50-50 split.
Another case would be an index that has deletions without subsequent inserts
or inserts within an acceptable period of time. Such a case would result in
wasted space that can't be effectively reused as there's not the sufficient
insert activity to reclaim the space. However, in this scenario, it's really
the *table* itself rather than the indexes directly that should be rebuilt.
Because such "shrinkage" results in both the table and associated indexes
being fragmented with HWMs that need resetting (to prevent performance
issues with Full Table Scans and all types of Index Scans). Yes the index
needs rebuilding but only as a result of the dependent table being rebuilt
as well.
ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD
alter index rebuild online实质上是扫描表而不是扫描现有的索引块来实现索引的重建.
alter index rebuild 只扫描现有的索引块来实现索引的重建。
rebuild index online在执行期间不会阻塞DML操作，但在开始和结束阶段，需要请求模式为4的TM锁。因此，如果在rebuild index online开始前或结束时，有其它长时间的事物在运行，很有可能就造成大量的锁等待。也就是说在执行前仍会产生阻塞, 应该避免排他锁.
而rebuild index在执行期间会阻塞DML操作, 但速度较快.
Online Index Rebuild Features:
+ ALTER INDEX REBUILD ONLINE;
+ DMLs are allowed on the base table
+ It is comparatively Slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later
Offline Index Rebuild Features:
+ ALTER INDEX REBUILD; (Default)
+ Does not refer the base table and the base table is exclusively locked
+ New index is created from the old index
+ No DML and DDL possible on the base table
+ Comparatively faster
两者重建索引时的扫描方式不同，rebuild用的是“INDEX FAST FULL SCAN”，rebuild online用的是“TABLE ACCESS FULL”; 即rebuild index是扫描索引块,而rebuild index online是扫描全表的数据块.
实验一：
SQL> create table t1 as select * From emp;
Table created.
SQL> CREATE INDEX i_empno on T1 (empno);
Index created.
SQL> CREATE INDEX i_deptno on T1 (deptno);
Index created.
--offline重建索引，查看执行计划
SQL> explain plan for alter index i_empno rebuild;
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1909342220
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 327 | 4251 | 3 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| I_EMPNO | | | | |
| 2 | SORT CREATE INDEX | | 327 | 4251 | | |
| 3 | INDEX FAST FULL SCAN| I_EMPNO | | | | |
----------------------------------------------------------------------------------
10 rows selected.
--online重建索引，查看执行计划
SQL> explain plan for alter index i_empno rebuild online;
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------
Plan hash value: 1499455000
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 327 | 4251 | 3 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| I_EMPNO | | | | |
| 2 | SORT CREATE INDEX | | 327 | 4251 | | |
| 3 | TABLE ACCESS FULL | T1 | 327 | 4251 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
10 rows selected.
结论：alter index rebuild online实质上是扫描表而不是扫描现有的索引块来实现索引的重建，速度慢.
alter index rebuild 只扫描现有的索引块来实现索引的重建，速度快。
实验二：
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:01.03
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:04.13
--分析索引
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.41
SQL> set linesize 200;
SQL> set linesize 200;
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 5154 36979767 7996 9 5153 61784 8028 41283636 37041551 90
Elapsed: 00:00:00.34
/* 可以看到IND_YOUYUS索引的基本结构，在初始状态下其block总数为5376，其中页块共5154 */
--删除三分之一数据：
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:10.31
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
--再次查询redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36422640
redo size 1471998664
Elapsed: 00:00:00.05
--使用coalesce字句合并索引：
SQL> alter index ind_youyus coalesce;
Index altered.
Elapsed: 00:00:03.72
--再次查询redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36426180
redo size 1542936592
经过前后对比coalesce 操作产生了大约70MB的redo数据。
Elapsed: 00:00:00.00
--再次分析索引结构；
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.17
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.02
/* 可以看到执行coalesce(合并)操作后页块数量下降到3439，而branch枝块和root根块的结构是不会变化的，同时coalesc命令并不释放索引上的多余空间，但索引结构实际占用的空间BTREE_SPACE下降到了27570496 bytes*/
/* 清理测试现场 */
SQL> drop table YOUYUS;
Table dropped.
Elapsed: 00:00:01.42
SQL>
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:01.04
SQL>
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:03.68
--再次删除数据：1/3
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:14.31
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
--查询目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36445880
redo size 1711003916
Elapsed: 00:00:00.01
--使用shrink space子句回收索引：
SQL> alter index ind_youyus shrink space;
Index altered.
Elapsed: 00:00:05.30
--再次查询目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36452200
redo size 1802409928
Elapsed: 00:00:00.01
前后比对，redo生成量为90MB左右，多出coalesce时的28%左右。。。。。
--再次分析索引：
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.17
SQL>
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 3520 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.01
/* 索引结构与coalesce命令维护后相同，但shrink space操作释放了索引上的空闲空间 */
/* 清理测试现场 */
SQL> drop table YOUYUS;
Table dropped.
Elapsed: 00:00:00.51
SQL>
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:00.84
SQL>
SQL>
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:03.60
--删除数据：
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:14.61
SQL>
SQL> commit;
Commit complete.
Elapsed: 00:00:00.07
--查询目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36468913
redo size 1970476820
Elapsed: 00:00:00.01
--使用shrink space compact子句回收索引：
SQL> alter index ind_youyus shrink space compact;
Index altered.
Elapsed: 00:00:04.95
--再次查询目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36474731
redo size 2061844832
Elapsed: 00:00:00.00
前后比对发现，redo生成量为90mb左右，与shrink space子句相同
--再次分析索引：
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.16
SQL>
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.01
/* shrink space compact 起到了和coalesce完全相同的作用，但其产生的redo仍要多于coalesce于28%，与shrink space相同 */；
总结：
coalesce与shrink space命令对比重建索引(rebuild index)有一个显著的优点:不会导致索引降级。从以上测试可以看到coalesce与shrink space compact功能完全相同；在OLTP环境中，大多数情况下我们并不希望回收索引上的空闲空间，那么coalesce或者shrink space compact(not shrink space)可以成为我们很好的选择，虽然实际操作过程中2者消耗的资源有不少差别。并不是说coalesce就一定会消耗更少的资源，这需要在您的实际环境中具体测试，合适的才是最好的！
分享标题：重建索引indexrebuildonlinevsofflinevsindexcoalescevsindexshrikspace
文章路径：http://azwzsj.com/article/gjsoch.html
http://blog.csdn.net/pan_tian/article/details/46563897
深入理解重建索引(原创):
http://czmmiao.iteye.com/blog/1481957
alter index coalesce和alter index rebuild的区别:
http://blog.csdn.net/techchan/article/details/6693275
Alter index coalesce VS shrink space：
http://www.askmaclean.com/archives/alter-index-coalesce-vs-shrink-space.html
什么时候需要重建索引
1、 删除的空间没有重用，导致 索引出现碎片
2、 删除大量的表数据后，空间没有重用，导致 索引"虚高"
3、索引的 clustering_facto 和表不一致
也有人认为当索引树高度超过4的时候需要进行重建，但是如果表数量级较大，自然就不会有较高的树，而且重建不会改变索引树高度，除非是由于大量引起的索引树“虚高”，重建才会改善性能，当然这又回到了索引碎片的问题上了。
关于索引是否需要重建，Oracle有这么一句话:
Generally speaking, the need to rebuild b-tree indexes is very rare, basically because a b-tree index is largely self-managed or self-balanced.
另外找到了一篇《When should one perform a rebuild?》分析的比较好的文章
Firstly, if the index value were to have monotonically increasing values
then any deleted space could be a problem as this space may not be reused
(making feature 3 above redundant). However, if sufficient entries are
deleted resulting in index nodes being fully emptied (say via a bulk delete)
then feature 4 would kick in and the deleted space could be reused. The
question now becomes one of *when* would the equivalent amount of index
entries be reinserted from the time of the deletions, as index scans (in all
it's manifestations) would be impacted during this interim period. So
monotonically increasing values *and* sparse deletions would present one
case for an index rebuild. These types of indexes can be identified as
having predominately 90-10 splits rather than the usual 50-50 split.
Another case would be an index that has deletions without subsequent inserts
or inserts within an acceptable period of time. Such a case would result in
wasted space that can't be effectively reused as there's not the sufficient
insert activity to reclaim the space. However, in this scenario, it's really
the *table* itself rather than the indexes directly that should be rebuilt.
Because such "shrinkage" results in both the table and associated indexes
being fragmented with HWMs that need resetting (to prevent performance
issues with Full Table Scans and all types of Index Scans). Yes the index
needs rebuilding but only as a result of the dependent table being rebuilt
as well.
ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD
alter index rebuild online实质上是扫描表而不是扫描现有的索引块来实现索引的重建.
alter index rebuild 只扫描现有的索引块来实现索引的重建。
rebuild index online在执行期间不会阻塞DML操作，但在开始和结束阶段，需要请求模式为4的TM锁。因此，如果在rebuild index online开始前或结束时，有其它长时间的事物在运行，很有可能就造成大量的锁等待。也就是说在执行前仍会产生阻塞, 应该避免排他锁.
而rebuild index在执行期间会阻塞DML操作, 但速度较快.
Online Index Rebuild Features:
+ ALTER INDEX REBUILD ONLINE;
+ DMLs are allowed on the base table
+ It is comparatively Slow
+ Base table is referred for the new index
+ Base table is locked in shared mode and DDLs are not possible
+ Intermediate table stores the data changes in the base table, during the index rebuild to update the new index later
Offline Index Rebuild Features:
+ ALTER INDEX REBUILD; (Default)
+ Does not refer the base table and the base table is exclusively locked
+ New index is created from the old index
+ No DML and DDL possible on the base table
+ Comparatively faster
两者重建索引时的扫描方式不同，rebuild用的是“INDEX FAST FULL SCAN”，rebuild online用的是“TABLE ACCESS FULL”; 即rebuild index是扫描索引块,而rebuild index online是扫描全表的数据块.
实验一：
SQL> create table t1 as select * From emp;
Table created.
SQL> CREATE INDEX i_empno on T1 (empno);
Index created.
SQL> CREATE INDEX i_deptno on T1 (deptno);
Index created.
--offline重建索引，查看执行计划
SQL> explain plan for alter index i_empno rebuild;
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------
Plan hash value: 1909342220
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 327 | 4251 | 3 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| I_EMPNO | | | | |
| 2 | SORT CREATE INDEX | | 327 | 4251 | | |
| 3 | INDEX FAST FULL SCAN| I_EMPNO | | | | |
----------------------------------------------------------------------------------
10 rows selected.
--online重建索引，查看执行计划
SQL> explain plan for alter index i_empno rebuild online;
Explained.
SQL> select * from table (dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------
Plan hash value: 1499455000
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | ALTER INDEX STATEMENT | | 327 | 4251 | 3 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| I_EMPNO | | | | |
| 2 | SORT CREATE INDEX | | 327 | 4251 | | |
| 3 | TABLE ACCESS FULL | T1 | 327 | 4251 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
10 rows selected.
结论：alter index rebuild online实质上是扫描表而不是扫描现有的索引块来实现索引的重建，速度慢.
alter index rebuild 只扫描现有的索引块来实现索引的重建，速度快。
实验二：
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:01.03
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:04.13
--分析索引
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.41
SQL> set linesize 200;
SQL> set linesize 200;
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 5154 36979767 7996 9 5153 61784 8028 41283636 37041551 90
Elapsed: 00:00:00.34
/* 可以看到IND_YOUYUS索引的基本结构，在初始状态下其block总数为5376，其中页块共5154 */
--删除三分之一数据：
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:10.31
SQL> commit;
Commit complete.
Elapsed: 00:00:00.02
--再次查询redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36422640
redo size 1471998664
Elapsed: 00:00:00.05
--使用coalesce字句合并索引：
SQL> alter index ind_youyus coalesce;
Index altered.
Elapsed: 00:00:03.72
--再次查询redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36426180
redo size 1542936592
经过前后对比coalesce 操作产生了大约70MB的redo数据。
Elapsed: 00:00:00.00
--再次分析索引结构；
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.17
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.02
/* 可以看到执行coalesce(合并)操作后页块数量下降到3439，而branch枝块和root根块的结构是不会变化的，同时coalesc命令并不释放索引上的多余空间，但索引结构实际占用的空间BTREE_SPACE下降到了27570496 bytes*/
/* 清理测试现场 */
SQL> drop table YOUYUS;
Table dropped.
Elapsed: 00:00:01.42
SQL>
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:01.04
SQL>
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:03.68
--再次删除数据：1/3
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:14.31
SQL> commit;
Commit complete.
Elapsed: 00:00:00.01
--查询目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36445880
redo size 1711003916
Elapsed: 00:00:00.01
--使用shrink space子句回收索引：
SQL> alter index ind_youyus shrink space;
Index altered.
Elapsed: 00:00:05.30
--再次查询目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36452200
redo size 1802409928
Elapsed: 00:00:00.01
前后比对，redo生成量为90MB左右，多出coalesce时的28%左右。。。。。
--再次分析索引：
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.17
SQL>
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 3520 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.01
/* 索引结构与coalesce命令维护后相同，但shrink space操作释放了索引上的空闲空间 */
/* 清理测试现场 */
SQL> drop table YOUYUS;
Table dropped.
Elapsed: 00:00:00.51
SQL>
SQL> create table YOUYUS as select rownum t1,rpad('A',20,'B') t2 from dual connect by level<=999999;
Table created.
Elapsed: 00:00:00.84
SQL>
SQL>
SQL> create index ind_youyus on youyus(t1,t2) nologging;
Index created.
Elapsed: 00:00:03.60
--删除数据：
SQL> delete YOUYUS where mod(t1,3)=1;
333333 rows deleted.
Elapsed: 00:00:14.61
SQL>
SQL> commit;
Commit complete.
Elapsed: 00:00:00.07
--查询目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36468913
redo size 1970476820
Elapsed: 00:00:00.01
--使用shrink space compact子句回收索引：
SQL> alter index ind_youyus shrink space compact;
Index altered.
Elapsed: 00:00:04.95
--再次查询目前redo生成量：
SQL> select vs.name, ms.value
2 from v$mystat ms, v$sysstat vs
3 where vs.statistic# = ms.statistic#
4 and vs.name in ('redo size','consistent gets');
NAME VALUE
---------------------------------------------------------------- ----------
consistent gets 36474731
redo size 2061844832
Elapsed: 00:00:00.00
前后比对发现，redo生成量为90mb左右，与shrink space子句相同
--再次分析索引：
SQL> analyze index IND_YOUYUS validate structure;
Index analyzed.
Elapsed: 00:00:00.16
SQL>
SQL> select height,
2 blocks,
3 lf_blks,
4 lf_rows_len,
5 lf_blk_len,
6 br_blks,
7 br_rows,
8 br_rows_len,
9 br_blk_len,
10 btree_space,
11 used_space,
12 pct_used
13 from index_stats;
HEIGHT BLOCKS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_BLKS BR_ROWS BR_ROWS_LEN BR_BLK_LEN BTREE_SPACE USED_SPACE PCT_USED
---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- ---------- ----------
3 5376 3439 24653178 7996 9 3438 41188 8028 27570496 24694366 90
Elapsed: 00:00:00.01
/* shrink space compact 起到了和coalesce完全相同的作用，但其产生的redo仍要多于coalesce于28%，与shrink space相同 */；
总结：
coalesce与shrink space命令对比重建索引(rebuild index)有一个显著的优点:不会导致索引降级。从以上测试可以看到coalesce与shrink space compact功能完全相同；在OLTP环境中，大多数情况下我们并不希望回收索引上的空闲空间，那么coalesce或者shrink space compact(not shrink space)可以成为我们很好的选择，虽然实际操作过程中2者消耗的资源有不少差别。并不是说coalesce就一定会消耗更少的资源，这需要在您的实际环境中具体测试，合适的才是最好的！
分享标题：重建索引indexrebuildonlinevsofflinevsindexcoalescevsindexshrikspace
文章路径：http://azwzsj.com/article/gjsoch.html
