Wednesday, November 22

Oracle merge-statement

The merge statement has been in Oracle since version 9, but is still not very widely used.
When looking at data loading and merging code I think the most common bit of PL/SQL I've seen is the "merge" statement written in pl with slow and boring if-statements to see check if an entity exists in a table and if it exists the pl-code will do an update, otherwise it will do an insert.
Two words, super slow.

A much better solution is to use the merge-statement which does exactly that, it's basically
"if exists then update else insert". Merge data from one data entity to another.
The basic concept is sort of a join between the loading entity and the destination table.
You can load from a normal table or from select query.

Example:
SQL> select * from emp_load;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- -------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1450 1400 30
7788 SCOTT ANALYST 7566 19-APR-87 3500 20
7902 FORD SALESMAN 7698 03-DEC-81 3000 1300 20
7100 CHARLES CLERK 7788 22-NOV-06 1150 10
7302 SALLY CLERK 7902 22-NOV-06 1300 30

SQL> MERGE INTO emp e USING emp_load l
ON (e.empno=l.empno)
WHEN MATCHED THEN
UPDATE SET
e.ename = l.ename,
e.job = l.job,
e.mgr = l.mgr,
e.hiredate = l.hiredate,
e.sal = l.sal,
e.comm = l.comm,
e.deptno = l.deptno
WHEN NOT MATCHED THEN
INSERT VALUES (
l.empno, l.ename, l.job,
l.mgr, l.hiredate,
l.sal, l.comm, l.deptno
)
/

5 rows merged.

SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- -------- ----------
7100 CHARLES CLERK 7788 22-NOV-06 1150 10
7302 SALLY CLERK 7902 22-NOV-06 1300 30

7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1450 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3500 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD SALESMAN 7698 03-DEC-81 3000 1300 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10

16 rows selected.
If you want to load from a query you can use this syntax:
MERGE INTO emp e USING 
(select * from emp_load
where hiredate > sysdate - 7
) l
ON (e.empno=l.empno)
...
Check out the merge docs .

14 comments:

Laura said...

Thanks--very helpful! I was toying with the idea of doing a pure delete/re-insert, but I think a MERGE may be faster for the amount of data I'm handling, after reading your entry. Thanks again.

Anonymous said...

How does this work with large volumes of data? Or is the following better for large amounts of data:


truncate some_table;
insert into some_table as
select *
from other_table;

Jeff Parker said...

Very insightful and practical advice.

A couple observations for Oracle loading from experience in a VLDB installation.

You can use Oracle External Tables combined with MERGE and/or INSERT to easily load data from a flat file external source. Breaking up the data into multiple data files referenced by the external table will allow Oracle to read them in parallel.

I've found that MERGE can be slow if your table has a large number of indexes. Periodic reordering of the PK and ordering of the data to load by PK will help with large volumes.

In some cases with large tables and you're changing more than 20% of the records it can actually be faster to create a new table and then flip the table-names to put the new table into production.

Anonymous said...

can you do an update if it doesnt match then ignore if it matches?

cheers

Alan said...

Merge works great if you're doing an insert/update, but what if I have A0abc and I need to result in A1def and A2ghi where my key is A#? In other words, how can I tell it to delete the key that didn't match, without it affecting B#?

Anonymous said...

eian, yes, you can use both the "WHEN MATCHED..." and the "WHEN NOT MATCHED..." statements or either one of them by themselves.

Anonymous said...

Nice article, especially the one line summary of the MERGE statement

"if exists then update else insert"

AG said...

how to merge records of datatable and physical database table from c#.net.
One idea is first create a temp table and insert records of datatable, then apply merge statement on temp table and physical database table.
but i don't want to create temp table.
I have a very large table consist millions records.


Any idea...
Thanks in advance..
~pramod

PD said...

Pramod, did you find a solution yet? -PD

Stew said...

Thanks for this example. I had never noticed the USING clause in any other examples, so never could get it to work.

Thought that may say more about my reading skills than your example??? :-/

Rajesh Shah said...

Hi that was a nice piece of advice...is there anything we have to merge records in the same table?

rpungin said...

Very good example. Thanks!

Unknown said...

Hi,

I have a need to merge data from 2 databases (same schema). Can the merge statement be used to merge data from the same table name in different databases? I would in this case be adding data from Oradb2 into the existing data in Oradb1 for example where both have the same schema, but different records, keys are managed so no collisions etc.

Andrea said...

Very helpful post. I have not used merge statement so far because I think its very slow but I do strongly agree with the solution that you have provided. I an sure it will be faster option to use on the process that I am working on.
oracle ebs r12