Hopefully you might have encountered the question “Which one is faster? TRUNCATE or DELETE” from your interviewer at least once while you sat for your technical interview. Question is simple, but before giving a mechanical answer “Truncate is faster than Delete”, let us understand why truncate is actually faster than delete? Also let us have brief view about both.
TRUNCATE is a DDL (Data Definition Language) command that removes all rows from table. It does not require much UNDO space to perform the operation as the data does not require making a copy to Rollback tablespace.
Conditional delete of records is not possible with the TRUNCATE command. You cannot use WHERE clause while using TRUNCATE.
Data once deleted using TRUNCATE command cannot be retrieved or restored again. Rollback is impossible.
Triggers cannot be fired on TRUNCATE.
DELETE is a DML (Data Manipulation Language) command that removes all or specified rows from table.
When DELETE is performed, data first gets copied into Rollback tablespace in order to retrieve the data back if ROLLBACK is fired.
Conditional records from table can be deleted using WHERE clause.
Data can be rolled back, if ROLLBACK is issued even after the DELETE statement is executed.
Triggers can be used on DELETE statement.
TRUNCATE or DELETE? Which one to use and when?
TRUNCATE can be used when your requirement demands to empty whole table before storing new records every time. For example, temporary table used in interface. DELETE can be also be used for the same purpose but there is point of piling up Rollback tablespace.
DELETE is more preferable when you delete org specific data in custom Oracle Report of Oracle E-business Suite application.
For example, before running report of India location you need to delete India records based on its org_id.
Above, I have stated most common application of TRUNCATE and DELETE. There are lots more of them which is fun to explore.
If you liked the above post put your comments below. Your feedback and suggestions are valuable to us.