Is TRUNCATE TABLE t same as DELETE FROM t?
February 23rd, 2006 By Eddie Awad
Even though they both result in removing all rows from a table, there are major differences between the two. To summarize:
TRUNCATE TABLE t
- Issues an implicit COMMIT
- You cannot ROLLBACK a TRUNCATE statement
- Deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter
- Sets the NEXT storage parameter to the size of the last extent removed from the segment by the truncation process
- If the REUSE STORAGE clause is used, the space from the deleted rows allocated to the table is retained. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations
- Resets the high water mark of the table
- Can be more efficient than dropping and re-creating a table
- Can be faster than removing all rows with the DELETE statement
- You cannot selectively delete rows. It’s all or nothing
DELETE FROM t
- You have to explicitly COMMIT a DELETE statement to make it permanent
- You can ROLLBACK a DELETE statement if you change your mind before you COMMIT
- Space is not deallocated
- Does not reset the high water mark, which may result in poor performance (especially in full table scans)
- Can be slower than removing all rows with the TRUNCATE statement
- You can selectively delete rows based on some criteria in the WHERE clause
February 24th, 2006 at 12:06 am
Hi Eddie thanks for posting;
i would like to be stronger in my statements :
Truncate:
*is* faster than removing all rows with the DELETE statement
Delete:
*is* slower than removing all rows with the TRUNCATE statement
Additionally
Delete ( the whole table ) causes a lot of IO on INDEX Segments, UNDO Blocks and Redo logs.
If you truncate a table and know that you will fill it again with almost the same amount of data
there is a TRUNCATE TABLE [TABLE_NAME] REUSE STORAGE. All Extents remain allocated – a load of may rows is faster then.
Greetings
Karl