【PL/SQL】MERGE文の高度利用：バルク更新と排他制御を両立する条件設計

大量データの更新と同時アクセスの安定性を両立させるには、MERGEを単なる「UPSERT文」として使うのではなく、USING句の設計、ON条件の粒度、WHEN MATCHED/NOT MATCHEDの分岐、行レベルの追加条件、そしてエラーロギングやバージョン管理と組み合わせた排他戦略までを一体で設計する必要がある。PL/SQL側に逐次ループを持たせず、コレクションやステージング表をUSING源にして一括適用するのが性能面の出発点であり、楽観ロックや差分判定、削除検出までをMERGEに畳み込むことで、シンプルな構文のまま業務要件を充足できる。ここではバルク更新の基本骨格から、排他制御を崩さない条件設計、差分更新の最適化、物理削除・論理削除の切替、エラーロギングや再実行前提の運用までを実装例で示す。

コレクションをUSING源にした一括UPSERTの骨格

PL/SQLコレクションをテーブル化してUSINGに与えると、逐次ループなしで一括適用できる。キーと更新対象列を明確にし、USING側で必要な前処理（正規化や型変換）を済ませておくと安定する。

-- 入力レコード型とコレクション
CREATE OR REPLACE TYPE t_order_row AS OBJECT(
  order_id    NUMBER,
  customer_id NUMBER,
  amount      NUMBER,
  upd_ts      TIMESTAMP
);
/
CREATE OR REPLACE TYPE t_order_tab IS TABLE OF t_order_row;
/

-- サンプル入力を作成してMERGEを一括適用
DECLARE
  v_src t_order_tab := t_order_tab(
    t_order_row(1001, 10, 1200, SYSTIMESTAMP),
    t_order_row(1002, 11, 1500, SYSTIMESTAMP),
    t_order_row(1003, 12,  800, SYSTIMESTAMP)
  );
BEGIN
  MERGE /*+ USE_NL */ INTO app.orders d
  USING (
    SELECT CAST(:1 AS t_order_tab) AS tt FROM dual
  ) s1
  CROSS JOIN TABLE(s1.tt) s
     ON (d.order_id = s.order_id)
  WHEN MATCHED THEN
    UPDATE SET
      d.customer_id = s.customer_id,
      d.amount      = s.amount,
      d.updated_at  = s.upd_ts
  WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, amount, created_at, updated_at)
    VALUES (s.order_id, s.customer_id, s.amount, s.upd_ts, s.upd_ts);
END;
/

差分のみ更新する条件でロックとUNDOを抑制する

更新行が多いほどUNDOとロック時間が増えるため、値が変わった行だけに限定する。MERGEのUPDATE句にはWHEREを併記できるので、差分判定を入れて無駄な更新を避ける。

MERGE INTO app.orders d
USING (SELECT CAST(:1 AS t_order_tab) tt FROM dual) s1
CROSS JOIN TABLE(s1.tt) s
   ON (d.order_id = s.order_id)
WHEN MATCHED THEN
  UPDATE SET
    d.customer_id = s.customer_id,
    d.amount      = s.amount,
    d.updated_at  = s.upd_ts
  WHERE (d.customer_id != s.customer_id OR d.amount != s.amount)
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, created_at, updated_at)
  VALUES (s.order_id, s.customer_id, s.amount, s.upd_ts, s.upd_ts);

楽観ロックを内包するON条件の設計

同時更新の競合をアプリ側で握るのではなく、MERGEのON条件にバージョンやハッシュを組み込むと、他トランザクションの更新を自然に検知できる。ONで一致しない場合はINSERT側へ落ちるため、別経路で「行はあるがバージョン不一致」を検出して補償へ回す。

-- 対象表にバージョン列を持つ前提
-- d.version_no が一致する場合のみ更新を許可する
MERGE INTO app.orders d
USING (SELECT CAST(:1 AS t_order_tab) tt FROM dual) s1
CROSS JOIN TABLE(s1.tt) s
   ON (d.order_id   = s.order_id
   AND d.version_no = s.version_no)   -- 楽観ロック条件
WHEN MATCHED THEN
  UPDATE SET
    d.amount      = s.amount,
    d.version_no  = d.version_no + 1,
    d.updated_at  = SYSTIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, version_no, created_at, updated_at)
  VALUES (s.order_id, s.customer_id, s.amount, 1, SYSTIMESTAMP, SYSTIMESTAMP);

論理削除や終端検出をMERGEに畳み込む

USINGに存在しない既存レコードを「消失」とみなす場合、DELETE句を加えるか、論理削除フラグを立てる。全消しを避けるためにドメインキーでUSING側の範囲を限定し、防衛的な条件を併置する。

-- ステージングの範囲に含まれないレコードを論理削除
MERGE INTO app.customers d
USING app.stage_customers s
   ON (d.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET d.name = s.name, d.is_deleted = 'N', d.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
  UPDATE SET d.is_deleted = 'Y', d.updated_at = SYSTIMESTAMP
  WHERE d.segment = 'RETAIL';  -- 安全弁として範囲制限

キー重複やデータ欠陥を弾くDMLエラーロギング

一括適用では部分的な重複や整合性違反を丸ごと失敗にしない設計が有効である。DMLエラーロギングを併用すると、失敗行を捕捉して継続でき、後段で補償が可能になる。

-- 事前にエラーログ表を生成
BEGIN
  DBMS_ERRLOG.create_error_log('ORDERS');
END;
/

MERGE INTO app.orders d
USING (SELECT CAST(:1 AS t_order_tab) tt FROM dual) s1
CROSS JOIN TABLE(s1.tt) s
   ON (d.order_id = s.order_id)
WHEN MATCHED THEN
  UPDATE SET d.amount = s.amount, d.updated_at = s.upd_ts
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, created_at, updated_at)
  VALUES (s.order_id, s.customer_id, s.amount, s.upd_ts, s.upd_ts)
LOG ERRORS INTO err$_orders REJECT LIMIT UNLIMITED;

ステージング表を介した大規模適用と統計安定化

コレクションではなくステージング表をUSING源にすると、並列度や統計の安定化、監査性で優位になる。事前に重複排除や主キー検査を施し、USING側を集約しておくと分岐の不確実性が減る。

-- ステージングの整形
CREATE TABLE app.stage_orders AS SELECT * FROM app.stage_orders_src WHERE 1=0;
-- ロード後の正規化と重複排除
INSERT /*+ APPEND */ INTO app.stage_orders
SELECT order_id, MAX(customer_id) customer_id, MAX(amount) amount, MAX(upd_ts) upd_ts
  FROM app.stage_orders_src
 GROUP BY order_id;
COMMIT;

-- ステージングから本表へ
MERGE /*+ PARALLEL(d 4) */ INTO app.orders d
USING app.stage_orders s
   ON (d.order_id = s.order_id)
WHEN MATCHED THEN
  UPDATE SET d.customer_id = s.customer_id, d.amount = s.amount, d.updated_at = s.upd_ts
  WHERE (d.customer_id != s.customer_id OR d.amount != s.amount)
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, created_at, updated_at)
  VALUES (s.order_id, s.customer_id, s.amount, s.upd_ts, s.upd_ts);

行レベル条件で競合を回避する排他設計

同時実行が想定される場合、対象の小刻みな分割と行レベル条件の厳密化で競合を緩和する。ドメインキーで範囲を絞り、USING側のサブセットのみを対象にすると、ロックの競合面積が減る。さらに更新側のWHEREに現状値のチェックを残しておくと、他トランザクションの直近更新をすり抜けない。

-- セグメント単位でバッチを分割して適用
MERGE INTO app.orders d
USING (SELECT * FROM app.stage_orders WHERE segment = :seg) s
   ON (d.order_id = s.order_id AND d.segment = s.segment)
WHEN MATCHED THEN
  UPDATE SET d.amount = s.amount
  WHERE d.updated_at <= s.upd_ts   -- 直近更新の踏み潰し防止
WHEN NOT MATCHED THEN
  INSERT (order_id, segment, amount, created_at, updated_at)
  VALUES (s.order_id, s.segment, s.amount, s.upd_ts, s.upd_ts);

MERGEに落とし込む前の差分抽出でI/Oをさらに削減する

USING側で事前に差分だけに絞ると、MERGE対象行が減りロックとUNDOを抑制できる。ハッシュ比較やNOT EXISTSでの差分抽出を挟み、USINGに渡す。

-- 差分のみをUSING源にする例
WITH d AS (
  SELECT order_id, customer_id, amount FROM app.orders
),
s AS (
  SELECT order_id, customer_id, amount FROM app.stage_orders
),
diff AS (
  SELECT s.*
    FROM s
    LEFT JOIN d ON d.order_id = s.order_id
   WHERE d.order_id IS NULL
      OR d.customer_id != s.customer_id
      OR d.amount      != s.amount
)
MERGE INTO app.orders d
USING diff s
   ON (d.order_id = s.order_id)
WHEN MATCHED THEN
  UPDATE SET d.customer_id = s.customer_id, d.amount = s.amount, d.updated_at = SYSTIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (order_id, customer_id, amount, created_at, updated_at)
  VALUES (s.order_id, s.customer_id, s.amount, SYSTIMESTAMP, SYSTIMESTAMP);

再実行に耐える冪等設計とトランザクション境界

MERGEは冪等に書けるため、失敗時にステージングを残して再実行できる。コミットはジョブの論理的なバッチ境界に合わせ、部分コミットが必要ならステージングをセグメントやキー範囲で分割する。ログと監査の相関IDを付与し、どのバッチがどのキー範囲を処理したかを辿れるようにすると、再実行や補償が容易になる。

PL/SQLからのバルク適用テンプレート

PL/SQLの外側は小さく保ち、MERGEそのものはSQLとして一括実行する。入力の整形、相関IDの設定、エラーログの確認と補償の起票を標準化すると運用が安定する。

DECLARE
  v_src t_order_tab := t_order_tab(
    t_order_row(2001, 21, 300, SYSTIMESTAMP),
    t_order_row(2002, 22, 450, SYSTIMESTAMP)
  );
  v_cid VARCHAR2(64) := 'CID-' || TO_CHAR(SYSTIMESTAMP,'YYYYMMDDHH24MISSFF3');
BEGIN
  DBMS_APPLICATION_INFO.set_module('ORDERS','MERGE');
  DBMS_SESSION.set_identifier(v_cid);

  MERGE INTO app.orders d
  USING (SELECT CAST(:1 AS t_order_tab) tt FROM dual) s1
  CROSS JOIN TABLE(s1.tt) s
     ON (d.order_id = s.order_id)
  WHEN MATCHED THEN
    UPDATE SET d.amount = s.amount, d.updated_at = s.upd_ts
    WHERE d.amount != s.amount
  WHEN NOT MATCHED THEN
    INSERT (order_id, customer_id, amount, created_at, updated_at)
    VALUES (s.order_id, s.customer_id, s.amount, s.upd_ts, s.upd_ts)
  LOG ERRORS INTO err$_orders REJECT LIMIT UNLIMITED;

  COMMIT;
END;
/

まとめ

MERGEはUSING源の設計、ON条件の粒度、UPDATE側の差分判定、論理削除の併用、楽観ロックや行レベル条件、エラーロギングによる部分継続を組み合わせることで、バルク更新と排他制御を同時に満たす強力な更新器となる。PL/SQLの逐次処理を排し、コレクションやステージングを介して一括適用しつつ、差分更新と防衛的な条件でロックとUNDOを抑える。再実行と補償を前提とした冪等設計と相関IDの一貫運用を添えることで、大規模運用時の安定性と可観測性が両立する。