Blogs

MERGE Statement – Oracle Base

Posted on by admin

Home » Articles » 9i

» Here

The MERGE statement was introduced in Oracle 9i to insert or update data conditionally depending on its presence, a process also known as “upsert”. The MERGE statement reduces table parsing and can perform the operation in parallel if necessary.

  • Syntax
  • Performance

Related articles

.

    The MERGE

  • Statement MERGE Statement

    • Enhancements to

  • Oracle Database 10g Syntax

Consider

the

following example where data from the HR_RECORDS table is merged into the EMPLOYEES table.

MERGE INTO EMPLOYEES E USING hr_records h ON (e.id = h.emp_id) WHEN MATCHING AND THEN UPDATE SET e.address = h.address WHEN MISMATCHED AND THEN INSERT (id, address) VALUES (h.emp_id, h.address);

The source can also be a query.

MERGE INTO EMPLOYEES E USING (SELECT * FROM hr_records WHERE start_date > ADD_MONTHS(SYSDATE, -1)) h ON (e.id = h.emp_id) WHEN MATCHING AND THEN UPDATE SET e.address = h.address WHEN MISMATCHED AND THEN INSERT (id, address) VALUES (h.emp_id, h.address);

Performance

The MERGE statement is optimized to combine data sets, rather than individual rows, as shown in the following example

.

Create the following test tables. The source table contains all the rows in the ALL_OBJECTS view, while the destination table contains about half of the rows.

CREATE source_tab TABLE SUCH AS SELECT object_id, OWNER, all_objects object_name object_type; MODIFY TABLE source_tab ADD ( RESTRICTION source_tab_pk PRIMARY KEY (object_id) ); CREATE THE TABLE dest_tab AS SELECT object_id, OWNER, object_name, all_objects object_type WHERE ROWNUM <= 25000; ALTER TABLE dest_tab ADD ( CONSTRAINT dest_tab_pk PRIMARY KEY (object_id) ); EXEC DBMS_STATS.gather_table_stats(USER, ‘source_tab’, cascade=> TRUE); EXEC DBMS_STATS.gather_table_stats(USER, ‘dest_tab’, cascade=> TRUE);

The following code compares the performance of four merge operations. The first uses the direct MERGE statement. The second also uses the MERGE statement, but row by row. The third party performs an update and conditionally inserts the row if the update touches zero rows. The fourth inserts the row and then performs an update if the insertion fails with a duplicate value in the index exception.

SET SERVEROUTPUT IN DECLARE TYPE t_tab IS A source_tab%ROWTYPE TABLE; l_tab t_tab; l_start NUMBER; BEGIN l_start := DBMS_UTILITY.get_time; MERGE INTO dest_tab A USING source_tab b ON (a.object_id = b.object_id) WHEN MATCHING AND THEN UPDATE SET owner = b.owner, object_name = b.object_name, object_type = b.object_type WHEN MISMATCHED, AND THEN INSERT (object_id, OWNER, object_name, object_type) VALUES (b.object_id, b.owner, b.object_name, b.object_type); DBMS_OUTPUT.put_line(‘MERGE : ‘ || (DBMS_UTILITY.get_time – l_start) || ‘hsecs’); REVERSION; l_start := DBMS_UTILITY.get_time; SELECT * BULK COLLECTION IN l_tab FROM source_tab; FOR IN l_tab.first.. l_tab. LAST LOOP IS COMBINED INTO dest_tab A USING (SELECT l_tab(i).object_id AS object_id, l_tab(i).owner AS OWNER, l_tab(i).object_name AS object_name, l_tab(i).object_type AS object_type FROM dual) b ON (a.object_id = b.object_id) WHEN MATCHED, AND THEN UPDATE SET owner = b.owner, object_name = b.object_name, object_type = b.object_type WHEN MISMATCHED AND INSERT (object_id, OWNER, object_name object_type) VALUES (b.object_id, b.owner, b.object_name, b.object_type); FINAL FRUIT; DBMS_OUTPUT.put_line(‘ROW MERGE : ‘ || (DBMS_UTILITY.get_time – l_start) || ‘hsecs’); REVERSION; l_start := DBMS_UTILITY.get_time; SELECT * BULK COLLECTION IN l_tab FROM source_tab; FOR IN l_tab.first.. l_tab.last LOOP UPDATE dest_tab SET owner = l_tab(i).owner, object_name = l_tab(i).object_name, object_type = l_tab(i).object_type WHERE object_id = l_tab(i).object_id; IF SQL%ROWCOUNT = 0 INSERT dest_tab VALUES (object_id, OWNER, object_name, object_type) (l_tab(i).object_id, l_tab(i).owner, l_tab(i).object_name, l_tab(i).object_type); END YES; FINAL FRUIT; DBMS_OUTPUT.put_line(‘UPDATE/INSERT: ‘ || (DBMS_UTILITY.get_time – l_start) || ‘hsecs’); REVERSION; l_start := DBMS_UTILITY.get_time; SELECT * BULK COLLECTION IN l_tab FROM source_tab; FOR IN l_tab.first.. l_tab.last LOOP BEGIN INSERT INTO dest_tab (object_id, owner, object_name, object_type) VALUES (l_tab(i).object_id, l_tab(i).owner, l_tab(i).object_name, l_tab(i).object_type); EXCEPTION WHEN DUP_VAL_ON_INDEX UPDATE dest_tab SET owner = l_tab(i).owner, object_name = l_tab(i).object_name, object_type = l_tab(i).object_type WHERE object_id = l_tab(i).object_id; The end; FINAL FRUIT; DBMS_OUTPUT.put_line(‘INSERT/UPDATE: ‘ || (DBMS_UTILITY.get_time – l_start) || ‘hsecs’); REVERSION; The end; / MERGE : 119 hsecs ROW MERGE : 1453 hsecs UPDATE/INSERT: 1280 hsecs INSERT/UPDATE: 2443 hsecs PL/SQL procedure completed successfully. SQL>

The result shows that the straight MERGE instruction is an order of magnitude faster than its nearest rival. Update/insert performs almost twice the speed of insert/update and even performs the COMBINATION row by row.

Simply comparing the update/insert

and insert/update methods in isolation, we have to remember that comparisons will vary depending on the data in the table. If most of the data is not already present, the insert/update approach may be better. If most of the data is already present, the update/push approach will likely be better. If you are not sure, just use merge as it is clearer.

In addition to the direct MERGE instruction being faster, because it is a DML instruction, it can be easily executed in parallel to further improve performance, as long as your server can handle the additional load.

For more information, see:

MERGE MERGE

  • Declaration Enhancements
  • in Oracle Database 10g MERGE

Hope this helps. Greetings Tim…

Back to top.

admin