Timing for Operations

[cyrus]

I want to know among the operations namely DELETE TABLE and TRUNCATE TABLE which is faster and why.Or is it that both takes the same time.

[msis2004]

I am new to Oracle DBA but I found the following in the book I am currently reading. It explains the difference between delete and truncate very well.

1)Truncate statement is similar to Delete statement without a where clause. Truncate is very fast on both large and small tables. Delete will generate undo information, in case a rollback is issued, but Truncate will not generate undo.

2) Truncate resets the high-water mark (I don't know what this means) in the table and all indexes. since full-table scans and index fast full scans read all data blocks up to the high-water mark, full-scan performance after a Delete will not improve; after a Truncate, it will be very fast.

3) Another key difference is that Truncate is DDL and Delete is DML.

Hope this helps!

[nagivijay]

HI

Delete and truncate both are useful in deleting the records, however when ever you had issued an delete clause it just marks the records in the table as deleted, the memory(bytes) will be the same,at this point you can perform commit and rollback transaction statement controls. where as truncate clause removes the entire records. the major advantage of truncate clause is it actually removes the records from the table so it releases memory. here you can't peform the transaction statement controls. hope this will help for you.

VJ