Re: SQL Deletes without rollback

"D Rolfe" <dwrolfeFRUITBAT_at_orindasoft.com> wrote in message news:40922201.6050306_at_orindasoft.com...
>
> 3. Try adding "AND ROWNUM <= 100000" to the end of your delete
> statement. You may find it that doing 100000 rows at a time is faster,
> especially if you commit after each delete. One of the symptons of
> running low on rollback space is a dramatic slowdown as transactions get
> bigger and bigger.
>

Hi David

Why does running low on rollback space cause a dramatic slowdown ?

You either have the space in which case the transaction should carry happily along or you don't in which case the transaction will dramatically slowdown as a car dramatically slows down when it hits a brick wall, crash and rollback all the way.

Are you perhaps referring to dynamic allocation of extents, which should be relatively fast with LMT or dynamic resizing of datafiles, which should be sized correctly in the first place ?

Breaking up a logical transaction into smaller pieces for *performance* reasons is usually ill foundered and dubious at best.

Cheers

Richard Received on Fri Apr 30 2004 - 08:53:39 CDT