This post will discuss the differences between Delete statement and Truncate Statement in Oracle SQL. Understanding this difference will help you to make a better decision in choosing the appropriate statement when a situation arises.
| DELETE | TRUNCATE |
|---|---|
| DML Statement | DDL statement |
| Once executed statement changes can be rolledback | Auto-commit, once executed changes cannot be rolled back |
| Conditional DELETE is possible using the WHERE clause | TRUNCATE will remove all the data from the table, no conditions can be applied |
| Performance is less than TRUNCATE, as all the deleted data is copied to the undo tablespace to rollback if required | Performance is better than DELETE, as no copying of data to undo tablespace is performed |
| Does not reset the HVM | Resets the HVM, which helps the next queries on the table to execute better |
| Can be used when a table has Foreign Key | If the table has valid Foreign Key enabled, TRUNCATE command cannot be executed on the table |
| Any existing DML triggers on a table will be executed when DELETE is fired on a table. | Triggers are not executed on issue of a TRUNCATE statement on a table |