The Difference between DELETE, TRUNCATE and DROP

May 19, 2010

in DBA, FAQ, keywords, SQL

delete-truncate-drop

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.

Photo by MarS

{ 14 comments… read them below or add one }

Scarpia May 23, 2010 at 9:50 pm

Very nicely listed. Bookmarked this. Thanks a million.

oratabler May 24, 2010 at 12:54 am

@Scarpia: You’re most welcome. Do check back again for more and share the word. Thank you!

dinesh December 13, 2010 at 12:12 pm

Thanks for information.

oratabler December 13, 2010 at 12:23 pm

@dinesh: Good to know you found it useful.

basha April 4, 2011 at 12:58 am

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

oratabler April 4, 2011 at 10:32 am

@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.

Hemasundar Oggu June 3, 2011 at 6:31 pm

Very nice !!!!

Dewe sh Pushkar January 31, 2012 at 1:48 am

Thanks for this useful details please send some detail about delete drop and truncate in sql server 2005.

smit September 29, 2012 at 10:20 pm

very nice ….

afsar imam October 3, 2013 at 2:34 pm

nice

OracleUser October 20, 2013 at 9:06 pm

Excellent..Excellent..Excellent

Mayur Narole April 25, 2016 at 7:39 pm

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.

Chinmaya Panda October 10, 2017 at 5:37 pm

Can you please elaborate the 2nd last point “No. A table can be truncated in one’s own schema only.” ?

oratabler October 13, 2017 at 9:47 pm

@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.

Leave a Comment

Previous post:

Next post: