Re: Simple SQL statement is taking too long

Atif Ahmad Khan wrote:
>
> I am trying the following simple SQL statement and it takes way too
> long. Infact I have always terminated it after waiting for 15 minutes.
>
> delete from table1 where product not in
> (select a.product from table1 a , table2 b where
> a.product = b.product and b.price < 200 )
>
> table1 has 900 records and table2 has about 60,000, both are keyed on
> product and have no other indexes. The subquery returns 700 records. Just
> executing the subquery takes less than 5 sconds. I am not sure why it is taking
> so long to delete 200 records. Maybe my logic is flawed ?
>
>

You do not need to reference table1 in the subquery. Use:

delete from table1 a
where not exists
(select null from table2 b
where b.product = a.product
 and b.price < 200)

Your current version requires the full join on product code in the subquery, which produces a large number of rows. These must be put into an intermediate table which is then joied back to table1 in the outer (delete) block. Since there is no join condition between inner and outer blocks this must be done by a sort-merge.

Hope this helps.

Chrysalis. Received on Sun Apr 20 1997 - 00:00:00 CDT