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