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.
{ 14 comments… read them below or add one }
Very nicely listed. Bookmarked this. Thanks a million.
@Scarpia: You’re most welcome. Do check back again for more and share the word. Thank you!
Thanks for information.
@dinesh: Good to know you found it useful.
it’s really great subject u have provided me. ur site is really great. if possible,send any data about oracle to my email id
@basha: Thanks. To receive articles about Oracle at your email id, provide your email id here: Email Sign-up. You will receive a mail to confirm your email address. Just do that and you’re good to go.
Very nice !!!!
Thanks for this useful details please send some detail about delete drop and truncate in sql server 2005.
very nice ….
nice
Excellent..Excellent..Excellent
I have a questions : I used TRUNCATE TABLE vendors CASCADE; and the records in the other tables with the foreign keys also got deleted. There was just having foreign key constraint but not having a single record with that vendor id reference, still those records were deleted. How this is possible. Any help will be appreciated. Thanks in advance.
Can you please elaborate the 2nd last point “No. A table can be truncated in one’s own schema only.” ?
@Chinmaya Panda: You need to say “truncate schema.table_name” to perform a truncate from outside the owning schema, even if a table synonym is available for DML operations. And you can do this only if you have the extremely powerful DROP ANY TABLE privilege.
There is a workaround: other schemas can truncate a table via a stored procedure in the owning schema, with dynamic SQL for issuing the TRUNCATE command. See this link for details.