When to Use UPDATE vs MERGE in Oracle: Performance Tips & Pitfalls

Background

Both UPDATE and MERGE can be used to modify rows in Oracle tables, but their performance can differ significantly in certain scenarios. Choosing the right statement can reduce execution time from hours to minutes.

When UPDATE Is Preferred

Single‑table updates are fast and stable. If a column serves both as a filter and as the updated value and has a highly selective index, e.g., UPDATE A SET status = 1 WHERE id = 1 AND status = 2 AND idc IN (…) , UPDATE is optimal.

When MERGE Is Preferred

When you need to join one or more tables to determine which rows to update and which to insert; MERGE can handle both in a single operation, often faster than separate INSERT + UPDATE statements. The NOT EXISTS part of an UPDATE such as UPDATE a SET i = (SELECT i FROM b WHERE a.id = b.id) WHERE NOT EXISTS (SELECT 1 FROM b WHERE a.id = b.id) adds overhead; a MERGE can avoid this.

Drawbacks of MERGE

MERGE

cannot modify columns referenced in the ON clause (ORA‑38104 error). In some cases, placing filter conditions outside the ON clause can cause performance regressions.

Example 1: Massive Update

Original UPDATE plan processed 682,546 rows, taking over two hours. The plan performed a nested‑loops join for each row, resulting in a cost of roughly 3.38 × 10⁹.

Optimization replaced the row‑by‑row update with a set‑based MERGE using a hash join, reducing the operation to a single scan and dramatically improving runtime.

Example 2: Join‑Based Update

Another case showed a full‑table scan on two tables. By moving the filter into the ON clause of a MERGE and using a hash join, the optimizer chose an index‑based plan.

Example 3: Filtering in WHERE Clause

A MERGE that placed a sub‑query filter in the WHERE clause caused the optimizer to ignore the sub‑query’s join, leading to poor performance. Moving the sub‑query into the ON clause resolved the issue.

Conclusion

Use UPDATE for simple, single‑table modifications with selective indexes. Prefer MERGE when you need to combine insert and update logic across joined tables or avoid costly NOT EXISTS checks. Pay attention to which columns appear in the ON clause, and consider hash joins for large data sets to achieve substantial performance gains.