Oracle 索引維護全攻略：從碎片檢測到重建優化

一、檢查 SYSTEM 表空間中的非系統索引

Oracle 資料庫安裝時會自動建立 SYSTEM 表空間，它包含完整的資料字典、預存程序、套件、函數、觸發器定義以及系統回滾段。為避免管理混亂與潛在風障，強烈建議不要在 SYSTEM 表空間存放非 SYS/SYSTEM 用戶的物件。一旦 SYSTEM 表空間損壞，往往需要重建整個資料庫。

使用以下查詢快速檢測是否有違規索引：

SELECT COUNT(*) 
FROM dba_indexes 
WHERE tablespace_name = 'SYSTEM' 
  AND owner NOT IN ('SYS','SYSTEM');

若回傳值大於 0，請立即規劃移動這些索引至適當的使用者表空間。

二、索引儲存結構與 Extent 分析

Oracle 以資料區塊、區段、段三層邏輯結構管理儲存空間。區塊大小由 DB_BLOCK_SIZE 決定，建庫後不可變更。Extent 由連續區塊組成，Segment 則由一或多個 Extent 構成，可跨資料檔案分佈。

透過查詢 DBA_EXTENTS 可掌握索引段的儲存分佈：

1. 統計各索引段的 Extent 數量

SELECT segment_name, COUNT(*) 
FROM dba_extents 
WHERE segment_type = 'INDEX' 
  AND owner = UPPER('&owner') 
GROUP BY segment_name;

2. 檢視表空間內索引的擴展情況

SELECT SUBSTR(segment_name,1,20) "SEGMENT NAME",
       bytes, COUNT(bytes)
FROM dba_extents 
WHERE segment_name IN (
    SELECT index_name FROM dba_indexes 
    WHERE tablespace_name = UPPER('&表空間')
) 
GROUP BY segment_name, bytes 
ORDER BY segment_name;

三、索引選擇性評估與優化

索引選擇性 = 不同值數量 / 表總行數。值越接近 1，索引效率越高。CBO（成本優化器）會自動拒絕低選擇性索引，RBO（規則優化器）則需人工干預。

手工測量（建立索引前預估）

SELECT COUNT(DISTINCT col1 || '%' || col2) / COUNT(*) 
FROM table_name;

自動測量（建立索引後分析）

1. ANALYZE TABLE table_name COMPUTE STATISTICS;
2. 查詢 USER_INDEXES.DISTINCT_KEYS 獲取不同鍵值數
3. 查詢 USER_TABLES.NUM_ROWS 獲取總行數
4. 計算：SELECT i.distinct_keys/t.num_rows FROM user_indexes i, user_tables t WHERE ...
5. 參考 USER_TAB_COLUMNS.NUM_DISTINCT 了解單欄選擇性

注意：USER_TAB_COLUMNS 無法精確測量複合欄位選擇性，複合索引仍需手工測量或建立後分析。

四、索引碎片檢測與處理

頻繁的 DML 操作會導致索引葉子行被刪除，產生碎片，增加 I/O 成本。碎片超 10% 時建議重建。

1. 驗證索引：VALIDATE INDEX username.index_name; 結果寫入 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. 重建索引（碎片 > 10%）：

   ALTER INDEX username.index_name REBUILD 
   TABLESPACE ts_name STORAGE(INITIAL 初始值 NEXT 擴展值) NOLOGGING;

4. 整理索引（無法重建時）：ALTER INDEX username.index_name COALESCE;
5. 清除統計資訊：ANALYZE INDEX username.index_name DELETE STATISTICS;

五、索引重建最佳實踐

定期排程檢查高頻 DML 表的索引碎片，維護窗口期執行 REBUILD 或 COALESCE，並配合 NOLOGGING 減少 Redo 產生。重建後記執行 ANALYZE INDEX ... COMPUTE STATISTICS 更新統計資訊，確保優化器擁有最新資訊。