About Me

My photo
Singapore, Singapore, Singapore
I am currently working as a DB Consultant with Ashnik. We are into Open Source and Cloud based enterprise class solutions. Prior to this I was working as a DBA @ Misys India (Banking Services). Working on IBM DB2, Oracle Microsoft SQL Server. Selected as IBM Champion for Data Management 2011. I like bit of photography, cycling, poetry and I just love gadgets!!

Connect to me

See my profile on LinkedIn Visit my Ashnik Website

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...

12 comments:

  1. How can I delete the not matched record. After the update and Insert I have written like this

    DELETE where id = 0 ;

    I am gettin this err:

    An unexpected token "DELETE where id = 0" was found
    following "l, null, null, null)". Expected tokens may include: "".
    LINE NUMBER=301. SQLSTATE=42601

    ReplyDelete
  2. Hey what do you intent to do?? Do you want to delete the records which are not matched with any record in SOURCE (SOURCE is the table/resultset one mentions in USING clause)? That facility is not there in DB2. :(
    SQL Server allows you doing that.

    It will be a nice to have feature in DB2.

    ReplyDelete
  3. Hi ,

    I'm trying to execute a merge as a prepared statement from java.
    It is a simple merge wherein a record is inserted if an id is not present or the same is updated.
    But when I run the application the merge executes fine , that is without any errors but I don't see any insert or update in the concerned tables.

    Would you be able to help please?

    ReplyDelete
    Replies
    1. If I were in your place, I would have tried 2 things. One is to execute the statement from an interactive client e.g. SQL Work Bench or Toad and see if it affects any records. 2nd I would have tried to execute the join of merge statement e.g. (pertaining to example given in my post)

      select tableX_columne_list, tableY_column_list from TableX X
      left outer join TableY Y on X.IDPK=Y.IDPK

      this statement will give you all the rows which are eligible for update and those which are eligible for insert (where the columns of TableY are null in output).

      Hope this helps

      Delete
  4. Hi ,

    Thanks so much for your reply.That helped.
    I was also trying something as follows

    MERGE INTO TABLE USING (DATA)
    ON (CONDITION)
    WHEN MATCHED THEN
    SELECT * FROM FINAL TABLE (INSERT Stmt)
    WHEN NOT MATCHED THEN
    SELECT * FROM FINAL TABLE (UPDATE Stmt)

    And this didn't work.Found a workaround.
    But would like to know what I'm doing wrong.
    Could you please advice?

    ReplyDelete
    Replies
    1. Pratik,

      I did not quite get the usage of SELECT * from FINAL TABLE. I am not aware of this syntax (i.e. usage of select in then clause) for merge statement.

      Probably if you can explain me what you intend to do, I can help.

      Delete
  5. Hi Sam,
    Thanks for your reply.Actually I gave it a thought.It seems I had gotten the whole concept of merge incorrectly.But now after trying out different options with merge I'm a bit comfortable with the ways to use it.

    Anyways are there other means to contact you.Cause many times it so happens that Im stuck with some db2 related issue and just cant find a way out.And could really use a DBA opinion in these situations.

    Thanks
    Pratik

    ReplyDelete
  6. And could really use a DBA opinion in these situations.

    I have always advocated involving a DBA right from requirement phase... What you can get from forums and blogs can never be as good as having a dba involved in your development... :P

    But if you are stuck somewhere you can ping me on my mailID sameer.kasi200x@gmail.com or you can ping me on twitter...

    ReplyDelete
  7. Hi Sam,

    I have a query with just SELECT fields from Table where condition.

    Is there a way to optimize this query?

    i can't use FIRST FETCH ROW option as the number of records cannot be determined.

    ReplyDelete
    Replies
    1. - Indexing is one way.
      - Reorg and runstats are two other options.
      - Reorg by the index in where clause.

      Delete
  8. Hi
    Is there a way where I can use a commit count when using a merge statement. I have to update/insert around 40 lakhs records per day into an archive table.

    ReplyDelete
  9. You can use pagenated queries.

    Or you can try use partitioned tables. It will become easier to move them around from live to archive tables!

    ReplyDelete