議題一：Index 不一定是最佳選擇
Optimizer 選擇 FTS 的兩大原因
- 統計資訊缺失/過舊： 無法正確估算 Selectivity
- 實際成本計算： FTS Cost < Index Access Cost
關鍵成本因子：Clustering Factor (CF) 與 Filter Factor (FF)
- CF： 索引順序與表資料順序的一致性。CF 接近 Blocks 數 = 好；接近 Rows 數 = 差 (需大量隨機 I/O)
- FF： 查詢回傳列數佔總列數比例
- Index Cost ≈ FF × (CF + Index Blocks)
實測案例
表 MYTABLES 32 萬列，PK MYID (1~409654)
-- FF ≈ 100% → FTS
SELECT * FROM mytables WHERE myid > 0;
-- FF ≈ 1% → Index Range Scan
SELECT * FROM mytables WHERE myid > 400000;
同樣索引，FF 差異導致完全不同計畫。
CF 惡化與維護
- 大量 DML 導致表資料物理順序與索引邏輯順序脫節
- 定期
ALTER INDEX ... REBUILD 或 ALTER TABLE ... MOVE 重組
- 監控
DBA_INDEXES.CLUSTERING_FACTOR
議題二：Index 有好壞之分
誤區：索引越多越好
- OLTP 表建議 ≤ 5 個索引，超過反而拖慢 DML
- 單一 SQL 最多使用 5 個索引 (Bitmap 合併除外)
單欄 vs 組合索引實測
查詢：WHERE coid = :1 AND issuedate = :2
| 索引策略 | 讀取 Blocks | 說明 |
|---|
| 兩單欄索引 (COID, ISSUEDATE) | 7,000 | Index Merge 或單一掃描 + Table Access |
| 組合索引 (COID, ISSUEDATE) | 300 | Index Range Scan 直接定位，可能 Index Only Access |
結論： 查詢欄位皆在組合索引中 → Index Only Access，效能碾壓多單欄索引。
外鍵欄位必建索引
避免父表刪除/更新時全表掃描子表檢查外鍵，並加速 Join。
議題三：Index 再好，寫錯也白搭
Optimizer 無法使用索引的常見寫法
- 非前導欄位： 組合索引
(F1,F2,F3)，條件 WHERE F2 = ... → 不用索引
- 索引欄位參與運算/函數：
WHERE col + 1 = 10WHERE TRUNC(date_col) = SYSDATEWHERE UPPER(name) = 'SMITH'WHERE col || '_suffix' = 'val'
解法： Function-Based Index 或改寫條件 (如 date_col >= TRUNC(SYSDATE) AND date_col < TRUNC(SYSDATE)+1)
- 隱含型別轉換：
WHERE num_col = '123' (字串比數值) → 隱含 TO_NUMBER(num_col) 導致索引失效
- 否定條件：
IS NULL (除非建立含 NULL 的索引)、!=、NOT IN、NOT EXISTS、LIKE '%pattern%' (前導萬用字元)
- OR 條件跨欄位：
WHERE f1 = :1 OR f2 = :2 (除非有 Index Combine)
驗證手段：EXPLAIN PLAN
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
檢查 OPERATION 是否為 INDEX RANGE SCAN/UNIQUE SCAN/FULL SCAN。
總結檢核表
- 統計資訊是否最新？(
DBMS_STATS.GATHER_TABLE_STATS)
- CF 是否接近 Blocks？否則考慮 Rebuild
- 高頻查詢是否有組合索引覆蓋？
- WHERE 條件是否避開函數/運算/隱含轉換？
- 定期
EXPLAIN PLAN 驗證關鍵 SQL 計畫
- 上一篇文章： Oracle 兩表交集查詢：INTERSECT 與 IN 子查詢效能比較
- 下一篇文章： Oracle 8i SQL*Plus Worksheet 亂碼解決：修改 dbappscfg.properties 指定 NLS_LANG
Copyright ©
程式碼之家 All Rights Reserved
