What is difference between the truncate and delete statements in SQL?

by Kiran
(Shimla)

The biggest difference between issuing a delete statement and issuing a truncate table statement against a table in Oracle is in what happens if you change your mind. With the truncate statement, if you change your mind after issuing it, it's too bad, all the data has gone. With the delete statement, you can reverse it if you change your mind by issuing the rollback command (as long as you haven't performed an explicit or implicit commit).

The truncate table command can therefore be regarded as a fast delete command for the whole table. The speed of the operation is not dependent on the size of the table (unlike the delete command) because no undo (rollback) data is generated, thereby, as previously mentioned, meaning that it can't be undone (without restoring from a backup). By default, the truncate table command also releases all space used by the table except that specified by the MINEXTENTS storage parameter.

The delete statement on the other hand can be used against a table or a view (subject to certain conditions) and can have a where clause to restrict the rows deleted. The delete statement can take a long time if the number of rows to be deleted is large as Oracle will generate undo data so that the delete can be reversed if required (as already mentioned). The delete statement also doesn't release any space allocated to the table even if all rows have been deleted.

For all the permutations and restrictions of the delete statement see the Oracle® Database SQL Language Reference 11g Release 2 (11.2). Full details of the truncate table statement can also be found in Oracle® Database SQL Language Reference 11g Release 2 (11.2).

A tutorial on the use of the basic SQL commands including the delete statement is available on our site.