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
