Posted By: David Aldridge
What’s the difference between
DELETE in SQL?
If your answer is platform specific, please indicate that.
Here’s a list of differences. I’ve highlighted Oracle-specific features, and hopefully the community can add in other vendors’ specific difference also. Differences that are common to most vendors can go directly below the headings, with differences highlighted below.
If you want to quickly delete all of the rows from a table, and you’re really sure that you want to do it, and you do not have foreign keys against the tables, then a TRUNCATE is probably going to be faster than a DELETE.
Various system-specific issues have to be considered, as detailed below.
Delete is DML, Truncate is DDL (What is DDL and DML?)
Commit and Rollback
Variable by vendor
Truncate can be rolled back.
Truncate can be rolled back.
Because a TRUNCATE is DDL it involves two commits, one before and one after the statement execution. Truncate can therefore not be rolled back, and a failure in the truncate process will have issued a commit anyway.
However, see Flashback below.
Delete does not recover space, Truncate recovers space
If you use the REUSE STORAGE clause then the data segments are not de-allocated, which can be marginally more efficient if the table is to be reloaded with data. The high water mark is reset.
Delete can be used to remove all rows or only a subset of rows. Truncate removes all rows.
When a table is partitioned, the individual partitions can be truncated in isolation, thus a partial removal of all the table’s data is possible.
Delete can be applied to tables and tables inside a cluster. Truncate applies only to tables or the entire cluster. (May be Oracle specific)
Data Object Identity
Delete does not affect the data object id, but truncate assigns a new data object id unless there has never been an insert against the table since its creation Even a single insert that is rolled back will cause a new data object id to be assigned upon truncation.
Flashback works across deletes, but a truncate prevents flashback to states prior to the operation.
However, from 11gR2 the FLASHBACK ARCHIVE feature allows this, except in Express Edition
Delete can be granted on a table to another user or role, but truncate cannot be without using a DROP ANY TABLE grant.
Delete generates a small amount of redo and a large amount of undo. Truncate generates a negligible amount of each.
A truncate operation renders unusable indexes usable again. Delete does not.
A truncate cannot be applied when an enabled foreign key references the table. Treatment with delete depends on the configuration of the foreign keys.
Truncate requires an exclusive table lock, delete requires a shared table lock. Hence disabling table locks is a way of preventing truncate operations on a table.
DML triggers do not fire on a truncate.
DDL triggers are available.
Truncate cannot be issued over a database link.
Truncate resets the sequence for IDENTITY column types, delete does not.
In most implementations, a
DELETE statement can return to the client the rows that were deleted.
e.g. in an Oracle PL/SQL subprogram you could:
DELETE FROM employees_temp WHERE employee_id = 299 RETURNING first_name, last_name INTO emp_first_name, emp_last_name;