Re: how to efficiently delete records from large tables?

Anne wrote:

> Hi,
>
> I have a speed problem deleting data from one table based on information in
> another table.
>
> I have two tables, 1.9 milion records each. Both have a primary key on field
> 'id'.
>
> TABLE_1:
> Name Type Nullable Default Comments
> ------- ------------- -------- ------- --------
> ID NUMBER(25)
>
> TABLE_2:
> Name Type Nullable Default Comments
> ------- ------------- -------- ------- --------
> ID NUMBER(25)
> NAME VARCHAR2(50)
>
> Now if I use:
> delete from table_1 t1 where t1.id in (select id from table_2 where
> name='DELETE');
>
> it takes hours to execute. (in the query explain plan I see that Oracle hash
> joins the tables using full table access on both tables, I would think that,
> since primary keys are used, Oracle should use the primary key indexes, but
> it doesn't).
>
> What can I do to increase execution speed?
>
> Thanks,
>
> please note that the reply e-mail address is disfigured to block automated
> bulk e-mail. The abbreviation for 'netherlands' is 'nl'.
>
> Anne

What version of Oracle?
Which optimizer?
Statistics current?
Have you run explain plan and you know the indexes are not being used or are you just guessing?
Why are you using 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)