MERGE: MAKE Your UPSERTs quick!!!

Hi guys... I am back... this time as a Advanced DB2 9.7 DBA... Thanks to Sathy for helping me prepare for the exam and Beth Flood for giving me a free voucher (otherwise I never had guts to put 5k on stake for my capabilities)... But very soon an all new DB2 is expected I am hope it will have hell lot of new features and I will have to upgrade or take the certification exams from scratch...

Well recently I have been working with some scenarios where people came to me with an update statement which was working very slow... When I looked at it, I said "I need to tell people there is something like MERGE"... There was one thing common, all the statements were having correlated sub-queries for set value... I replaced their queries with a MERGE and it worked like wonder...

Let's see some example...

UPDATE Table1
SET T1Col1=(select T2Col1 from Tables2 where Table1.T1IDPK=T2IDPK);

So the problem with this query is the inner query will be executed for every row of table Table1...

So I wrote them a query MERGE, which shall first create a HASH JOIN and then do an RID based update:

MERGE INTO Table1
USING Table2 on (Table1.T1IDPK=Table2.T2IDPK)
WHEN MATCHED THEN UPDATE
SET Table1.T1Col1=Table2.T2Col1;

Another scenario was of a process where a scheduled process will pick up rows from a table (which has designated pagenumbers/rownumers) in batches and after processing certain number of rows in every batch the scheduled process will sleep... Remaining rows shall be renumbered and partitioned in batches...

UPDATE PagedTab t1
set SEQNUM=(select row_number() over(order by PagedTab_IDPKColumn) from PagedTab t2 where t1.PagedTab_IDPKColumn=t2.PagedTab_IDPKColumn);

So I just changed it to below:

MERGE INTO PagedTab t1
USING (select PagedTab_IDPKColumn,row_number() over(order by PagedTab_IDPKColumn) as rnum from PagedTab ) t2
on t1.PagedTab_IDPKColumn=t2.PagedTab_IDPKColumn
WHEN MATCHED THEN UPDATE
SET t1.SEQNUM=t2.rnum;

I have simulated the below scenrio to make it more easy to be understood as the actual was more complex...
Third place was where there was a Mater-Detail kind (Dependent Entity) kind of relationship and some details were stored in Master Table and for every IDPK in master table one and only one record existed in detail table... ACCOUNTID and LASTACCESSDATE and STATUS were some of the details... some one wanted to first find out the LATEST record (based on date) from detail table for every ACCOUNTID and then find out primary key for that record and then for that PK whatever status is found in detail had to bee copied to master... So here goes the query people will write in first attempt...

UPDATE MasterTable outTab
SET STATUS=
(select STATUS from DetailsTable t2 join
(select ACCOUNTID, MAX(LASTACCESSDATE) DT from DetailTable group by ACCOUNTID)t1
on t1.ACCOUNTID=t2.ACCOUNTID and t2.LASTACCESSDATE=t1.DT and t2.IDPK=outTab.IDPK
)temp;


Actually the query which was written had used one more IN clause which I have converted to JOIN to avoid more confusion...

I modified it...

MERGE into MasterTable t1
USING
(select IDPK, DENSE_RANK() over(PARTITION BY ACCOUNTID order by LASTACCESSDATE) rn, STATUS from DetailsTable) t2
ON (t1.IDPK=t2.IDPK and rn=1)
WHEN MATCHED THEN UPDATE
SET STATUS=t2.STATUS;

And this avoided a join and sort which was being done for row... Did a single sort a hash join and an update based on RID... Wow!!!

One final instance where I saw something like this:

UPDATE TableX X
SET
Col1=(select Col1 from TableY Y where X.IDPK=Y.IDPK),
Col2=(select Col2 from TableY Y where X.IDPK=Y.IDPK),
Col3=(select Col3 from TableY Y where X.IDPK=Y.IDPK),
Col4=(select Col4 from TableY Y where X.IDPK=Y.IDPK)
;

and then

INSERT INTO TableX (COl1, COl2, COl3, COl4)
Select COl1, COl2, COl3, Col4 from TableY Y
where not exists (select 1 from TableX X where X.IDPK=Y.IDPK);


and finally...

DELETE FROM TableX X where not exists (select 1 from TableY where X.IDPK=Y.IDPK);

I will leave it upto the readers to interpret the purpose of these statements... I will just give an alternate query...


MERGE INTO TableX X
USING TableY Y on (X.IDPK=Y.IDPK)
WHEN MATCHED then UPDATE
SET
X.COl1=Y.COl1,
X.COl2=Y.COl2,
X.COl3=Y.COl3,
X.COl4=Y.COl4
WHEN NOT MATCHED THEN INSERT(COl1, COl2, COl3, COl4)
VALUES (Y.COl1, Y.COl2, Y.COl3, Y.COl4)
;

This has combined the UPDATE and INSERT statements and is much more efficient cause the hash join is executed once as opposed to execution of 4 correlated sub-queries for every row in TableX for Update and then for INSERT an anti-join is performed...

The delete can not be optimized or combined in merge which is a little disappointing and I hope to see this feature in DB2 very soon... A clause of "WHEN NOT MATCHED BY SOURCE" exists in SQL Server using which I could have just said:


MERGE INTO TableX X
USING TableY Y on (X.IDPK=Y.IDPK)
WHEN MATCHED then UPDATE
SET
X.COl1=Y.COl1,
X.COl2=Y.COl2,
X.COl3=Y.COl3,
X.COl4=Y.COl4
WHEN NOT MATCHED THEN INSERT(COl1, COl2, COl3, COl4)
VALUES (Y.COl1, Y.COl2, Y.COl3, Y.COl4)
WHEN NOT MATCHED BY SOURCE THEN DELETE
;


Well this blog is bloated too much and I do not want to bore people, you can have multiple WHEN MATCHED and WHEN NOT MATCEHD clauses and combine with another condition (AND col1=col2) and have different different action for every branch, hence you will write one single statement instead of writing one update for every condition... I will try to put some examples sometime...

BTW thanks to all my readers... I feel lucky and honored to be listed in "My Favorite DB2 Blogs" by Troy Coleman @ db2tutor

If you are reading this Mr. Coleman, trust me db2tutor and db2geek are my frist reference points for any query... :) and thanks for putting me on the list...