Re: Delete whole table in one go versus batches

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...
Received on Mon Aug 19 2002 - 23:08:12 CDT