Oracle 索引碎片檢測與重建實戰：從 ANALYZE 到 ONLINE REBUILD 最佳實務

索引碎片過多會導致掃描效率下降、執行計畫偏差。本文記錄完整實戰流程：確認表空間佈局、依統計指標篩選需重建索引、撰寫批次分析腳本處理鎖衝突、最終評估重建必要性與排程策略。

一、前置確認：表空間與索引分佈

• 索引獨立放置於專用 Tablespace（如 INDEX_TS），與資料表 Tablespace 分離
• 所有索引 Owner 統一（如 TAX），便於批次管理

二、碎片判斷指標（ANALYZE INDEX VALIDATE STRUCTURE）

分析結果寫入 INDEX_STATS 視圖，核心判斷條件：

• HEIGHT > 4：B-tree 層級過深
• PCT_USED < 50%：空間利用率低
• DEL_LF_ROWS / LF_ROWS > 3%：刪除標記行比例過高

三、批次分析腳本與鎖衝突處理

DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  FOR idx IN (SELECT INDEX_NAME FROM DBA_INDEXES WHERE OWNER='TAX' AND TABLESPACE_NAME='INDEX_TS') LOOP
    BEGIN
      -- 需獨佔鎖，生產環境易失敗
      EXECUTE IMMEDIATE 'ANALYZE INDEX ' || idx.INDEX_NAME || ' VALIDATE STRUCTURE';
      -- 將 INDEX_STATS 寫入自建統計表
      INSERT INTO IDX_ANALYSIS_LOG SELECT INDEX_NAME, HEIGHT, LF_ROWS, DEL_LF_ROWS, PCT_USED, SYSDATE FROM INDEX_STATS;
      COMMIT;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('FAILED: ' || idx.INDEX_NAME || ' - ' || SQLERRM);
        -- 記錄失敗索引供人工處理
        INSERT INTO IDX_ANALYSIS_FAILED VALUES (idx.INDEX_NAME, SYSDATE, SQLERRM);
        COMMIT;
    END;
  END LOOP;
END;
/

教訓：夜間低峰執行、捕獲例外避免腳本中斷、失敗索引人工補分析。

四、實際案例評估結果

• 522 個索引中 160 個符合碎片判斷條件
• 但符合條件者皆為小索引，百萬 Leaf 級大索引反而未達標
• 結論：無需立即 REBUILD，碎片對效能無顯著影響

五、何時執行 REBUILD？最佳策略

1. 對象： 大量 DML 操作的大型索引（Leaf Blocks > 100 萬）
2. 頻率： 每月或每季一次
3. 語法： ALTER INDEX idx_name REBUILD ONLINE PARALLEL 8;
   • ONLINE：允許 DML 並發（仍會產生 I/O 爭用）
   • PARALLEL 8：利用 8 CPU 並行加速
4. 排程： 分 3-5 個夜間時段批次執行，避免單時段 I/O 風暴
5. 替代方案： COALESCE（無需額外空間、不阻塞 DML，適合輕度碎片）

六、監控腳本：定期檢測碎片趨勢

SELECT INDEX_NAME, HEIGHT, LF_ROWS, DEL_LF_ROWS, PCT_USED,
       ROUND(DEL_LF_ROWS/LF_ROWS*100,2) AS DEL_PCT
FROM IDX_ANALYSIS_LOG
WHERE ANALYSIS_DATE = (SELECT MAX(ANALYSIS_DATE) FROM IDX_ANALYSIS_LOG)
  AND (HEIGHT > 4 OR PCT_USED < 50 OR DEL_LF_ROWS/LF_ROWS > 0.03)
ORDER BY DEL_PCT DESC;

總結：「有碎片不一定要重建，要重建要挑對時機與對象。」建立常態化監控機制，比盲目定期重建更專業。