Re: How to improve the deletion rate.

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" <tkumar_at_ipolicynet.com> wrote in message news:e4ad76f5.0312220320.6bb0ab35_at_posting.google.com...
> 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