Re: how to efficiently delete records from large tables?

On Sat, 30 Aug 2003 17:41:57 +0200, "Anne" <anneb_at_xs4all.netherlands> 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
>
>

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

--
Bob Hairgrove
rhairgroveNoSpam_at_Pleasebigfoot.com