重建索引：ALTER INDEX..REBUILD ONLINE vs ALTER INDEX..REBUILD

什么时候需要重建索引

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