Oracle SQL 效能優化系列 (七)：COMMIT 策略、計數寫法、WHERE 替代 HAVING、減少表查詢

本系列第七篇，涵蓋四項實戰優化技巧。

12. 盡可能頻繁 COMMIT

每次 COMMIT 釋放資源：

• Undo 區塊 (回滾段資訊)
• 鎖 (Lock)
• Redo Log Buffer 空間
• 管理上述資源的內部開銷

⚠️ 必須平衡交易完整性：過度 COMMIT 可能導致邏輯不一致 (如轉帳僅扣款未入帳)。批次處理可設定每 N 筆 COMMIT。

13. 計算記錄數：COUNT(*) vs COUNT(1) vs COUNT(索引欄)

實測三者效能差異可忽略 (優化器內部統一處理)。若有索引，COUNT(索引欄) 理論最快 (僅掃描索引)。

-- 皆可接受
SELECT COUNT(*) FROM emp;
SELECT COUNT(1) FROM emp;
SELECT COUNT(empno) FROM emp; -- 若 empno 有索引

14. 用 WHERE 子句替代 HAVING 子句

HAVING 在分組後過濾，需先完成所有分組/排序/聚合；WHERE 在分組前過濾，減少參與運算的資料量。

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

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

原則：非聚合條件放 WHERE，聚合條件 (如 HAVING COUNT(*) > 10) 才放 HAVING。

15. 減少對表的重複查詢 (子查詢合併、多欄位更新)

子查詢合併

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

-- 高效：單次掃描
SELECT tab_name FROM tables
WHERE (tab_name, db_ver) = (SELECT tab_name, db_ver FROM tab_columns WHERE version = 604);

多欄位更新合併

-- 低效：兩次子查詢
UPDATE emp
SET emp_cat = (SELECT MAX(category) FROM emp_categories),
    sal_range = (SELECT MAX(sal_range) FROM emp_categories)
WHERE emp_dept = 20;

-- 高效：單次子查詢
UPDATE emp
SET (emp_cat, sal_range) = (SELECT MAX(category), MAX(sal_range) FROM emp_categories)
WHERE emp_dept = 20;

這四項技巧屬於「寫碼習慣」層面，無需 DDL 變更，立竿見影降低資料庫負載。