Oracle SQL 調優 14 大黃金法則：從執行計畫到寫碼習慣全方位優化

法則 1：FROM 子句表順序——由小至大（RBO 時代遺產）

Oracle 解析器舊版本按從右到左處理 FROM 表，最後寫的表（驅動表）優先讀取。現代 CBO 已根據統計資訊自動決定連接順序，無需人為調整表順序。但理解此機制有助於閱讀舊代碼。

-- 範例數據：TAB1 16,384 行，TAB2 1 行
SELECT COUNT(*) FROM tab1, tab2;  -- 0.96 秒（TAB2 驅動）
SELECT COUNT(*) FROM tab2, tab1;  -- 26.09 秒（TAB1 驅動，舊版行為）

法則 2：WHERE 條件順序——過濾率高的後置（RBO 時代）

舊版自下而上解析 WHERE，連接條件優先，高過濾條件放最後。CBO 忽略書寫順序，依統計資訊計算選擇率。

-- 低效寫法（舊版）
WHERE sal > 50000 AND job = 'MANAGER' AND 25 < (SELECT COUNT(*)...);

-- 高效寫法（舊版）
WHERE 25 < (SELECT COUNT(*)...) AND sal > 50000 AND job = 'MANAGER';

現代建議：專注收集統計資訊、建立合適索引、使用 Hint，而非調整條件順序。

法則 3：避免 SELECT *

* 需查詢資料字典解析為具體欄位，增加解析開銷，且傳輸不必要數據。永遠顯式列出欄位。

法則 4：善用 DECODE/CASE 減少重複掃描

單次掃描完成多條件分支統計，避免多次全表掃描或自連接。

SELECT COUNT(DECODE(deptno,10,1)) dept10_cnt,
       COUNT(DECODE(deptno,20,1)) dept20_cnt
FROM emp;  -- 一次掃描完成多部門統計

法則 5：高效刪除重複資料（ROWID 定位）

DELETE FROM emp e
WHERE e.ROWID > (SELECT MIN(x.ROWID) FROM emp x WHERE x.emp_no = e.emp_no);

保留每組最小 ROWID，其餘刪除。大量刪除考慮 CREATE TABLE AS SELECT DISTINCT ... 重建。

法則 6：計數函數選擇

• COUNT(*) ≥ COUNT(1) > COUNT(col)（無索引時）。
• 有索引欄位：COUNT(index_col) 最快（Index Fast Full Scan）。
• COUNT(*) 不判斷 NULL，語義最清晰，現代優化器處理最優。

法則 7：WHERE 替代 HAVING

HAVING 在 GROUP BY 後過濾，需先彙總全量數據。能下推至 WHERE 的條件務必下推。

-- 低效
SELECT region, AVG(log_size) FROM location GROUP BY region HAVING region != 'SYDNEY';

-- 高效
SELECT region, AVG(log_size) FROM location WHERE region != 'SYDNEY' GROUP BY region;

HAVING 僅保留對彙總函數的過濾（如 HAVING COUNT(*) > 10）。

法則 8：減少子查詢重複訪問表

-- 低效：兩次掃描 TAB_COLUMNS
WHERE tab_name = (SELECT tab_name FROM tab_columns WHERE version=604)
AND db_ver = (SELECT db_ver FROM tab_columns WHERE version=604);

-- 高效：一次掃描返回多欄位
WHERE (tab_name, db_ver) = (SELECT tab_name, db_ver FROM tab_columns WHERE version=604);

法則 9：使用表別名消除歧義與加速解析

SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno;

多表連接強制使用別名並前綴所有欄位。

法則 10：EXISTS 替代 IN（相關子查詢場景）

-- 低效
SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM dept WHERE loc='MELB');

-- 高效
SELECT * FROM emp e WHERE EXISTS (SELECT 1 FROM dept d WHERE d.deptno=e.deptno AND loc='MELB');

EXISTS 可利用外表欄位值在內表索引定位，避免子查詢物化。

法則 11：NOT EXISTS 替代 NOT IN（鐵律）

-- 最高效
SELECT * FROM emp e WHERE NOT EXISTS (SELECT 1 FROM dept d WHERE d.deptno=e.deptno AND dept_cat='A');

避免 NOT IN 全表掃描與 NULL 陷阱。

法則 12：連接替代 EXISTS（簡單等值連接）

-- EXISTS 版本
SELECT ename FROM emp e WHERE EXISTS (SELECT 1 FROM dept d WHERE d.deptno=e.deptno AND dept_cat='A');

-- 連接版本（通常更優）
SELECT e.ename FROM emp e JOIN dept d ON e.deptno=d.deptno WHERE d.dept_cat='A';

連接給優化器更多重排序空間，但需注意重複行（可加 DISTINCT 或改用 EXISTS）。

法則 13：EXISTS 替代 DISTINCT（一對多去重）

-- 低效：產生笛卡兒積再去重
SELECT DISTINCT d.deptno, d.dname FROM dept d JOIN emp e ON d.deptno=e.deptno;

-- 高效：半連接語義
SELECT d.deptno, d.dname FROM dept d WHERE EXISTS (SELECT 1 FROM emp e WHERE e.deptno=d.deptno);

法則 14：索引欄位禁止運算/函數（SARGable 原則）

-- 低效：索引失效，全表掃描
WHERE sal * 12 > 25000;

-- 高效：常數運算移至右側
WHERE sal > 25000 / 12;

同理：WHERE TRUNC(date_col) = ... → WHERE date_col BETWEEN ... AND ...。功能性索引（Function-Based Index）可解決不可避免的函數場景。

總結

前 2 條為 RBO 時代遺留，現代 CBO 環境下統計資訊完整性、索引設計合理性、綁定變量使用、避免隱式轉型才是調優核心。14 條法則中，第 3、7、11、14 條為永恆真理，建議列為代碼審查核對清單。