Introduction

Today I will write about another Database feature unjustly ignored: The Oracle MERGE statement is one of the most powerful SQL features for synchronizing data between tables.

It allows developers and DBAs to perform INSERT, UPDATE, and even DELETE operations in a single SQL statement based on matching conditions.

This article explains Oracle MERGE statement best practices with practical examples and real-world use cases.

Use Cases

MERGE is widely used in:

• Data warehouse ETL processes
• Incremental data loading
• Data synchronization
• Staging-to-production updates
• Slowly Changing Dimensions (SCD)
• Replication and integration workflows

What is the MERGE Statement?

The MERGE statement combines:

• INSERT
• UPDATE
• Optional DELETE

into one atomic operation.

Use Cases – more

• Removing inactive records
• Cleaning temporary data
• Synchronization jobs

Basic Syntax

Sample statement:

MERGE INTO target_table t
USING source_table s
ON (t.id = s.id)
WHEN MATCHED THEN
    UPDATE SET
        t.name = s.name,
        t.salary = s.salary
WHEN NOT MATCHED THEN
    INSERT (id, name, salary)
    VALUES (s.id, s.name, s.salary);

Why Use MERGE?

Benefits

• Reduces multiple SQL statements into one
• Improves performance
• Simplifies ETL logic
• Ensures transactional consistency
• Reduces context switching between SQL operations

Sample Setup

Employees Table

CREATE TABLE employees (
    emp_id      NUMBER PRIMARY KEY,
    emp_name    VARCHAR2(100),
    salary      NUMBER,
    department  VARCHAR2(50)
);

In the lab, I use the usual EMPLOYEES_DEMO table from other articles:

Staging Table

CREATE TABLE employees_stage (
    emp_id      NUMBER,
    emp_name    VARCHAR2(100),
    salary      NUMBER,
    department  VARCHAR2(50)
);

Example 1 — Basic UPSERT Operation

Scenario

Update existing employees and insert new employees from staging.

For start insert some new data into the STAGE table:

INSERT INTO "ADMIN"."EMPLOYEES_STAGE" (EMP_ID, EMP_NAME, SALARY, DEPARTMENT) VALUES ('10', 'Ilie', '107', 'HR')

MERGE INTO employees_demo e
USING employees_stage s
ON (e.employee_id = s.emp_id)
WHEN MATCHED THEN
    UPDATE SET
        e.emp_name = s.emp_name,
        e.salary = s.salary,
        e.department = s.department
WHEN NOT MATCHED THEN
    INSERT (
        employee_id,
        emp_name,
        salary,
        department
    )
    VALUES (
        s.emp_id,
        s.emp_name,
        s.salary,
        s.department
    );

After commit, I can check that the new row is now present for the new employee Ilie from HR:

Use Case

• Daily HR data synchronization
• ETL batch processing
• Application integration

Best Practice #1 — Always Use Proper Join Conditions

The ON clause determines matching logic.

Good Example

Bad Example

ON (e.department = s.department)

Poor join conditions may:
• Update unintended rows
• Cause duplicates
• Lead to performance problems

Recommendation

Use:

• Primary keys
• Unique keys
• Indexed columns

Best Practice #2 — Avoid Updating Unchanged Rows

Unnecessary updates generate:

• Redo logs
• Undo data
• Extra I/O
• Trigger executions

Better Approach

e.g.

WHEN MATCHED THEN
    UPDATE SET
        e.salary = s.salary
WHERE e.salary <> s.salary;

Benefits

• Reduces redo generation
• Improves performance
• Minimizes locking

Best Practice #3 — Use DELETE Carefully

Oracle supports deleting rows during merge. Sample code:

MERGE INTO employees e
USING employees_stage s
ON (e.emp_id = s.emp_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = s.salary
    DELETE WHERE s.salary = 0;

Important:

The DELETE applies only to matched rows.

Best Practice #4 — Index Matching Columns

Performance depends heavily on indexed join columns.

Recommended Index example

CREATE INDEX idx_emp_id
ON employees(emp_id);

Also consider indexing:
• Staging tables
• Foreign keys
• Frequently filtered columns

Best Practice #5 — Filter Source Data Before MERGE

Avoid merging unnecessary rows.

Example

MERGE INTO employees e
USING (
    SELECT *
    FROM employees_stage
    WHERE department = 'IT'
) s
ON (e.emp_id = s.emp_id)

Benefits

• • Smaller execution plans
• Faster merge operations
• Reduced resource usage

Best Practice #6 — Handle Duplicate Source Rows

Duplicate source rows can cause:

ORA-30926: unable to get a stable set of rows

Problem Example

If employees_stage contains multiple rows for the same emp_id. The would be sort of corrupted data as we try to create several identical new employees!

Solution

Use aggregation or analytic functions

Example

USING (
    SELECT emp_id,
           MAX(emp_name) emp_name,
           MAX(salary) salary,
           MAX(department) department
    FROM employees_stage
    GROUP BY emp_id
) s

Best Practice #7 — Commit in Controlled Batches

Large merge operations may:
• Generate massive undo
• Cause long-running transactions
• Increase rollback risks

Recommended Strategy

• Process data in chunks
• Use partitioning
• Commit at logical intervals

Example 2 — MERGE with Conditional Updates

MERGE INTO employees_demo e
USING employees_stage s
ON (e.employee_id = s.emp_id)
WHEN MATCHED THEN
    UPDATE SET
        e.salary = s.salary
    WHERE s.salary > e.salary
WHEN NOT MATCHED THEN
    INSERT (
        employee_id,
        emp_name,
        salary,
        department
    )
    VALUES (
        s.emp_id,
        s.emp_name,
        s.salary,
        s.department
    );

Use Case

Update salaries only when the new value is higher.

Example 3 — Slowly Changing Dimension (SCD Type 1)

Data warehouses commonly use MERGE.

Scenario

Overwrite old customer information with latest values.

MERGE INTO customers c
USING customers_stage s
ON (c.customer_id = s.customer_id)
WHEN MATCHED THEN
    UPDATE SET
        c.customer_name = s.customer_name,
        c.city = s.city
WHEN NOT MATCHED THEN
    INSERT (
        customer_id,
        customer_name,
        city
    )
    VALUES (
        s.customer_id,
        s.customer_name,
        s.city
    );

Example 4 — MERGE from External Tables

Use Case

Load CSV data efficiently.

MERGE INTO sales_target t
USING external_sales_file s
ON (t.sale_id = s.sale_id)
WHEN MATCHED THEN
    UPDATE SET t.amount = s.amount
WHEN NOT MATCHED THEN
    INSERT (
        sale_id,
        amount
    )
    VALUES (
        s.sale_id,
        s.amount
    );

Performance Considerations

Monitor Execution Plans

Use:

EXPLAIN PLAN FOR
MERGE ...

Then:

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Parallel MERGE Operations

For large datasets:

ALTER SESSION ENABLE PARALLEL DML;
MERGE /*+ PARALLEL(e,4) */
INTO employees e
USING employees_stage s
ON (e.emp_id = s.emp_id)
...

Use Carefully

Parallel DML increases:
• CPU usage
• Temporary space usage
• System load

Common Errors

ORA-30926

Cause:
• Duplicate rows in source dataset
Fix:
• Remove duplicates before merge

ORA-00001 Unique Constraint Violated

Cause:
• Duplicate inserts
Fix:
• Validate source uniqueness

ORA-38104

Columns referenced in the ON clause cannot be updated

Bad Example

ON (e.emp_id = s.emp_id)
UPDATE SET e.emp_id = s.emp_id

Fix:
• Do not update columns used in ON

Real-World Use Cases

ETL Incremental Loads

Synchronize staging and warehouse tables nightly.

HR Synchronisation

Update employee records from external HR systems.

Financial Reconciliation

Merge transactions from multiple systems.

Inventory Systems

Update stock quantities from incoming feeds.

Customer Master Data Management

Maintain centralized customer records.

MERGE vs Separate INSERT/UPDATE

Recommended MERGE Checklist

Before deploying a MERGE statement:
• Use indexed join columns
• Eliminate duplicate source rows
• Avoid unnecessary updates
• Test execution plans
• Validate source data quality
• Consider transaction size
• Monitor undo and redo generation

Conclusion

The Oracle MERGE statement is an essential feature for efficient data synchronization and ETL processing. When properly designed, it simplifies SQL logic, improves performance, and ensures transactional consistency.

However, poor implementation can lead to:
• Performance bottlenecks
• Duplicate data
• Excessive redo generation
• Locking issues

By following the best practices covered in this article, Oracle professionals can build scalable, maintainable, and high-performance data integration solutions.
For DBAs and developers working with enterprise Oracle systems, mastering MERGE is a critical skill that delivers significant operational and performance benefits.