什么时候需要重建索引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 valuesthen any deleted space could be a problem as this space may not be reused(making feature 3 above redundant). However, if sufficient entries aredeleted 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. Thequestion now becomes one of *when* would the equivalent amount of indexentries be reinserted from the time of the deletions, as index scans (in allit's manifestations) would be impacted during this interim period. Somonotonically increasing values *and* sparse deletions would present onecase for an index rebuild. These types of indexes can be identified ashaving predominately 90-10 splits rather than the usual 50-50 split.Another case would be an index that has deletions without subsequent insertsor inserts within an acceptable period of time. Such a case would result inwasted space that can't be effectively reused as there's not the sufficientinsert activity to reclaim the space. However, in this scenario, it's reallythe *table* itself rather than the indexes directly that should be rebuilt.Because such "shrinkage" results in both the table and associated indexesbeing fragmented with HWMs that need resetting (to prevent performanceissues with Full Table Scans and all types of Index Scans). Yes the indexneeds rebuilding but only as a result of the dependent table being rebuiltas well. ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILDalter 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 laterOffline 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 createdSQL> CREATE INDEX i_empno on T1 (empno);Index createdSQL> CREATE INDEX i_deptno on T1 (deptno);Index createdSQL> explain plan for alter index i_empno rebuild;Explainedalter index xxx rebuild使用的是INDEX FAST FULL SCAN 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 selectedalter index xxx rebuild online使用的是TABLE ACCESS FULL SQL> explain plan for alter index i_empno rebuild online;ExplainedSQL> 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 selectedSQL>

转自<http://blog.csdn.net/pan_tian/article/details/46563897>