how to efficiently delete records from large tables?

From: Anne <anneb_at_xs4all.netherlands>
Date: Sat, 30 Aug 2003 17:41:57 +0200
Message-ID: <vl1hd99ffmdp59@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 Received on Sat Aug 30 2003 - 10:41:57 CDT