Oracle 索引三大核心議題：選擇、品質與失效避坑

前言

索引是影響資料庫效能的關鍵物件。雖然文獻眾多，實務上仍存許多誤解。本文針對三大常見議題，以 Oracle 8.1.7 實測數據深度剖析。

議題一：索引並非總是最佳選擇

優化器若選擇全表掃描而非索引掃描，通常因兩大原因：

1. 統計資訊缺失或過舊導致誤判
2. 依據資料筆數與區塊數，全表掃描確實更快

關鍵指標：CF (Clustering Factor) 與 FF (Filtering Factor)

• CF：每讀一個索引區塊，平均需讀幾個資料區塊。CF 越大代表索引與表資料對應越亂，需重建索引改善。
• FF：查詢結果集佔總資料比例。Oracle 依統計資訊估算。

成本估算公式約為：FF * (CF + 索引區塊數)。讀取區塊越多，Cost 越大，越傾向全表掃描。

實測對比

表 mytables 32 萬筆，主鍵 myid 範圍 1~409654。

• SELECT * FROM mytables WHERE myid >= 1 → FF=100% → 全表掃描，Cost=3132，讀 41473 區塊
• SELECT * FROM mytables WHERE myid >= 400000 → FF≈1% → 索引範圍掃描，Cost=346，讀 6647 區塊

FF 差異導致截然不同的執行計畫。撰寫 SQL 前預估 FF 可預判索引是否會被使用。

議題二：索引也有好壞——單欄 vs 複合索引

常見誤區：

1. 索引非越多越好。OLTP 系統單表超過 5 個索引反而降低效能；單一 SQL 最多使用 5 個索引。
2. 複合索引常優於多個單欄索引。
3. 連結欄位加索引效益顯著。

何時複合索引更優？

查詢欄位全包含於複合索引時，Oracle 僅掃描索引即可取得所有資料，稱為 Index Only Access Path，極快。

實測：單欄 vs 複合索引

SELECT COUNT(1) FROM mytabs
WHERE coid >= 130000 AND issuedate >= TO_DATE('2001-07-20','yyyy-mm-dd');

• 兩單欄索引 I_mytabs1(coid), I_mytabs2(issuedate)：讀 7000 區塊，Cost=384
• 複合索引 I_mytabs_test(coid, issuedate)：讀 300 區塊，Cost=3

7000 vs 300 區塊，複合索引效能碾壓。索引設計需結合應用整體分析。

議題三：索引再好，寫法錯誤照樣失效

Oracle 使用索引的基本條件：

1. WHERE 條件欄位必須為複合索引的第一欄位
2. 條件欄位不可參與任何運算（函數、運算式、隱含轉型）

常見導致索引失效的寫法

-- 錯誤：欄位參與函數運算
WHERE TO_DATE(updatedate) >= TO_DATE('2001-7-18','YYYY-MM-DD')

-- 顯式阻止索引的操作符
f1 > f2, f1 IS NULL, f1 IS NOT NULL, f1 NOT IN, f1 !=, f1 LIKE '%pattern%', NOT EXISTS
-- 某些情況下 IN 也會失效

對策

• 避免上述寫法；IN 可嘗試改寫為 比較運算 + UNION ALL
• 複雜 SQL 上線前必做 EXPLAIN PLAN 驗證執行計畫
• 拆解過度複雜的 SQL，常能獲得更好優化