Delete of very large table

[rwozniak71]

Hi Folks,

I have a very large table with approximately 150 million rows at 300 kb each. I need to delete 4.3 million or so "junk" transactions and the deletes are taking so long the delete process would need to run for a week or so. Any suggestions on how to speed things up? Here are some parameters that I hope will help:

--Oracle Version 8i running on AIX 5.0 on a reasonably powerful server.
-- PL/SQL cursor based on a table scan of a table containing one column of all the invalid keys that need to be deleted (i.e. it's a straight SELECT)
-- Explain plan run on the DELETE statement that executes after every fetch shows that the unique index created to enforce the pk constraint is being utilized.

Any help would be appreciated,

Rob

 

[kskid]

You can remove rows by using

Truncate table table_name

However, make sure that if there are triggers that delete rows that depend on the rows of the table, then truncate does NOT execute those trigers.

Also, if you use truncate, then it cannot be rolled back.

 

[rwozniak71]

Thanks, but I don't want to truncate this table, just delete a small number of rows.

 

[rrrkrishnan]

You can create a new table using 'create table tbl_nm' as select stmt;
Apply the filter in the select.
You can drop the original table and rename this new table.

 

[rwozniak71]

Thanks. That's actually the strategy I've decided on but am having trouble explaining to the DBA's why I need (temporarily) all that extra disk space (I estimate about 40GB).

 

[sem]

I suppose that deleting only 4.3 from 150 mln should be done explicitly by DELETE. But don't forget to choose an appropriate RBS for transaction as otherwise all your time would be spent in expanding it. You should also disable triggers and drop all indexes except the one (if any) usable for selecting those rows.

Regards, Dima

http://seminihin.narod.ru