Re: truncate (was: Re: Flameless Advice Please...)

A copy of this was sent to Robert Sullivan <bsullivn_at_home.com> (if that email address didn't require changing) On Mon, 03 Jan 2000 20:32:48 GMT, you wrote:

>RC wrote:
>> Also Oracle allows you to do things like TRUNCATE a table which removes all
>> rows from table much faster than say deletes which can take a long time.
>
>This brings up a question I've been wondering about for some time:
>why *is* 'truncate' so much faster than 'delete from'? I'm assuming
>it uses a different delete algoritm on the b-tree, but does anyone
>know for sure?
>

truncate just releases the space allocated to objects for reuse (either for that object to back into the free pool for all). It does not 'clean' the blocks, it does not generate rollback for the data being truncated -- it does not generate redo for the data being truncated. It just updates the data dictionary (for which rollback and redo is generated) and zaps the data out of existence.

delete on the other hand would visit each and every block and physically delete the data, generating redo/rollback for all.

>Thanks,
>bob :)

-- 
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
 
Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation