前言
索引是資料庫效能關鍵，但許多 DBA/開發者存在誤解。本文透過實測案例 (Oracle 8.1.7 OPS on HP) 深入剖析三大核心問題。
問題一：索引並非總是最佳選擇 — 何時全表掃描更快？
CBO 選擇 FTS 而非 Index Scan 的常見原因：
- 統計資訊缺失或過舊：導致 Cardinality/Selectivity 估算錯誤。執行 `DBMS_STATS.GATHER_TABLE_STATS` 修正。
- 實際成本計算：FTS 成本 < Index Scan 成本。核心公式：
Index Cost ≈ FF * (CF + Index Blocks)
FTS Cost ≈ Table Blocks / MBRC
關鍵指標解析
- CF (Clustering Factor)：索引掃描需對應讀取的表塊數。CF 接近 Table Blocks = 資料依索引順序排列 (好)；CF 接近 Row Count = 資料亂序 (差)。大量 DML 後 CF 惡化，需 `ALTER INDEX ... REBUILD` 或 `REORG`。
- FF (Filter Factor)：查詢回傳列數佔總列數比例。CBO 依統計資訊估算。FF 高 (如 > 5-10%) 時 FTS 通常更優。
實測案例
-- 表 MYTABLES 325,917 列，PK MYID 範圍 1~409,654
-- 查詢 1：FF ≈ 100%
SELECT * FROM MYTABLES WHERE MYID BETWEEN 1 AND 409654; -- 選擇 FTS，讀 32 萬列
-- 查詢 2：FF ≈ 1%
SELECT * FROM MYTABLES WHERE MYID BETWEEN 1 AND 4096; -- 選擇 PK Index Range Scan
同一索引，FF 差異導致截然不同計畫。寫 SQL 前預估 FF，可預判索引是否會被使用。
問題二：索引也有好壞 — 單欄 vs 複合索引
誤區：索引越多越好、單欄索引足夠
- OLTP 系統單表超過 5 個索引通常降低 DML 效能 (每次 Insert/Update 需維護所有索引)。
- 單一 SQL 最多使用 5 個索引 (Index Combine/Bitmap 除外)。
複合索引優勢場景
- Index Only Access (Covering Index)：查詢欄位全包含於複合索引中，無需回表。
`INDEX (A,B,C)` 滿足 `SELECT A,B,C FROM T WHERE A=...`
- 多欄過濾結合：實測案例：
單欄索引 `I1(COID)`, `I2(ISSUEDATE)` → 查詢 `WHERE COID=... AND ISSUEDATE=...` 讀 7,000 塊。
複合索引 `I_TEST(COID, ISSUEDATE)` → 同查詢讀 300 塊。
23 倍效能差距，源於複合索引可直接定位滿足兩個條件的葉節點，避免 Index Combine 或回表過濾。
索引設計原則
- 分析應用完整 SQL 工作負載，識別高頻 `WHERE`/`JOIN`/`ORDER BY` 欄位組合。
- 將選擇性最高、最常作為等值連接/過濾的欄位置於複合索引最左邊。
- 外鍵欄位 (Join Column) 務必建立索引，避免 Full Table Scan 連接與鎖升級。
- 定期監控 `V$OBJECT_USAGE` (監控模式) 識別未使用索引並移除。
問題三：索引再好，不用也是白搭 — SQL 寫法導致索引失效
Oracle 使用索引的基本必要條件：
- WHERE 條件欄位必須是複合索引的前導欄位 (Leading Column)。
- 條件欄位不參與任何運算/函數/隱性轉換。
常見導致索引失效的寫法 (實際系統抓包)
| 失效模式 | 錯誤寫法 | 修正寫法 |
|---|
| 函數運算 | `WHERE TO_CHAR(DT,'YYYYMMDD') = '20231001'` | `WHERE DT = TO_DATE('20231001','YYYYMMDD')` 或 Function-Based Index |
| 隱性轉換 | `WHERE NUM_COL = '123'` (字串比較數字) | `WHERE NUM_COL = 123` |
| 非前導欄位 | Index (A,B,C) → `WHERE B = ...` | 調整索引順序或新增 Index (B,...) |
| 負向條件 | `!=`, `NOT IN`, `NOT EXISTS`, `IS NULL`, `IS NOT NULL` | 改寫為正向條件或使用 Bitmap Index |
| LIKE 通配符前置 | `LIKE '%ABC%'` | `LIKE 'ABC%'` (前導常數) 或 Oracle Text / Function-Based Index |
| 列間比較 | `WHERE COL1 > COL2` | 避免或使用虛擬欄位/索引 |
驗證與調優流程
- 開發階段對核心 SQL 執行 `EXPLAIN PLAN`，確認 `INDEX RANGE/UNIQUE/FAST FULL SCAN` 等關鍵字。
- 上線前在類生產環境實測 `AUTOTRACE` / `SQL_TRACE` / `DBMS_XPLAN`。
- 複雜 SQL 拆解為多步驟 (WITH 子查詢 / 暫存表 / 物化視圖)，往往能獲得更穩定優秀的計畫。
總結
- 不盲目信仰索引：理解 CF/FF 成本模型，預估選擇度。
- 精準設計索引：少而精，偏好複合索引覆蓋高頻查詢，外鍵必建索引。
- 編寫索引友善 SQL：避免前導欄位運算、負向條件、隱性轉換；善用 `EXPLAIN PLAN` 驗證。
索引調優是「統計資訊準確 + 索引設計精準 + SQL 寫法正確」三位一體的系統工程，缺一不可。
