Re: how does truncate table work?

hopehope_123_at_yahoo.com (utkanbir) wrote in message news:<f6c90ebe.0308182200.3f51f194_at_posting.google.com>...
> Dear Gurus,
>
> I am new with oracle database but strong experience with informix .
> In one of the oracle training which i have recently involved , the
> lecturer asked the difference between delete and truncate table.
> Although i did not exactly know , i can guess that , truncate table
> runs fast because instead of deleting each records , it can invalidate
> the table by cahnging a flag in data file headers or etc. ( thinking
> of informix architecture) But the lecturer said truncate table also
> deleted records but did not generate redo log data thats why it was
> faster.
>
> Is it possible to be very fast for example deleting 100000000 rows
> without creating redo log entry. I had better drop & create it?
>
> I will be appreciate if someone comments about this.
>
> Kind Regards,
> hope

truncate resets the High Water Mark of the table. It doesn't check constraints and it is considered DDL, so it commits automatically. As the HWM is reset, the only redolog it generates is the redolog for the DDL transaction. Time to ask your lecturer the exact spot where it says truncate does *delete* records. It doesn't

Sybrand Bakker
Senior Oracle DBA Received on Tue Aug 19 2003 - 04:25:52 CDT