Re: how to efficiently delete records from large tables?

Anne wrote:

> > 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

Incorrect. And worse yet ... you should not be using the CBO with 9.2 unless you query is specifically optimized for the RBO.

The RBO is deprecated and will not be in any future versions of Oracle. The time to learn how to not use it ... is now.

>
> > Statistics current?
> Yes, but that is not important for primary (=unique) indexes is it?

Not if RBO but critical if CBO.

>
> > 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

Sybrand showed you how to not use IN.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)