Truncate vs Delete Entire Table

Here I compare the differences between TRUNCATE and DELETE an entire table in the following chart.

QuestionTruncate TableDelete From
DDL or DML?DDLDML
Need Commit?Not Necessary.Yes (or exit gracefully).
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:

  1. 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. That's why your FRA is under pressure.
  2. If you roll back an uncommitted DELETE transaction, the amount of generated redo logs will be doubled. Therefore, you have to watch the space usage of archived logs closely.
  3. Using DELETE to remove all data in a table may run out of UNDO tablespace, once UNDO is exhausted, the transaction will be aborted with rollback.
  4. 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.
  5. Like I said in another post, there're several ways that can recover a truncated table, but they all have some disadvantages or restrictions.

Leave a Reply

Your email address will not be published. Required fields are marked *