Difference Between Truncate and Delete

Difference Between Truncate and Delete
-------------------------------------------

1. “Truncate is fast, delete is slow”.
2. “Truncate is DDL, delete is DML”.
3. “You can’t rollback a truncate”.
4. “Truncate is implicitly a commit”. someone once mentioned that there are
actually two commits in a truncate, one before and one .
5. “You can’t grant permission to truncate a table”.
6. “You can delete any subset of rows, but you can only truncate the complete
table, or a partition or sub partition of it
7. “Truncate makes unusable indexes usable again”. If you attempt to optimize
a data load by rendering indexes unusable and truncating a table (possibly
followed by an index rebuild and a partition exchange) then be careful of
the order.
8. “Truncate can’t maintain foreign keys”. It’s “cascading delete”,
not “cascading truncate”. That would be an interesting feature though .
Truncating an index cluster is pretty close to a “cascading truncate” to a
limited extent though. In any case no truncate is permitted on a table
referenced by foreign keys.
9. “You can’t flashback a truncate”. This is an oddity to me. We can flashback
a “drop table”, rollback uncommited deletes, or use flashback to recover
pre-commit deleted data, but a truncate is a barrier across which we cannot
flashback.
10. “Truncate deallocates space, delete doesn’t”. Unless you want it not to,
using the “reuse storage” clause. However the high water mark is reset in
either case so maybe that’s a better answer …
11. “Truncate resets the high water mark, delete doesn’t”. And on the indexes,
12. “Truncate allows special treatment of materialized view logs”.
13. “DML triggers do not fire on a truncate”. Because it’s DDL not DML.