MERGE magic and madness

Using the MERGE statement, you can insert into, delete from and update the same table all at once: that is the magic. If you don’t pay attention, you can also make the database do a lot of unnecessary work: that is the madness!

I’ve blogged a lot about comparing tables, then using MERGE to synchronize them. Let’s see what MERGE can do alone, without a prior comparison step.

(Here is a list of my posts about comparing and synchronizing tables.)

UPSERT

When Oracle introduced the MERGE statement, it could only do inserts and updates, but not deletes. This is a common scenario in transactions, when you want a row of data to go into a table whether a row with the same key exists or not. Here is an example using my typical “compare and sync” test data:

select * from t_target where key_num = 1;

no rows selected

merge into T_TARGET o
using (
  select 1 KEY_NUM,
  trunc(sysdate) KEY_DATE,
  trunc(sysdate) VAL_TS
  from DUAL
) n
on (o.KEY_NUM=n.KEY_NUM and o.KEY_DATE=n.KEY_DATE)
when matched then update
  set VAL_TS=n.VAL_TS,VAL_STRING='UPDATE'
when not matched then insert
  (KEY_NUM,KEY_DATE,VAL_TS,VAL_STRING)
  values(n.KEY_NUM,n.KEY_DATE,n.VAL_TS,'INSERT');

1 rows merged.

select * from t_target where key_num = 1;

KEY_NUM	KEY_DATE	VAL_TS	VAL_STRING
1	2015-01-04 00:00:00	04-JAN-15 12.00.00.000000 AM	INSERT

The row was inserted. Now run the same MERGE statement again and select the same row:

KEY_NUM	KEY_DATE	VAL_TS	VAL_STRING
1	2015-01-04 00:00:00	04-JAN-15 12.00.00.000000 AM	UPDATE

Now the row has been updated.

Synchronize tables without DELETE

There may be times when you want to apply new or changed data to your target table, without removing any historical data. In this case, there is no need for any comparison code before the MERGE; the MERGE statement will do a RIGHT JOIN between the target and the source, and will either UPDATE or INSERT based on whether the target row was found or not.

As a reminder, my test tables T_TARGET and T_SOURCE have 300 rows each.

• T_TARGET has 10 rows not in T_SOURCE
• T_SOURCE has 10 rows not in T_TARGET
• There are 10 rows in both tables, but with different non-key values.

Since we are not doing deletes, there should be 10 updates and 10 inserts and that’s it.

Here is my first try:

merge into T_TARGET o
using T_SOURCE n
on (o.KEY_NUM=n.KEY_NUM and o.KEY_DATE=n.KEY_DATE)
when matched then update
  set VAL_TS=n.VAL_TS,VAL_STRING=n.VAL_STRING
when not matched then insert
  (KEY_NUM,KEY_DATE,VAL_TS,VAL_STRING)
  values(n.KEY_NUM,n.KEY_DATE,n.VAL_TS,n.VAL_STRING);

300 rows merged.

That’s funny, there should be only 20 rows merged! What happens if I run the statement again?

300 rows merged.

Madness!

I’ll bet you have figured out what’s wrong: I am updating rows in T_TARGET that are identical to T_SOURCE, in other words I am making Oracle do lots of work for nothing.

Fortunately, there is a way to filter out identical rows – with a WHERE clause.

merge into T_TARGET o
using T_SOURCE n
on (o.KEY_NUM=n.KEY_NUM and o.KEY_DATE=n.KEY_DATE)
when matched then update
  set VAL_TS=n.VAL_TS,VAL_STRING=n.VAL_STRING
  where 1 in (
    decode(o.VAL_TS,n.VAL_TS,0,1),
    decode(o.VAL_STRING,n.VAL_STRING,0,1)
  )
when not matched then insert
  (KEY_NUM,KEY_DATE,VAL_TS,VAL_STRING)
  values(n.KEY_NUM,n.KEY_DATE,n.VAL_TS,n.VAL_STRING);

20 rows merged.
(and the second time...)
0 rows merged.

Why DECODE?

When I compare the non-key values, I don’t know if they are NULL or not. One way to compare potentially NULL values is with DECODE: if both values are NULL then DECODE will return 0, and if only one is NULL then DECODE will return 1.