Re: fastest way of deleting all rows in a table

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 . . . .

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.

Drop / Create incurs large amounts of storage management activity as well as updating the dictionary (and attendant redo acitivty)

Which do you think will be faster? Received on Fri Feb 28 2003 - 07:38:56 CST