DELETE, TRUNCATE and DROP – all three commands get rid of table data. How exactly are they different? When should you use which?
A bird’s eye view of their differences, a table comparing the three.
| DELETE | TRUNCATE | DROP | |
| Purpose | Deletes some or all rows of a table | Deletes all rows of a table | Removes all rows and also the table definition, including indexes, triggers, grants, storage parameters |
| Command Type | DML | DDL | DDL |
| Space Usage and Release | Uses UNDO space.
Released blocks that go to the freelist for the table, to be used for subsequent inserts/updates. Does not deallocate space. |
Does not use UNDO space.
Deallocates all space used by the table except MINEXTENTS. |
Does not use UNDO space.
Unless the PURGE clause is specified, does not result in space being released. |
| Commit required? | Yes | No | No |
| Undo possible? | Uncommitted deletes can be rolled back | Cannot be rolled back – once truncated, gone forever | A dropped table can be reinstated from the recycle bin (more on this in a future article) |
| Selective deletion possible? | Yes. Filter criteria be specified via WHERE clause | No filter criteria allowed, removes all rows | No filter criteria allowed, removes all rows |
| Triggers fired? | Yes, DELETE triggers fired | No triggers fired | No triggers fired |
| What if foreign keys (FKs) based on the table exist? | Can delete data even if FKs are enabled, provided the data violates no FK constraint | Cannot delete data if FKs are enabled; FKs need to be disabled/dropped.
Exception: Truncate is possible if the FK is self-referential. |
Can drop the table with the CASCADE CONSTRAINTS option. This will also remove the associated FKs |
| Efficiency | DELETE can be slow especially if the table has many triggers, indexes,and other dependencies | TRUNCATE is most efficient for deleting all rows, even more than dropping and recreating the table using DROP | DROP may not be as efficient as TRUNCATE, as dropping and re-creating the table requires you to re-grant object privileges, re-create indexes, constraints, etc. |
| Privileges required to issue the command | DELETE privilege.
DELETE ANY TABLE allows you to delete rows from any table of any schema. |
DROP ANY TABLE system privilege.
|
DROP ANY TABLE system privilege. |
| Grants | DELETE privilege on a specific table can be granted to another user or role. | TRUNCATE privilege on a specific table cannot be granted to another user or role. | DROP ANY privilege on a specific table cannot be granted to another user or role. |
| Can work outside the user’s schema? | Yes, as long as the user has the DELETE privilege on the object. | No. A table can be truncated in one’s own schema only. | No. A table can be dropped in one’s own schema only. |
| Can work on a table that is part of a cluster? | Yes | No. You will have to truncate the whole cluster, or use either DELETE or DROP. | Yes |
For further reading (10.2 SQL Reference): DELETE, TRUNCATE and DROP.
