Problem deleting million+ rows from table

[anishpyne]

I have a DB table which is being fed data from a java program almost every second. In 24 hours an estimated 10-20 million rows get added to this table. Every night at 2.20 AM I have a java batch job which triggers a DML for cleaning up the data from this table which isnt required the next day anymore as follows:-

DELETE FROM table1 where status = 'O' and creation_time < '20 Jan 2008'

Bascially I need to cleanup all records having status as 'O' and which got inserted prior to today's midnight.

What is happening now is that the DML seems to running for hours 9-11+ hours. As a result the jobs for inserting data into these tables, retrieving data from these tables, etc other threads are getting extremely affected.

I have 3 composite indexes on this table, so my guess is the time is getting eaten up for generating re-do logs, etc.

Please suggest on how I can optimize this deletion. Any optimization which helps in getting the cleanup done within 1-2 hours would be great.

Note that I cannot have the batch job inserting data into the table stopped while the cleanup happens.

[malay_biswal]

Do you like redundancy why 2 posts?