Truncate vs Delete
Here I compare the differences between TRUNCATE and DELETE an entire table in the following chart.
|Question||Truncate Table||Delete From|
|DDL or DML?||DDL||DML|
|Need Commit?||Not Necessary.||Yes (or exit gracefully in sqlplus).|
|Able to Rollback?||No, a successful DDL cannot be rolled back.||Yes, we can rolled back uncommitted DML.|
|Generate Redo?||Very little.||A lot.|
|Archived Logs?||Very little.||A lot.|
|Generate Undo?||No.||A lot.|
|HWM Moved?||Reset to zero.||No change.|
|Possible Space Alert?||Never.||FRA and UNDO.|
|Speed?||Very fast.||Much slower.|
|Able to Recover Completely?||Quite hard.||Mildly hard.|
|Recoverable by Flashback?||Only Flashback Database.||Mainly Flashback Transaction Query.|
Please note the following things about Truncate vs Delete:
- Since archived logs are derived from redo logs, so we can expect that the total generated number of archived logs equals to redo log's turnovers. That's why your FRA is under pressure.
- If you roll back an uncommitted DELETE transaction, the amount of generated redo logs will be almost doubled. Therefore, you have to watch the space usage of archived logs closely.
- Using DELETE to remove all data of a table may run out of UNDO tablespace, once UNDO is exhausted, the transaction will be aborted with rollback.
- After DELETE statements, you can use ALTER TABLE SHRINK SPACE or ALTER TABLE MOVE to compact the table so as to lower the high water mark. I prefer the latter one.
- Like I said in another post, there're several ways that can recover a truncated table, but they all have some disadvantages or restrictions.