Results 1 to 2 of 2

Thread: Merge vs Update

  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Merge vs Update

    Hi all,
    I m new to db2 and I am working with db2 9.1V. I experienced that Merge statement is faster when compared to update.
    For updating 15 million records using merge took 17.23 mins and Update statement took 30.11 mins.

    Can anyone explain me the reason, whats the strategy that makes Merge faster than Update, even though in Merge also we are using the Update statement only.
    Also can any one tell how mergestatement works in the backend?

    Thanks in Advance.

  2. #2
    Join Date
    Sep 2010
    Posts
    2

    Merge Vs Update

    The following are the update and the merge statements. In both the tables the Index is created on the column ACCT_ID. The total number of records to be updated is 15,636,417

    ----- Merge statement

    MERGE TABLE_A TA
    USING (SELECT TB.ACCT_ID,
    MAX(CAST(TB.BDT AS DATE)) BDATE
    FROM TABLE_B TB
    WHERE TB.AGE BETWEEN 18 AND 100
    GROUP BY TB.ACCT_ID) TB1
    ON TA.ACCT_ID = TB1.ACCT_ID
    AND TA.DOB IS NULL
    WHEN MATCHED THEN
    UPDATE SET TA.DOB = TB1.BDATE;


    ----- Update statement

    UPDATE TABLE_A TA
    SET BD.DOB = (SELECT MAX(CAST(TB.BDT AS DATE)) BDATE
    FROM TABLE_B TB
    WHERE TB.AGE BETWEEN 18 AND 100
    AND TA.ACCT_ID = TB.ACCT_ID
    GROUP BY TA.ACCT_ID)
    WHERE TA.DOB IS NULL;

    --------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •