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

delete from table where in, problem

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a

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

P: n/a
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

P: n/a

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

P: n/a
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

This discussion thread is closed

Replies have been disabled for this discussion.