Re: How to improve the deletion rate.

"Ed prochak" <ed.prochak_at_magicinterface.com> wrote in message news:4b5394b2.0312220943.6ca0ec0e_at_posting.google.com...
> 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

It is a local tem to India meaning a particular magnitude (like K for thousands). I do not remember what particular order of magnitude it is. Jim Received on Tue Dec 23 2003 - 05:23:11 CET