Re: How to improve the deletion rate.

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