Re: how to efficiently delete records from large tables?

"Anne" <anneb_at_xs4all.netherlands> wrote in message news:vl1hd99ffmdp59_at_corp.supernews.com...
> 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
>
>

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

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address