Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: fastest way of deleting all rows in a table

Re: fastest way of deleting all rows in a table

From: Ed Stevens <nospam_at_noway.nohow>
Date: Fri, 28 Feb 2003 17:20:17 -0600
Message-ID: <2brv5v8ere7i8c0q8g98bn66cnqcdrn0n4@4ax.com>


On Fri, 28 Feb 2003 19:20:31 GMT, Kenneth Koenraadt wrote:

>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

Yes, I need to be more careful with my symantics. I'd hate it if my doctor said 'heart' when he meant 'appendix' and blew it off by saying "it's just symantics!"

But that being the case, the basic point is that 'truncate' generates very little -- dare I say *almost no*? -- physical disk activity, especially when compared to delete or drop. And while it's true that it is recoverable (again, in the strictest use of the terminology) it cannot be rolled back, so that your 'recovery' option would be to do a database recovery to the point in time just before you issued the drop. Received on Fri Feb 28 2003 - 17:20:17 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US