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;
--再次查询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
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;
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;
/* 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就一定会消耗更少的资源，这需要在您的实际环境中具体测试，合适的才是最好的！
