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.

How are you doing the UPDATE and MERGE statements? Can you post the statements you are using? It would also be useful to post any DDL including indexes on the affected tables. It is rather hard to answer your specific question with just a general knowledge of the problem.

Andy

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;

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

My guess as to why the merge goes faster is because the subselect in the merge is only done once for the entire process and in the update statement it is done for every row. Look at the access plans for both statements and see if you can find the difference.

Andy