Re: how to efficiently delete records from large tables?

> What version of Oracle?

9.2, but the query is so simple that I would think that it does not matter?
> Which optimizer?

Cost based
> Statistics current?

Yes, but that is not important for primary (=unique) indexes is it?
> Have you run explain plan and you know the indexes are not being used or
are you
> just guessing?

yes I ran the explain plan
> Why are you using IN?

In a select query I would use:
select * from table_1 t1, table_2 t2 where t1.id=t2.id and t2.name='DELETE'; or
select * from table_1 t1 join table_2 t2 on (t1.id=t2.id) where name='DELETE';

But I can not use:
delete from table_1 t1, table_2 t2 where t1.id=t2.id and t2.name='DELETE';

I assumed that the Oracle optimizer would figure out that it should join on id even if I used the IN()

Thanks to the answer of Sybrand I now believe that I should use: delete from table_1 t1 where t1.id=(select t2.id from table_2 t2 where t2.id=t1.id and name='DELETE');

Anne Received on Sat Aug 30 2003 - 18:55:30 CDT