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

by Kiran

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 by Oracle, therefore, as previously mentioned, it can't be undone (without restoring from a backup). By default, the truncate table command also causes the database to release 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 basic Oracle SQL commands including the delete statement is available on our site.

For more help with SQL see our Oracle SQL tutorial.

If you're interested in formal Oracle training (either on-line or in a classroom) then see our Oracle training page for more information.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.