What is difference between the truncate and delete statements in SQL?
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.
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.