Re: fastest way of deleting all rows in a table

Actually: deletes do generate a huge amount of redo - the complete before image of the deleted row is recorded in the redo log (except for any lob columns).

try using logminer on an old archived redo log file - contents of deletes are reported - this is long after the undo segment has been re-used for other purposes.

<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.
>
>
> >
>
>
> >Delete generates huge amounts of redo (read: I/O activity) -- all of
> >the redo necessary to recover every row deleted. Probably some other
> >overhead in marking blocks as empty and usable.
>
> Again : Deletes generates huge *undo* and little redo. That's quite
> logical : Undoing a delete essentially requires all the deleted rows'
> content to be stored in rollback segments. Redoing a delete is easy;
> you essentially just need to keep track of which rows were deleted.
>
> (With inserts, it is just the opposite.)
>
>
> >Drop / Create incurs large amounts of storage management activity as
> >well as updating the dictionary (and attendant redo acitivty)
>
> See above.
> >
> >Which do you think will be faster?
>
>
> - Kenneth Koenraadt
Received on Fri Feb 28 2003 - 16:25:12 CST