Delete vs. Merge

[deebeetwo]

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!

[Serge Rielau]

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