Re: Delete whole table in one go versus batches

I love your scenario :) Will give it a try with my donut-loving DBA :)

Richard Foote wrote:

> Hi Ender,
>
> OK, got the scenario. Here's my solution.
>
> set timing on
>
> Walk to local shops. 00:05:13.29
>
> Depending on DBA, buy donuts, chocolates, David Bowie CD... 00:03:54.45
>
> Walk back to work. 00:04:36.02
>
> Walk upstairs to DBA area. 00:01:07.09
>
> Ask very nicely, please mate (show goodies), could you please truncate
> this table, I need to remove all it's data and currently due to your
> excellent security measures I can only perform this operation via a
> delete which could take hours, produce mountains of redo, cause your
> well tuned rollback segments to go nuts (if indeed they're big enough to
> cope), put stress on the server and cause heaps of I/Os. It does all
> sound very inefficient for the 500,000 rows I need to blow away. Pretty
> please (show goodies again). 00:01:02.14
>
> DBA truncates table (assuming reuse storage). 00.00.02.36
>
> Total Time: 00:15:56.15
>
>
> VS
>
> delete big_table; 02:34:53:04
>
>
> Based on this scientific study, the first option would be approximately
> 10 times faster (although there could be some differences due to local
> differences and configuration).
>
> My advice ;)
>
> Richard
>
>
> Ender Wiggin wrote:

>> 
>> Coughs, not the owner of the table, access is given to
>> insert/select/update/delete for the table. So unless my
>> dear DBA grants me to drop any table, I am kind of stuck...
>> 
>> Richard Foote wrote:
>> 
>> > Hi Ender,
>> >
>> > Love to hear what those various reasons are ...
>> >
>> > Love to hear just one.
>> >
>> > Richard
>> >
>> > Ender Wiggin wrote:
>> >>
>> >> Are there any performance gains to delete rows in batches versus one
>> >> delete statement if I don't cause the rollback segments extend ? Thnx
>> >>
>> >> Rows > .5 million
>> >>
>> >> PS> Cannot use truncate for various reasons...