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

參考資料：

<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=529590097581429&amp;id=1525787.1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=mztcpsxax_149">https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=529590097581429&amp;id=1525787.1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=mztcpsxax_149</a>

<a href="https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=529957019657481&amp;id=1525787.1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=mztcpsxax_198">https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=529957019657481&amp;id=1525787.1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=mztcpsxax_198</a>

<a href="http://www.it-matters.be/doc/index-internals-rebuilding-the-truth.pdf">http://www.it-matters.be/doc/index-internals-rebuilding-the-truth.pdf</a>

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=531414583432488&amp;id=186826.1&amp;displayIndex=1&amp;_afrWindowMode=0&amp;_adf.ctrl-state=mztcpsxax_247