Re: fastest way of deleting all rows in a table

<Kenneth Koenraadt> wrote in message
news:3e5fb29d.4023345_at_news.inet.tele.dk...
> On Fri, 28 Feb 2003 07:38:56 -0600, Ed Stevens <nospam_at_noway.nohow>
> wrote:
>
> >On Fri, 28 Feb 2003 18:48:39 +0530, ShravanaKumar
> ><shravanakumar.ks_at_siemens.com> wrote:
> >
> >>Hi All,
> >>
> >>We are using Oracle 9i on Solaris 8.
> >>
> >>I would like to know, which is the fastest way of deleting all rows from
> >>a table and why ?
> >>
> >> a. Truncate table .....
> >> b. delete table .....
> >> c. drop table .... ( an re-create the table afterwards )
> >>
> >>can some one shed light on this ?
> >>
> >>Thanks,
> >>
> >>Best Regards,
> >>Shravana Kumar.
> >>
> >>
> >Others, please correct me if I'm wrong . . . .
>
> Well, here goes :
>
> Generally , you are confusing redo with undo/rollback.
>
>
> >
> >Truncate resets the high-water mark -- a simple pointer change. It
> >doesn't generate any redo, and so is not recoverable.
>
> 1) It does (almost) not generate any *undo*, which means it can't
> be*rolled back*.
>
> 2) It does in fact generate (a little) redo, because redoing "truncate
> table" is very simple. Truncate is fully recoverable.
>
> 3) It does more than resetting the HWM, e.g. it cuts off all extents
> except the initial.
> This in case of "truncate table <table> ...DROP storage".
> With the "KEEP storage" option, it keeps the HWM.

Hi Kenneth,

Don't confuse keeping the allocated extents (for subsequently reused) to the resetting of the HWM.

Truncate *always* resets the HWM.

Cheers

Richard Received on Fri Feb 28 2003 - 17:48:55 CST