Re: how to efficiently delete records from large tables?

>
> Try
> delete from table_1 t1
> where exists
> (select 'x' from table_2 t2
> where t2.id = t1.id and
> name='DELETE');
>
> t1 will still be subject to FTS though as there are no sensible clauses.
If
> you want to keep the subquery try using the pushd hint or the hash_aj hint
>

This was much better, the explain plan now indicated use of the index. Since execution was still slow, I noticed that I accidently left a foreign key constraint active referencing table_1.id. After disabling the constraint, the query now runs in 163 seconds, much better than the previous multi-hour version.

Based on your answer I finally rewrote 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');

Thanks for the help.

Anne Received on Sat Aug 30 2003 - 18:42:50 CDT