Oracle索引維護進階：選擇性計算、碎片檢測與重建策略

核心指標：索引選擇性精確測量

索引選擇性 = 不重複鍵值數 / 表總行數，是評估索引效能的黃金指標。本文提供三種互補的測量方法，滿足不同業務場景需求。

方法一：聯合索引選擇性查詢（推薦）

SELECT i.distinct_keys / t.num_rows
FROM user_indexes i, user_tables t
WHERE i.table_name = '表名'
AND i.index_name = '索引名'
AND i.table_name = t.table_name;

方法二：單欄位選擇性快速掃描

SELECT column_name, num_distinct
FROM user_tab_columns
WHERE table_name = '表名';

單欄選擇性 = num_distinct / 表總行數。注意：此法無法精確測量多欄組合選擇性，需輔以手工測量或建立複合索引後分析。

方法三：手工測量複合欄位選擇性

SELECT COUNT(DISTINCT col_a || '%' || col_b) / COUNT(*)
FROM table_name;

索引碎片檢測與處理標準流程

頻繁DML操作導致索引葉子行刪除產生碎片，增加I/O成本。建立標準化檢測重建機制至關重要。

步驟1：驗證索引填充統計資訊

VALIDATE INDEX 用戶名.索引名;

此命令將統計資訊寫入INDEX_STATS表（單一資料列，每次驗證覆蓋）。

步驟2：計算碎片比率

SELECT name,
       del_lf_rows,
       lf_rows,
       ROUND((del_lf_rows / (lf_rows + 0.0000000001)) * 100) "Frag Percent"
FROM index_stats;

步驟3：依閾值執行對應動作

步驟4：清理分析統計資訊

ANALYZE INDEX 用戶名.索引名 DELETE STATISTICS;

自動化維護建議

1. 建立排程作業：每週執行碎片掃描，生成重建腳本
2. 重建操作選擇業務低峰期，並設定NOLOGGING減少Redo生成
3. 關鍵業務表建議採用ONLINE重建避免鎖定影響
4. 重建後必須重新收集統計資訊：DBMS_STATS.GATHER_INDEX_STATS

總結

建立「監測-評估-執行-驗證」閉環機制，結合選擇性分析與碎片管理，可使索引長期維持最佳存取效能。實務證據顯示，規範化索引維護可提升核心查詢效能30%-50%以上。