From: fbortel@nescape.net   
      
   Tuhin Kumar wrote:   
   > Hi,   
   >     I have a requirement on improving the deletion rate on on records   
   > of a table.   
   > The table contains 5 million records, but since deleting everything   
   > matching the condition at one go was giving the ROLLBACK segment   
   > allocation error, the query was called in a loop and deleting 2000   
   > rows at one interation.   
   > My query is:   
   > DELETE FROM Table1 WHERE TimeStamp >= TO_DATE('2003-11-30 18:30:00',   
   > 'YYYY-MM-DD HH24:MI:SS') and TimeStamp <= TO_DATE('2003-12-18   
   > 18:29:59', 'YYYY-MM-DD HH24:MI:SS') AND ROWNUM < 2000   
   >   
   > The performance I am getting is deletion of 1.5 lakhs records   
   > happening in 10 minutes. I am looking forward to 10 lakhs records in   
   > 10 minutes.   
   >   
   > Thanks,   
   > Tuhin   
      
   Have your dba create partitions, based on date.   
   Drop partion takes a fraction of what you are trying to accomplish.   
   Typical example of an application not designed for a lifetime.   
      
   If you don't get this, decrease your time stamp window, and run   
   until completion (e.g. delete 1 day at a time)   
   --   
   Merry Christmas and a Happy New Year,   
   Frank van Bortel   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)