By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,590 Members | 2,174 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,590 IT Pros & Developers. It's quick & easy.

Delete vs. Merge

P: n/a
I need to delete some rows from a table. The rows to delete are
uniquely identified in a second table. I realize that I have two
options here:

1) DELETE FROM tableA WHERE ...

2) MERGE INTO tableA ... USING tableB ... ON .... WHEN MATCHED DELETE
....

I was wondering which of the two options would provide best performance
overall. I would welcome any suggestions.

Thanks!

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
de*******@yahoo.com wrote:
I need to delete some rows from a table. The rows to delete are
uniquely identified in a second table. I realize that I have two
options here:

1) DELETE FROM tableA WHERE ...

2) MERGE INTO tableA ... USING tableB ... ON .... WHEN MATCHED DELETE
...

I was wondering which of the two options would provide best performance
overall. I would welcome any suggestions.

Thanks!

On average I would expect DELETE to be faster.
The reason is that MERGE has to raise an error if it tried to delete the
same row twice. This is extra work.
DELETE FROM T WHERE EXISTS(SELECT 1 FROM S WHERE T.c1 = S.c1)
Doesn't care wether S.c1 is unique.
If S.c1 and T.c1 are both keys MERGE may get up to the same speed
depedending on your fixpack.
MERGE can't be faster though :-)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.