Difference between delete and truncate

Jun 11 2017 04:58 AM by Kishan Mashru

This blog will discuss the probable difference between Delete statement and Truncate Statement in Oracle SQL. Understanding this difference will help you to take 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

 

To understand more you can have a look at the video on the top of the page or visit the video on YouTube with the following link: 

https://www.youtube.com/watch?v=OgEmJtOQ7hg&t=4s