Problem deleting million+ rows from table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Problem deleting million+ rows from table

  1. #1
    Join Date
    Jan 2008
    Posts
    4

    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
    Location
    Mooresville, NC
    Posts
    349
    Do you like redundancy why 2 posts?
    http://www.perf-engg.com
    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