Oracle B-tree 索引維護實務：存儲檢查、選擇度評估與統計收集

一、防範 SYSTEM 表空間被用戶索引佔用

SYSTEM 表空間僅應存放資料字典。若發現非 SYS/SYSTEM 用戶的索引落在 SYSTEM，會嚴重增加維護風險（一旦損壞需重建資料庫）。

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

二、索引儲存結構檢查：Extent 與 Segment 分析

Oracle 儲存層級：Block → Extent → Segment。索引對應一個 Segment，透過查詢 Extent 分布可掌握碎片狀況。

1. 查看各索引的 Extent 數量

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

2. 查看表空間內索引的 Extent 大小分布

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 效率越高。

• Cost-based Optimizer (CBO) 會自動拒絕低選擇度索引。
• Rule-based Optimizer (RBO) 不考慮選擇度（唯一索引除外），需手動 Hint 避開。

1. 手工測量（建立索引前評估）

-- 兩欄複合索引選擇度
SELECT COUNT(DISTINCT col1 || '%' || col2) / COUNT(*) 
FROM table_name;

2. 自動測量（建立後收集統計）

ANALYZE TABLE table_name COMPUTE STATISTICS;
SELECT distinct_keys FROM user_indexes WHERE index_name = 'IDX_NAME';

實務建議：採用 DBMS_STATS 套件取代 ANALYZE，支援並行、直方圖等進階統計。