Monday 21 May 2012

Delete vs. Truncate

Delete and Truncate SQL verbs (commands) are frequently used to empty tables. The actual difference between the two is not much clear to most users; not only beginners but also to the experienced users. There also seems to be people having very little idea about when to use what regarding these two commands. This post is an effort to clarify about the same. The bulleted list of properties of these commands may help to make out the differences between the two very easily. We also visit the situations when it will be appropriate to use "delete" or "truncate". -

Delete -
  • This is a DML category of command.
  • Being a DML category command, it stores the rollback/undo data (the original version of the data that was changed, in case of delete it is the entire set of rows which are deleted) 
  • Being a DML category command, the operation can be undone.
  • Being a DML category command, it requires an explicit "commit" command to be fired post operation if the deletion is to be confirmed.
  • Delete command does not reset the high water mark (HWM) i.e. the level to which once the rows had reached in the table.
  • Delete command may be used with a "where" clause. It is only when the predicate is not used, will empty the table, and there is no freeing of space even in such case for new object(s).
Truncate -
  • This is a DDL category command.
  • Being a DDL category command, it stores no rollback / undo data.
  • Being a DDL category command, the operation can NOT be undone by using a "rollback" command.
  • Being a DDL category, it DOES NOT require an explicit "commit" command.
  • Truncate command resets the high water mark to the 0 level, with only preserving the initial extent allocated to the table for the existence of the table. Which means all the extents above the initial are reclaimed as free space.
  • Truncate command does not have a "where" clause, which means it must empty the table while returning the allocated space (except the initial extent) for new objects.
Is "Truncate" same as "Delete + commit" ? -
There is a normal perception of many users of the databases that truncate is equivalent to deleting all rows and commit rolled into one. And it is just that. Technically however, this is a big NO. As is apparent from the properties of these two commands as described above, the main difference is the reclamation of the space and resetting of the High Water Mark. Many are also seen to be advising use of Truncate over Delete + commit if the users want to empty the table, since the truncate command is "more efficient". Actually there are some considerations to be made before deciding upon the "truncate" or "delete" for emptying the table -
  • Delete will require huge space (same size as table) to store rollback/undo data if the table is huge, so a truncate may be more appropriate, but only if the data being deleted is for sure to be deleted and there is no possibility of afterthought for rolling back.
  • A moderate sized table if required to be reloaded with approximately same amount of data (to same HWM) then delete command may be recommended. Truncate will release the extents above the minimum initial extents and will require de-allocation and reallocation of space, the resources for which may outweigh the extension of rollback segments where the undo data is stored.
  • A truncate command may be recommended over delete if the volume of the reload to the table is much smaller than the present volume of the data, since the space above HWM of the present data may have been reclaimed in the truncate operation.
  • Delete command may be recommended in cases if there is a temporary replacement of data for running only "select" queries on the intermediate temporarily loaded data and a simple rollback will be able to restore the original state of data.
  • If no bulk load is in consideration post empty operation of the table, the truncate shall be more appropriate.
The above scenarios may not present an exhaustive list, but known the properties of the delete and truncate operations, a prudent consideration of the differences between the two, may guide the process of appropriate choice between the two.
 
Other suggested reading - Materialized ViewsUse of External Tables , DDBMS

3 comments:

  1. One more difference which you did't discuss above is ... if the tables have referential integerity constraints then you can not truncate the table untill this constraint is enabled , even there is no data in both the tables.. we have to disable the constraint to truncate the table

    but using delete we can do the same using delete cascade and on delete set null ...

    Try this ...

    ReplyDelete
    Replies
    1. "on delete cascade" or "on delete set null" are the clauses to be used with the definition of the Foreign key constraint. They allow the parent rows to be deleted along with the related child rows either to be deleted or FK values for such rows to be set to null respectively. These clauses since are part of definition of constraint, has no difference in behavior with respect to truncate or delete. With the FK constraint define with these clauses the parent rows may be deleted or truncated without any difference, since these clauses are not part of the delete or truncate commands. Please try at your end and post your observations. Thanks for your interest.

      Delete
  2. One more difference.... You need an explicit 'DROP TaBLE' privilege to truncate a table, but you need 'delete any table' privilege to delete data from a table.

    ReplyDelete