ORACLE关于索引是否需要定期重建争论的整理

     ORACLE数据库中的索引到底要不要定期重建呢？ 如果不需要定期重建，那么理由是什么？ 如果需要定期重建，那么理由又是什么？另外，如果需要定期重建，那么满足那些条件的索引才需要重建呢？关于这个问题，网上也有很多争论，也一直让我有点困惑，因为总有点不得庐山真面目的感觉，直到上周看到了一些资料，遂整理于此，方便以后翻阅：

首先来看看网上关于索引需要重建的准则或标准：

   一：分析(analyze)指定索引之后，查询index_stats的height字段的值，如果这个值>=4 ,最好重建（rebuild）这个索引。虽然这个规则不是总是正确，但如果这个值一直都是不变的，则这个索引也就不需重建。

　　二：在分析(analyze)指定索引之后，查询index_stats的del_lf_rows和lf_rows的值,如果(del_lf_rows/lf_rows)*100 > = 20，则这个索引也需要重建。

关于这个论据，我们找到可以考证的官方资料为Various Aspects of Fragmentation (文档 ID 186826.1) ，其中有这么一段

Along with the REBUILD clause of the ALTER INDEX, Oracle8i introduces the

COALESCE clause as another way to address fragmentation issues.

In the following cases, it may be worthwhile to rebuild the index:

--> the percentage of the space used is bad - lower than 66%: PCT_USED

--> deleted leaf blocks represent more than 20% of total leaf blocks: DEL_LF_ROWS

--> the height of the tree is bigger than 3: HEIGHT or BLEVEL

另外网上还有一些关于重建索引的理由或说法，大概有这么一下（有可能不全），如下所示：

    1、Oracle的B树索引随着时间的推移变得不平衡(错误的认识)

    2、索引碎片在不断增加

    3、索引不断增加，删除的空间没有重复使用（错误的认识）

    4、索引 clustering factor （集群因子）不同步，可以通过重建修复(错误的认识)

但是这个资料非常古老了，是Oracle 8i， Oracle 9i时代的资料，当然对于索引深度超过4级以及已删除的索引条目至少占有现有索引条目总数的20%需要重建索引的准则，这种说法显然也没有过时，但是关于PCT_USED低于66%的就建议重建索引，这种说法对现在主流版本有点不合时宜。关于这些，其实ORACLE官方文档有关于索引重建的必要性和影响的讨论， 官方文档为索引重建的必要性与影响 (文档 ID 1525787.1)

---------------------------------------------------------------------------------------------------------------------

适用于:

Oracle Database - Enterprise Edition - 版本 8.1.7.0 和更高版本

本文档所含信息适用于所有平台

用途

本文章将重点概述重建索引的各种影响。我们通常会定期重建索引，但事实上，判断索引重建是否有用一般并不以统计数字为基础，而且很少保留索引的重建历史记录。

适用范围

本文章的目标受众是数据库管理员。

详细信息

关于重建索引有用与否的讨论有很多。一般而言，极少需要重建 B 树索引，基本原因是 B 树索引很大程度上可以自我管理或自我平衡。

认为需要重建索引的最常见理由有：

- 索引碎片在不断增加

- 索引不断增加，删除的空间没有重复使用

- 索引 clustering factor （群集因子）不同步

事实上，大多数索引都能保持平衡和完整，因为空闲的叶条目可以重复使用。插入/更新和删除操作确实会导致索引块周围的可用空间形成碎片，但是一般来说这些碎片都会被正确的重用。

Clustering factor 群集因子可以反映给定的索引键值所对应的表中的数据排序情况。重建索引不会对群集因子产生影响，要改变集群因子只能通过重组表的数据。

另外，重建索引的影响非常明显，请仔细阅读以下说明：

1. 大多数脚本都依赖 index_stats 动态表。此表使用以下命令填充：

analyze index ... validate structure;

尽管这是一种有效的索引检查方法，但是它在分析索引时会获取独占表锁。特别对于大型索引，它的影响会是巨大的，因为在此期间不允许对表执行 DML 操作。虽然该方法可以在不锁表的情况下在线运行，但是可能要消耗额外的时间。

2. 重建索引的直接结果是 REDO 活动可能会增加，总体的系统性能可能会受到影响。

插入/更新/删除操作会导致索引随着索引的分割和增长不断发展。重建索引后，它将连接的更为紧凑；但是，随着对表不断执行 DML 操作，必须再次分割索引，直到索引达到平衡为止。结果，重做活动增加，且索引分割更有可能对性能产生直接影响，因为我们需要将更多的 I/O、CPU 等用于索引重建。经过一段时间后，索引可能会再次遇到“问题”，因此可能会再被标记为重建，从而陷入恶性循环。因此，通常最好是让索引处于自然平衡和（或）至少要防止定期重建索引。

3. 通常是优先考虑index coalesce（索引合并），而不是重建索引。索引合并有如下优点：

- 不需要占用近磁盘存储空间 2 倍的空间

- 可以在线操作

- 无需重建索引结构，而是尽快地合并索引叶块，这样可避免系统开销过大，请见第 2 点中的解释。

注意：例如，如要将索引转移到其他表空间，则需要重建索引。

综上所述，强烈建议不要定期重建索引，而应使用合适的诊断工具。请参阅以下文章，其中列出了可用于分析索引结构的脚本。它不使用“analyze index validate structure” 命令，但将基于当前表和索引统计信息来估计索引大小。

Note 989186.1- Script to investigate a b-tree index structure

看了上面官方文档，想必你已经对索引是否需要定期重建有了一个认识,但是，你是否总觉得这是一个结论性的说法，总觉得不够深入，例如Oracle索引为什么会自我平衡，自我管理呢？为什么那些被删除的索引空间会被重复使用？ 为什么大部分B树索引不需要重建呢? 等等诸如此类的疑问，那么下面这哥们这篇文章（index-internals-rebuilding-the-truth）关于这方面的分析、介绍，绝对是我见过最详细、最深入介绍索引内部的知识。如果链接查看不了,请从附件下载index-internals-rebuilding-the-truth.pdf 。剩下的就是反复通读、深入理解、体会了！

参考资料：

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=529590097581429&id=1525787.1&_afrWindowMode=0&_adf.ctrl-state=mztcpsxax_149
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=529957019657481&id=1525787.1&_afrWindowMode=0&_adf.ctrl-state=mztcpsxax_198
http://www.it-matters.be/doc/index-internals-rebuilding-the-truth.pdf
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=531414583432488&id=186826.1&displayIndex=1&_afrWindowMode=0&_adf.ctrl-state=mztcpsxax_247