Re: SQL Deletes without rollback

From: D Rolfe <dwrolfeFRUITBAT_at_orindasoft.com>
Date: Fri, 30 Apr 2004 16:17:53 +0100
Message-ID: <40926E21.30508@orindasoft.com>

Richard,

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

You are right in that in most (>90%) situations the one big SQL statement beats several smaller ones.

I have however seen several situations where breaking down a really big transaction into chunks did lead to it running faster.

Bear in mind that I'm not telling him it *will* be faster - I'm suggesting he try a test and see for himself....

On reflection my rollback segment theory is probably wrong.... :)

David Rolfe
Orinda Software
Dublin, Ireland Received on Fri Apr 30 2004 - 10:17:53 CDT