《Oracle 查詢優化改寫》第四章心得：MERGE 替代 UPDATE 的性能優勢實測

書評筆記：第四章「插入、更新與刪除」核心技巧

本章重點講解 MERGE 語句在多表關聯更新場景下對比傳統 UPDATE ... WHERE EXISTS 的性能優勢，並介紹 WITH CHECK OPTION 視圖繞過 CHECK 約束不能引用 SYSDATE 的限制。

1. CHECK 約束無法用 SYSDATE 的繞過方案

-- 直接建表會報 ORA-02436
CREATE TABLE ttt (create_date DATE CHECK (create_date > SYSDATE));

-- 解法：視圖 + WITH CHECK OPTION
INSERT INTO (SELECT empno, ename, hiredate FROM scott.emp
             WHERE hiredate <= SYSDATE WITH CHECK OPTION)
VALUES (9999, 'test', SYSDATE+1);  -- 報錯 ORA-01402 違反視圖條件
INSERT INTO (SELECT empno, ename, hiredate FROM scott.emp
             WHERE hiredate <= SYSDATE WITH CHECK OPTION)
VALUES (9999, 'test', SYSDATE-1);  -- 成功

2. UPDATE 易犯錯誤與技巧

• 忘寫 WHERE 導致全表更新 → 技巧：將 SET 子句的值複製到 WHERE 子句做自檢。
• 多表關聯更新建議改寫為 MERGE，因 MERGE 只掃描一次目標表。

3. 實測對比：UPDATE vs MERGE (11.2.0.3)

場景：將 emp_bk.dname 根據 dept_bk 更新，條件 dname IN ('ACCOUNTING','RESERCH')。

-- 傳統 UPDATE
UPDATE lhr.emp_bk a
SET a.dname = (SELECT b.dname FROM lhr.dept_bk b
               WHERE b.deptno=a.deptno AND b.dname IN ('ACCOUNTING','RESERCH'))
WHERE EXISTS (SELECT 1 FROM lhr.dept_bk b
              WHERE b.deptno=a.deptno AND b.dname IN ('ACCOUNTING','RESERCH'));

-- MERGE 改寫
MERGE INTO lhr.emp_bk a
USING (SELECT dname, deptno FROM lhr.dept_bk
       WHERE dname IN ('ACCOUNTING','RESERCH')) bb
ON (bb.deptno = a.deptno)
WHEN MATCHED THEN UPDATE SET a.dname = bb.dname;

執行計劃顯示 UPDATE 掃描 dept_bk 兩次 (HASH JOIN SEMI + FILTER)，MERGE 僅一次 HASH JOIN。

4. 相關優化案例鏈接（原文）

• UPDATE 改 MERGE (MAX+DECODE)
• MERGE 非關聯形式顯神能
• 走索引卻像蝸牛一樣慢

5. 總結

1. 多表關聯更新首選 MERGE，語義清晰、單掃描、避免重複子查詢。
2. WITH CHECK OPTION 視圖可實現業務規則在 DML 時的強制驗證，繞過 CHECK 約束限制。
3. 養成「寫 UPDATE 先寫 WHERE」習慣，防誤更新全表。