Re: How to improve the deletion rate.

From: Eric Manseau <>
Date: Mon, 22 Dec 2003 12:41:26 -0500
Message-ID: <bs7aag$pnu$>

Hi Tuhin,

You should try to refine the where clause. You may use a BETWEEN clause instead of two comparaison.

DELETE FROM Table1 WHERE TimeStamp BETWEEN TO_DATE('2003-11-30 18:30:00', 'YYYY-MM-DD HH24:MI:SS') and TO_DATE('2003-12-18 18:29:59', 'YYYY-MM-DD H24:MI:SS') AND ROWNUM < 2000

Also it can be a good idea to create an index the field TimeStamp if it is not already.

"Tuhin Kumar" <> wrote in message
> 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
Received on Mon Dec 22 2003 - 18:41:26 CET

Original text of this message