Deep Dive — MERGE

One pass over the source, one hash join to the target, and every row is inserted, updated or deleted — no loop, no "does it exist" check.

Used in
Chapter 1 — SQL First
Appendix entry
MERGE
Related
INSERT SELECT UPDATE WHERE EXISTS FORALL APPEND HINT

MERGE is a conditional INSERT/UPDATE/DELETE driven by a join between a source (any query) and a target table. The optimizer treats it like a join: it reads the source once, joins it to the target (usually a hash join on large sets) and applies the matching branch to each row.

Use it whenever the loop would be "for each source row: check if the target row exists, then update or insert". That loop is the single most common PL/SQL anti-pattern on large tables, and MERGE replaces it with one statement.

SQL 
MERGE [hint] INTO <target> [t_alias]
USING { <table> | <view> | (<subquery>) } [s_alias]
ON    (<join_condition>)
[WHEN MATCHED THEN
   UPDATE SET <col> = <expr> [, ...]
   [WHERE <update_condition>]
   [DELETE WHERE <delete_condition>]]
[WHEN NOT MATCHED THEN
   INSERT [(<cols>)] VALUES (<exprs>)
   [WHERE <insert_condition>]]
[LOG ERRORS [INTO <err_table>] [(<tag>)] [REJECT LIMIT { <n> | UNLIMITED }]];
  • At least one of WHEN MATCHED / WHEN NOT MATCHED is required; either can be omitted.
  • DELETE WHERE applies after the UPDATE SET on the same row, and only to rows that matched.
  • Columns referenced in ON cannot be updated (ORA-38104).
  • SQL%ROWCOUNT after a MERGE = inserted + updated + deleted rows, combined.
  • Hints: /*+ APPEND */ on the INSERT branch gives direct-path for the inserted rows; /*+ PARALLEL */ parallelizes the whole statement (with ENABLE PARALLEL DML).

SQL 
MERGE INTO customers c
USING (SELECT customer_id, MAX(order_date) AS last_dt
       FROM   orders
       GROUP  BY customer_id) o
ON    (c.customer_id = o.customer_id)
WHEN MATCHED THEN
  UPDATE SET c.last_order_date = o.last_dt
  WHERE  c.last_order_date IS NULL OR c.last_order_date < o.last_dt;   -- touch only what changes

The WHERE on the UPDATE branch avoids rewriting rows that already hold the value — less redo, fewer index updates, no pointless trigger firing.

SQL 
MERGE INTO customers c
USING customers_stage s
ON    (c.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET c.status = s.status,
             c.tier   = s.tier
WHEN NOT MATCHED THEN
  INSERT (customer_id, status, tier, last_order_date)
  VALUES (s.customer_id, s.status, s.tier, NULL);

Cancelled customers with no orders in two years are removed; the rest are updated:

SQL 
MERGE INTO customers c
USING (SELECT customer_id, status,
              (SELECT MAX(order_date) FROM orders o WHERE o.customer_id = s.customer_id) AS last_dt
       FROM   customers_stage s) s
ON    (c.customer_id = s.customer_id)
WHEN MATCHED THEN
  UPDATE SET c.status = s.status
  DELETE WHERE s.status = 'CANCELLED'
           AND (s.last_dt IS NULL OR s.last_dt < ADD_MONTHS(SYSDATE, -24));

SQL 
DECLARE
  TYPE t_ids   IS TABLE OF customers.customer_id%TYPE;
  TYPE t_dates IS TABLE OF DATE;
  l_ids   t_ids;
  l_dates t_dates;
BEGIN
  -- l_ids / l_dates filled by earlier PL/SQL logic
  FORALL i IN 1 .. l_ids.COUNT
    MERGE INTO customers c
    USING (SELECT l_ids(i) AS customer_id, l_dates(i) AS last_dt FROM dual) s
    ON    (c.customer_id = s.customer_id)
    WHEN MATCHED THEN
      UPDATE SET c.last_order_date = s.last_dt
    WHEN NOT MATCHED THEN
      INSERT (customer_id, status, last_order_date)
      VALUES (s.customer_id, 'NEW', s.last_dt);
END;

One statement per batch, not per row. If the collection is large, a global temporary table + a single MERGE is faster still.

SQL 
-- once:
EXEC DBMS_ERRLOG.CREATE_ERROR_LOG('CUSTOMERS');       -- creates ERR$_CUSTOMERS

MERGE INTO customers c
USING customers_stage s
ON    (c.customer_id = s.customer_id)
WHEN MATCHED THEN     UPDATE SET c.tier = s.tier
WHEN NOT MATCHED THEN INSERT (customer_id, status, tier) VALUES (s.customer_id, s.status, s.tier)
LOG ERRORS INTO err$_customers ('daily feed ' || TO_CHAR(SYSDATE, 'YYYY-MM-DD')) REJECT LIMIT UNLIMITED;

SELECT ora_err_number$, ora_err_mesg$, customer_id FROM err$_customers;

Rows that violate a constraint or fail a conversion go to the error table with the ORA message; the rest are committed. This is the set-based equivalent of SAVE EXCEPTIONS.

  • The source must be unique on the ON key. Two source rows for one target row raise ORA-30926: unable to get a stable set of rows in the source tables. On a 500M-row source this shows up after minutes of work. Deduplicate in the USING subquery (GROUP BY, or ROW_NUMBER() OVER (PARTITION BY key ORDER BY ...) = 1).
  • Index on the join key. With a small source and a huge target the optimizer wants a nested loop through the target's index on the ON columns. With a big source it wants a hash join and a full scan of the target — both fine, as long as statistics are current. Check the plan (DBMS_XPLAN).
  • Undo and redo. MERGE is conventional DML: every updated row generates undo and redo. A 40M-row update is one transaction — size undo accordingly, or drive the MERGE in chunks with DBMS_PARALLEL_EXECUTE.
  • Direct path applies only to the insert branch (MERGE /*+ APPEND */), and only with the usual direct-path restrictions.

  1. ORA-30926 — duplicates in the source. Always deduplicate a feed before merging it.
  2. ORA-38104 — trying to UPDATE SET a column used in ON. Restructure: join on a stable key, update the other columns.
  3. Forgetting the WHERE on the UPDATE branch on a daily feed — every matched row is rewritten every day whether it changed or not. On 5M customers that is 5M redo records per night for nothing.
  4. DELETE WHERE is evaluated on the post-update values of the row.
  5. Triggers fire per affected row exactly as they would for the equivalent INSERT/UPDATE/DELETE.
  6. SQL%ROWCOUNT cannot tell you how many were inserted vs updated. If you need the split, count the source vs the pre-existing matches before the MERGE, or use LOG ERRORS-style tagging.
  7. A correlated subquery in the USING clause (as in 3c) runs per source row. On a large source, join instead.