|
Delete vs Truncate
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!
|