Merge statement in Oracle

Merge statement is introduced in oracle9i and it has enhanced in oracle10g. It is beneficial for the ETL (Extraction, Transformation, and Loading) part of any Business Intelligence process flow, addressing all the needs of highly scalable data transformation inside the database. In data warehouse environment, when we refresh FACT table periodically, the source system cannot distinguish between newly inserted or changed information during the extraction. We need to scan the FACT table multiple times and it is becoming performance issue.

Prior to Oracle9i, these operations were expressed either as a sequence of DMLs (INSERT/UPDATE) or as PL/SQL loops deciding, for each row, whether to insert or update the data.

In oracle9i, it overcomes this problem and we can write both INSERT, UPDATE in one statement called MERGE. It reduces multiple scans and it improves the performance.

Here is sample Merge statement in oracle9i. The source_table is created as the same structure of ALL_OBJECTS with out any data.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHEN NOT MATCHED THEN
7 INSERT (object_id, status)
8 VALUES (b.object_id, b.status);

22278 rows merged.

SQL>

In oracle10g, merge statement is enhanced. The new enhancements are

1. Optional INSRET or UDPATE clause
2. Conditional operations for INSERT and UPDATE
3. We can use the delete statement on Merge

Optional INSRET or UDPATE clause

The MATCHED and NOT MATCHED clauses are now optional making all of the following examples valid.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status);
VALUES (b.object_id, b.status)
*
ERROR at line 6:
ORA-00905: missing keyword

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status;
UPDATE SET a.status = b.status
*
ERROR at line 5:
ORA-00905: missing keyword

SQL>

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status);

28603 rows merged.

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status;

28603 rows merged.

SQL>
Conditional operations for INSERT and UPDATE

Conditional inserts and updates are now possible by using a WHERE clause on these statements

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production


SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 WHEN NOT MATCHED THEN
8 INSERT (object_id, status)
9 VALUES (b.object_id, b.status)
10 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword


SQL>
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status)
7 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 7:
ORA-00905: missing keyword


SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID';
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword


SQL>

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 WHEN NOT MATCHED THEN
8 INSERT (object_id, status)
9 VALUES (b.object_id, b.status)
10 WHERE b.status != 'VALID';

33 rows merged.

SQL>
SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN NOT MATCHED THEN
5 INSERT (object_id, status)
6 VALUES (b.object_id, b.status)
7 WHERE b.status != 'VALID';

0 rows merged.

SQL>
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID';

33 rows merged.

SQL>

Delete statement on Merge

An optional DELETE WHERE clause can be used to clean up after a merge operation. Only those rows which match both the ON clause and the DELETE WHERE clause are deleted.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 – Production
SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 DELETE WHERE (b.status = 'VALID');
WHERE b.status != 'VALID'
*
ERROR at line 6:
ORA-00905: missing keyword


SQL>

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> MERGE INTO source_table a
2 USING all_objects b
3 ON (a.object_id = b.object_id)
4 WHEN MATCHED THEN
5 UPDATE SET a.status = b.status
6 WHERE b.status != 'VALID'
7 DELETE WHERE (b.status = 'VALID');

33 rows merged.

SQL>

Restriction On Merge : We cannot update a column that is referenced in the ON condition clause.