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.
|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|