什么时候需要重建索引

索引在普遍意义上能够给数据库带来带来提升，但索引的额外开销也是不容小视的，而索引的重建也是维护索引的重要工作之一。 经过维护的索引可带来以下好处：
1、CBO对于索引的使用可能会产生一个较小的成本值，从而在执行计划中选择使用索引。
2、使用索引扫描的查询扫描的物理索引块会减少，从而提高效率。
3、于需要缓存的索引块减少了，从而让出了内存以供其他组件使用。
重建索引的原因主要包括：
1、 删除的空间没有重用，导致 索引出现碎片
2、 删除大量的表数据后，空间没有重用，导致 索引"虚高"
3、索引的 clustering_facto 和表不一致
也有人认为当索引树高度超过4的时候需要进行重建，但是如果表数量级较大，自然就不会有较高的树，而且重建不会改变索引树高度，除非是由于大量引起的索引树“虚高”，重建才会改善性能，当然这又回到了索引碎片的问题上了。
索引出现碎片

由于索引中只有删除和插入操作，且索引中更新完全不同于表达更新。如果索引中的记录关键字需要更新，就需要将旧记录的位置标记为删除，并在相应的叶子节点插入新的索引纪录。这种删除标记并非真正的删除索引块中的记录，索引块中被标记为删除的记录只有在相同索引条目插入到相同块的相同位置时才能重用。由于即使相同的索引记录也不一定插入到被删除的空间中，故如果对索引频繁进行update和delete操作很容易导致索引出现碎片。较高的PCTFREE也容易出现索引碎片。索引的碎片也就导致了，访问索引数据时需要访问更多的索引块
索引虚高
上面的说的是频繁update和delete导致索引块中有碎片，那如果进行大量的delete操作把整个索引块的数据都删了呢？索引中的索引条目仍然只被标记为删除而没有被真正清空。设想下，如果这时候的索引关键字是一个不断增大的id，那么被标记为删除的索引条目就永远不会被重用，那树就不会不断增长，也就出现了，表的数据空间减少了，而索引的数据空间却在不断增大的情况。由于索引的高度不断增加，访问索引数据时需要访问更多的索引块。
clustering_factor对 B树索引 的影响
对于clustering_factor来说，它是用来比较索引的顺序程度与表的杂乱排序程度的一个度量。Oracle在计算某个clustering_factor时，会对每个索引键值查找对应到表的数据，在查找的过程中，会跟踪从一个表的数据块跳转到另外一个数据块的次数（当然，它不可能真的这么做，源代码里只是简单的扫描索引，从而获得ROWID，然后从这些ROWID获得表的数据块的地址）。每一次跳转时，有个计数器就会增加，最终该计数器的值就是clustering_factor。下图描述了这个原理。
?????????

在上图中，我们有一个表，该表有4个数据块，以及20条记录。在列N1上有一个索引，上图中的每个小黑点就表示一个索引条目。列N1的值如图所示。而N1的索引的叶子节点包含的值为：A、B、C、D、E、F。如果oracle开始扫描索引的底部，叶子节点包含的第一个N1值为A，那么根据该值可以知道对应的ROWID位于第一个数据块的第三行里，所以我们的计数器增加1。同时，A值还对应第二个数据块的第四行，由于跳转到了不同的数据块上，所以计数器再加1。同样的，在处理B时，可以知道对应第一个数据块的第二行，由于我们从第二个数据块跳转到了第一个数据块，所以计数器再加1。同时，B值还对应了第一个数据块的第五行，由于我们这里没有发生跳转，所以计数器不用加1。
在上面的图里，在表的每一行的下面都放了一个数字，它用来显示计数器跳转到该行时对应的值。当我们处理完索引的最后一个值时，我们在数据块上一共跳转了十次，所以该索引的clustering_factor为10。
注意第二个数据块，clustering_factor为8出现了4次。因为在索引里N1为E所对应的4个索引条目都指向了同一个数据块。从而使得clustering_factor不再增长。同样的现象出现在第三个数据块中，它包含三条记录，它们的值都是C，对应的clustering_factor都是6。
从clustering_factor的计算方法上可以看出，我们可以知道它的最小值就等于表所含有的数据块的数量；而最大值就是表所含有的记录的总行数。很明显，clustering_factor越小越好，越小说明通过索引查找表里的数据行时需要访问的表的数据块越少。
所以我们可以得出结论，如果仅仅是为了降低索引的clustering_factor而重建索引没有任何意义。降低clustering_factor的关键在于重建表里的数据。事实上，生产环境下，我们甚至没有必要考虑 clustering_factor对索引访问的影响，这个是表数据分布决定的，如果想考虑，就得先创建索引，然后分析 clustering_factor，最后对表进行排序，再重新创建索引，可行性非常低。因此，这里只是作为研究讨论，实际环境下还是要结合具体情况进行分析。针对索引碎片和索引的"虚高"，如果查询范围主要是通过unique index访问数据，可以不用理会 索引碎片和索引的"虚高"，如果数据范围，主要是通过range scan的方式则需要重建索引，至于原理，相信读了笔者下面的文章后肯定会明白
http://czmmiao.iteye.com/blog/1481227
。关于索引是否需要重建，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.

如何查找出需要重建的索引
我们通过下面实验来具体看下如何查找需要重建的索引
准备实验环境如下
SQL> crea