Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: fastest way of deleting all rows in a table
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.
>
>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?