Oracle SQL 效能調優 20 條黃金法則

從寫法到執行計畫的系統化 SQL 優化清單

SQL 效能優化是開發與 DBA 共同責任。本文彙整 20 條實戰驗證的優化原則，涵蓋 WHERE 條件排序、連接順序、子查詢改寫、索引使用陷阱、提示應用等核心領域，助你寫出高效率 SQL。

20 條黃金法則

1. 避免 WHERE 子查詢：改用 JOIN 或 WITH 子查詢 (CTE)，減少重複剖析。
2. 日期查詢勿用函數轉換：

   -- 差：全表掃描
   WHERE TO_CHAR(dt, 'YYYY-MM-DD') = '2024-01-15'
   -- 優：索引範圍掃描
   WHERE dt >= DATE '2024-01-15' AND dt < DATE '2024-01-16'

3. 過濾力強的條件放 WHERE 後面：Oracle 由下往上評估 WHERE 條件 (規則基礎優化器) 或依統計資訊決定 (CBO)。
4. FROM 子句基礎表選擇：結果集最小的表放最後 (驅動表)，或為交叉表 (被多表參考)。
5. 使用綁定變數：WHERE id = :b1 而非硬編碼，共用游標、減少硬解析。
6. WHERE 盡量不用 OR：改用 IN 或 UNION ALL，避免全表掃描。
7. EXISTS 替代 IN / NOT EXISTS 替代 NOT IN：尤其子查詢回傳大量資料時，EXISTS 可短路優化。
8. 索引欄位避免運算：

   -- 差：索引失效
   WHERE sal * 12 > 25000
   -- 優
   WHERE sal > 25000 / 12

9. IN 替代多個 OR：

   WHERE loc_id IN (10, 15, 20)

10. 索引欄位避免 IS NULL/IS NOT NULL：B-tree 索引不儲存全 NULL 鍵值。
11. 複合索引必須用到導欄：INDEX (a,b,c) 需 WHERE 條件含 a 才會被使用。
12. UNION ALL 替代 UNION：無需排序去重，效能差異巨大。
13. 避免隱式型別轉換導致索引失效：

    -- 差：字串比較數字欄位，觸發 TO_CHAR(empno)
    WHERE empno = '123'
    -- 優
    WHERE empno = 123

14. != / <> 不走索引：改寫為範圍查詢或 NOT IN (需注意 NULL)。
15. 優化 GROUP BY：僅選擇必要欄位，避免 SELECT *；大量資料考慮 HASH GROUP BY 提示。
16. LIKE 通配符前置不走索引：

    LIKE 'ABC%'  -- 走索引
    LIKE '%ABC'  -- 全表掃描

17. 避免複雜正則表達式：LIKE '98___' 不走索引，改用 zipcode > '98000' AND zipcode < '99000'。
18. 明確列出 SELECT 欄位：禁用 SELECT *，減少網路傳輸、解析開銷、快取失效風險。
19. 表別名前綴所有欄位：SELECT e.ename FROM emp e，避免歧義、加快解析。
20. 遵循資料庫習慣組織 SQL：大寫關鍵字、縮排對齊、註釋關鍵邏輯，利於維護與計畫穩定。

進階驗證工具

• EXPLAIN PLAN FOR ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
• AUTOTRACE (SQL*Plus/Developer 內建)
• SQL_TRACE + TKPROF 深度分析
• V$SQL_PLAN / DBA_HIST_SQL_PLAN 歷史計畫比對

優化心法

「先測量、再優化、再驗證」。無執行計畫與統計資訊的優化都是盲目嘗試。

內化這 20 條法則，配合系統化測試流程，SQL 效能問題將不再是難題。