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就一定會消耗更少的資源，這需要在您的實際環境中具體測試，合適的才是最好的！
