Re: how to efficiently delete records from large tables?

From: Anne <anneb_at_xs4all.netherlands>
Date: Sun, 31 Aug 2003 02:05:42 +0200
Message-ID: <vl2etsefqfulc5@corp.supernews.com>

> >
> Your WHERE clause doesn't use the ID columns, therefore it can't use
> the index to filter the rows returned in your subquery.
>

Thanks to other advice from this newsgroup I first changed the query to: delete from table_1 t1 where t1.id=(select t2.id from table_2 t2 where t1.id=t2.id and name='DELETE');
which finishes in 163 seconds (instead of hours)

After going back to using the in clause and using your advise I get: delete from table_1 t1 where t1.id in (select t2.id from table_2 t2 where t1.id=t2.id and name='DELETE');
which finished in 4.3 secs

Thanks! Received on Sat Aug 30 2003 - 19:05:42 CDT