Oracle MERGE INTO 實戰大全：Upsert 語法、多表合併、效能優化與避坑指南

MERGE 核心價值：單陳述式實現「存在更新、不存在插入」

Oracle 9i 引入 MERGE，10g/11g 持續增強（支援 `DELETE` 子句、錯誤記錄、並行提示）。適用於數據倉儲 ETL、增量同步、緩存刷新等高頻 Upsert 場景。

標準語法 (11g/12c/19c/21c/23ai)

MERGE [hint] INTO target_table [t_alias]
USING {source_table | view | subquery} [s_alias]
ON (join_condition)
WHEN MATCHED THEN
  UPDATE SET col1 = expr1, col2 = expr2 [WHERE update_condition]
  [DELETE WHERE delete_condition]          -- 10g+ 新增
WHEN NOT MATCHED THEN
  INSERT (col1, col2, ...) VALUES (expr1, expr2, ...)
  [WHERE insert_condition];                -- 10g+ 新增

基礎實戰：單表 Upsert

-- 目標表
CREATE TABLE tj_test1 (id NUMBER PRIMARY KEY, name VARCHAR2(20), age NUMBER);
INSERT INTO tj_test1 VALUES (1, 'jkl', 23);

-- 來源表
CREATE TABLE tj_test AS SELECT * FROM tj_test1 WHERE 1=0;
INSERT INTO tj_test VALUES (1, 'jan', 23);
INSERT INTO tj_test VALUES (2, 'kk', 22);
INSERT INTO tj_test VALUES (3, 'joe', 27);

-- MERGE：id 相同更新 name/age，不同則插入
MERGE INTO tj_test1 tgt
USING tj_test src
ON (tgt.id = src.id)
WHEN MATCHED THEN
  UPDATE SET tgt.name = src.name, tgt.age = src.age
WHEN NOT MATCHED THEN
  INSERT (id, name, age) VALUES (src.id, src.name, src.age);

-- 結果：id=1 name 從 jkl→jan，新增 id=2,3

進階場景：多表關聯合併

MERGE INTO course c
USING (
  SELECT course_name, period, course_hours
  FROM   course_updates
) cu
ON (c.course_name = cu.course_name AND c.period = cu.period)
WHEN MATCHED THEN
  UPDATE SET c.course_hours = cu.course_hours
WHEN NOT MATCHED THEN
  INSERT (course_name, period, course_hours)
  VALUES (cu.course_name, cu.period, cu.course_hours);

單行資料 Upsert 技巧：DUAL 當來源

MERGE INTO config tgt
USING (SELECT 'MAX_SESSIONS' key, '500' val FROM dual) src
ON (tgt.cfg_key = src.key)
WHEN MATCHED THEN UPDATE SET tgt.cfg_val = src.val
WHEN NOT MATCHED THEN INSERT (cfg_key, cfg_val) VALUES (src.key, src.val);

效能優化關鍵點

1. ON 條件列必須有索引：目標表與來源表連接鍵建立唯一/非唯一索引，避免全表掃描。
2. 來源集合精簡：`USING` 子查詢僅選取必要欄位，必要時加 `/*+ MATERIALIZE */` 物化。
3. 大批次分塊：超過 10 萬行建議分批 `MERGE`，配合 `DBMS_PARALLEL_EXECUTE` 並行處理。
4. 並行提示：`MERGE /*+ PARALLEL(tgt 4) */ INTO ...` 針對大表啟用 PX。
5. 避免觸發器連鎖：目標表若有複雜觸發器，考慮 `INSERT /*+ APPEND */` + `UPDATE` 分離替代。

常見陷阱與對策

結語

`MERGE` 是 Oracle 實現 Upsert 的標準化、集合化利器。掌握其完整語法（含 `DELETE/WHERE` 子句）、索引設計原則、分批並行策略，能將 ETL 合併邏輯從「存儲過程+迴圈」重構為單一 SQL，提升可維護性與效能數個數量級。