DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2008

    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.

  2. #2
    Join Date
    Nov 2002
    Mooresville, NC
    Do you like redundancy why 2 posts?
    A performance engineering forum

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.