delete from table where in, problem

Ian

Hi,
I have a problem with delete using where in clause. This is a query:

delete from tab1 where id not in (select id from tab2)
I calculated costs using select instead of delete:

select count(*) from tab1 where id not in (select id from tab2)
select count(*) from tab1 t1 left outer join tab2 t2 on t1.id = t2.id
where t2.id is null

and query with left outer join is 50x faster. But I can't use join in
delete.

I'm using DB2 v7.2 on Windows.
Can anyone help me speed up this delete query?
Thanks,
Filip

Apr 7 '06 #1

Sumanth

I am suspecting "not in" could result in a full table scan.(not sure though)
Can you try this
delete from tab1 where id in (select id from tab1, tab2 where tab1.id =
tab2.id)

-Sumanth

"Ian" <ia******@yahoo.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...

Hi,
I have a problem with delete using where in clause. This is a query:

delete from tab1 where id not in (select id from tab2)
I calculated costs using select instead of delete:

select count(*) from tab1 where id not in (select id from tab2)
select count(*) from tab1 t1 left outer join tab2 t2 on t1.id = t2.id
where t2.id is null

and query with left outer join is 50x faster. But I can't use join in
delete.

I'm using DB2 v7.2 on Windows.
Can anyone help me speed up this delete query?
Thanks,
Filip

Apr 7 '06 #2

Tonkuma

delete from tab1
where NOT EXISTS
(SELECT * from tab2 WHERE tab1.id = tab2.id)

I thought that at least index for id of tab2 would be required for good
performance.

Apr 7 '06 #3

Sumanth

try this one:
delete from tab1 where id in ( select id from tab1 except select id from
tab2)

I have found the cost of this to be much better than others, but the except
operartion similar to a minus opearation
can take more time depending on the size of the tables and whether they are
sorted.

-Sumanth

"Sumanth" <Su************@sas.com> wrote in message
news:e1**********@foggy.unx.sas.com...

I am suspecting "not in" could result in a full table scan.(not sure
though)
Can you try this
delete from tab1 where id in (select id from tab1, tab2 where tab1.id =
tab2.id)

-Sumanth

"Ian" <ia******@yahoo.com> wrote in message
news:11**********************@z34g2000cwc.googlegr oups.com...

Hi,
I have a problem with delete using where in clause. This is a query:

delete from tab1 where id not in (select id from tab2)
I calculated costs using select instead of delete:

select count(*) from tab1 where id not in (select id from tab2)
select count(*) from tab1 t1 left outer join tab2 t2 on t1.id = t2.id
where t2.id is null

and query with left outer join is 50x faster. But I can't use join in
delete.

I'm using DB2 v7.2 on Windows.
Can anyone help me speed up this delete query?
Thanks,
Filip

Apr 7 '06 #4

Ian

Thank you all for your help. I found that Sumanth's last query works
much faster then the others. In addition I dropped foreign keys during
delete and then everything worked much faster.
Filip

Apr 10 '06 #5