Re: How to improve the deletion rate.

tkumar_at_ipolicynet.com (Tuhin Kumar) 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,

Then ask your DBA to increase rollback segment space. That's the right answer.

> 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

Let me guess, NO INDEX on the Timestamp column. (so this does a full table scan)

the DELETE itself is inside another loop that doesn't do a COMMIT WORK; before executing the DELETE again.

(And I still wonder whether there are any foreign keys to this table, resulting in cascading deletes.)

>
> 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

I'm STILL curious: what is a "lakhs"?

ED Received on Mon Dec 22 2003 - 18:43:57 CET