Problem deleting million+ rows from table
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.
Do you like redundancy why 2 posts?
Click Here to Expand Forum to Full Width