索引碎片整理--重建索引、合并索引、shrink索引

隨著時間的推移，索引也可能會產生碎片，oracle在處理該問題的時候提供給予我們有三種方式：重建索引、合并索引、shrink 索引。每種都有自己的特點，今天我在此學習一下記錄一下筆記。
第一：重建索引：
  重建索引其實語句很簡單實用alter index index_name rebuild;參數即可對指定的索引進行重建，但是注意在重建索引的時候 會對相應的對象加鎖，因此重建的時候一定要注意，如何避免在重建索引的時候不影響其他業務使用呢？那么可以指定online 參數，如：alter index index_name rebuild online;指定該參數之后就不會對其他業務訪問對象產生任何影響。另外有時候我們還可以指定并行創建索引，但要注意在指定parallel（degree interger）參數的時候，那么并行度將存儲于索引中，隨著在基于硬件如cpu個數創建速度上確實提高了，但在在執行查詢的時候將使用并行方式，有時候也會伴隨著等待事件的出現如：PX Deq Credit: send blkd，因此創建索引是不是應該使用paralle應該斟酌一下。
eg：

  另外當我們需要重新創建反向鍵索引的時候需要指定reserver參數：alter index index_name rebuild reverse;回收未使用的空間，當使用
alter index index_name deallocate unused;
命令的時候，會將沒有使用的空閑段返回給數據，但是曾經使用過的空塊將不會返還給數據庫空間（包含之前刪除的索引或是移動條目導致段內沒有使用的空間）對于分區索引和索引組織表的信息查看：
http://blog.csdn.net/rhys_oracle/article/details/18671897
http://blog.csdn.net/rhys_oracle/article/details/18409063
  另外如何確定是否需要重建索引呢？一般認為有兩種情況：
  1、索引深度大于等于4
  2、已刪除的索引條目占總索引條目的20%
  3、索引空間使用率小于50%
再次不得不提 一個視圖index_stats該視圖默認是沒有任何數據的，當使用analyze index index_name validate structure;對索引結構分析之后將會填充相應的數據，一般該視圖可以提供給我們足夠的信息去引導我們是否需要對索引進行重建。
查看相關字段信息：

查看未刪除葉子行數占總行數的百分比公式為：(（lf_rows-del_lf_rows)/lf_rows)*100;
查看未刪除行占用的空間百分比公式為：（（used_space-del_lf_rows_len)/btree_space)*100;
pct_used計算公式為：（used_space/btree_space)*100
eg：

收集統計信息，之后可以看到在dba_indexes中依然顯示存在的索引葉塊，優化器從而使用該索引。

但是注意：使用analyze index index_name validate structure ;進行索引分析的時候會鎖定相應的對象直到該命令執行完成，如果不加鎖可以使用online參數，但使用online參數數據信息又不會記錄到index_stats視圖，且在重建索引的過程中會產生很多的redo日志，可以考慮使用nologging參數，另外當在分析完成后在執行插入操作，那么相應的del_lf_rows將會改變從而影響對索引的分析信息提取：
eg：

從以上可以看出兩點內容，產生索引數據之后剩余的空間不會返還給數據庫，但是當插入新數據的時候將有可能重新利用之前被刪除數據的空間，另外一點可以看del_lf_row已經評估出現錯誤，到目前為止剛剛開始刪除9999條數據，然后插入一條數據在進行分析，那么現在既然是9582，因此不能僅僅依靠del_lf_rows進行索引重建評估。以前記得有個朋友曾經提過這么一個問題，說是測試環境庫執行一條sql會非常的塊，但是導到正式環境卻很慢，但是執行計劃都是一樣的，我的懷疑就是需要重建正式環境庫的索引。因此，如果確定對 索引相同部分執行了大量刪除操作，產生了大量的索引碎片，并且查詢每次讀取了大量的索引行，索引被頻繁使用，這時候重建索引是有價值的。
第二種：合并索引
  合并索引就是將索引段中相鄰的索引塊其中空閑空間進行整合重組，從而釋放索引塊空間，這比較類似于我們windows的磁盤碎片整理，但是注意該過程不會將騰出的空間返回與數據庫，而是加入到空閑空間列表中，以便下次在進行使用。這種操作對于那種以序列或是時間日志為字段的表是有非常重要價值的，因為當我們對這些表刪除了大部分數據，那么其中很多空間是無法在進行使用的，那么在我們制定謂詞查詢的時候通常會掃描索引中很多空快，那么合并索引就將空的索引塊進行釋放與索引塊的空閑列表中。
語句非常簡單：
alter index index_name coalesce;
合并索引與重建索引不同事，合并索引不會降低索引的高度，而是對其數據條目進行重組整合，但是重建可能會降低索引高度，另外重建索引需要2倍的磁盤空間，首先需要存儲原先的索引條目數據，還需要額外的空間存儲新調整 的索引數據直到重建完成才可。

注：合并索引是一種在線操作。
第三種：shrink 索引：
 因為shrink是一個耗資源相對嚴重的過程，因此兩個過程，一個是compact參數，另一個是直接shrink space，第一種類似于coalesce但是相比會產生更多的redo日志，執行完后不會釋放空間，但是shrink space 除了整理碎片還可以將空間釋放給表空間，但是shrink space雖然是在線可以做的，依然會產生過打的redo日志。除此之外shrink space還要啟動行移動。
eg：
alter index index_name shrink space compact;
alter index index_name shrink space;
注：Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management (ASSM).



select name,
         blocks,
         del_lf_rows_len,
         lf_rows_len,
         (del_lf_rows_len / lf_rows_len) * 100,
         (DEL_LF_ROWS / LF_ROWS) * 100
    from index_stats;
  
  NAME          BLOCKS     DEL_LF_ROWS_LEN      LF_ROWS_LEN      (DEL_LF_ROWS_LEN / LF_ROWS_LEN) * 100          (DEL_LF_ROWS / LF_ROWS) * 100
  ------------  ---------  -------------------  ---------------  ----------- ---------------------------------   -------------------------
  IND_OBJ_ID    384        766085               1906952          40.1732713                                      40.2394062
  
  索引碎片比率：(del_lf_rows_len / lf_rows_len) * 100，如果百分比超過20%就說明索引碎片比率很高了。需要整理碎片。