Re: fastest way of deleting all rows in a table

From: Stan <stan0074_at_yahoo.com>
Date: Fri, 28 Feb 2003 16:56:46 GMT
Message-ID: <3E5F9498.3090704@yahoo.com>

Ed Stevens 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 . . . .
>
> Truncate resets the high-water mark -- a simple pointer change. It
> doesn't generate any redo, and so is not recoverable.
>

sorry, its still generates redo but it would be very minimal (segment header changes...fet$/uet$ changes, if using dictionary managed tablespace)

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

-- 
-Stan

Received on Fri Feb 28 2003 - 10:56:46 CST