Oracle SQL 查詢效能優化 11 大實用技巧

前言

SQL 語句寫得對不代表寫得好。掌握以下 11 項優化技巧，能顯著提升 Oracle 查詢效能，減少資源消耗。

1. WHERE 條件順序：由下而上解析

Oracle 解析 WHERE 子句採用自下而上順序。因此：

• 表連接條件寫在最前面
• 過濾性最強（能過濾最多記錄）的條件寫在最後面

2. 避免 SELECT *

`*` 會觸發資料字典查詢以展開所有欄位名稱，增加解析開銷。明確列出所需欄位。

3. 善用 DECODE 函數減少重複掃描

使用 `DECODE` 或 `CASE` 可在單次掃描中完成多條件邏輯，避免多次掃描同一表或重複連接。

4. 用 TRUNCATE 替代 DELETE (全表清空)

5. 適時 COMMIT 釋放資源

每次 `COMMIT` 釋放：

• 回滾段資訊
• 行鎖
• REDO LOG BUFFER 空間
• 內部管理開銷

長交易拆分為小批次提交可降低鎖爭用與 Undo 保留壓力。

6. 使用表別名並加前綴

多表連接時，為每個表指定簡短別名，並在所有欄位引用前加上別名（如 `e.empno`），可減少解析時間並避免欄位歧義錯誤。

7. EXISTS 替代 IN / NOT EXISTS 替代 NOT IN

低效：

SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'MELB')

高效：

SELECT * FROM emp e WHERE EXISTS (SELECT 'X' FROM dept d WHERE d.deptno = e.deptno AND d.loc = 'MELB')

原因：`NOT IN` 會對子查詢執行全表掃描並內部排序合併；`EXISTS` 只要滿足一筆即返回，且可利用索引。

8. EXISTS 替代 DISTINCT (一對多去重)

低效：

SELECT DISTINCT d.dept_no, d.dept_name FROM dept d, emp e WHERE d.dept_no = e.dept_no

高效：

SELECT d.dept_no, d.dept_name FROM dept d WHERE EXISTS (SELECT 'X' FROM emp e WHERE e.dept_no = d.dept_no)

`EXISTS` 讓 RDBMS 核心在子查詢條件滿足瞬間返回，避免 `DISTINCT` 的排序去重開銷。

9. SQL 關鍵字大寫

Oracle 解析器會將小寫轉大寫再執行。直接寫大寫 可省去轉換步驟（雖微小，但大量執行時可觀）。

10. WHERE 替代 ORDER BY 利用索引排序

`ORDER BY` 使用索引的嚴格條件：

1. 所有排序欄位必須在同一組合索引中且順序一致
2. 所有排序欄位定義為 NOT NULL

若不滿足，改用 `WHERE` 過濾配合索引範圍掃描：

-- 低效：DEPT_TYPE 可為 NULL，無法用索引
SELECT dept_code FROM dept ORDER BY dept_type;

-- 高效：利用索引範圍掃描
SELECT dept_code FROM dept WHERE dept_type > 0

11. 優化 GROUP BY：先過濾再分組

低效：

SELECT job, AVG(sal) FROM emp GROUP BY job HAVING job IN ('PRESIDENT','MANAGER')

高效：

SELECT job, AVG(sal) FROM emp WHERE job IN ('PRESIDENT','MANAGER') GROUP BY job

`WHERE` 在分組前過濾，大幅減少參與分組的行數。

總結表