Difference between TRUNCATE and DELETE.

TRUNCATE is DDL commands, whereas DELETE is a DML command. Therefore DELETE operations can be rolled back (undone), while TRUNCATE operations cannot be rolled back.

Truncate does not make entries in a LOG file.

Delete makes entries for each row that gets deleted.

TRUNCATE is much faster than DELETE.

Reason: When you type DELETE. All the data get copied into the rollback tablespace first, then delete operation get performed, That’s why when you type rollback after deleting a table, you can get back the data (The system get it for you from the rollback tablespace).

All this process takes time. But when you type TRUNCATE, it removes data directly without copying it into the Rollback tablespace. That’s why TRUNCATE is faster, once you truncate you can't get back the data.

Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all records have been deleted retains all of its original blocks.

A truncate moves the High Water Mark of the table back to zero. No row-level locks are taken, no redo or rollback is generated. All extents bar the initial are de-allocated from the table. By re-positioning the high water mark, they prevent reading of any table data, so they have the same effect as a delete, but without all the overhead. Just one slight problem: a truncate is a DDL command, so you can't roll it back if you decide you made a mistake. (It's also true that you can't selectively truncate -no "WHERE" clause is permitted, unlike with deletes, of course).

By resetting the High Water Mark, the truncate prevents reading of any table's data, so they it has the same effect as a delete, but without the overhead. There is, however, one aspect of a Truncate that must be kept in mind. Because a Truncate is DDL it issues a COMMIT before it acts and another COMMIT afterward so no rollback of the transaction is possible.

Note that by default, TRUNCATE drops storage even if DROP STORAGE is not specified.

Oracle Database SQL Reference documentation for versions 11.1, 10.2, 10.1 and 9.2 all state that "DROP STORAGE" is default option for TRUNCATE.

That is: "DROP STORAGE: Specify DROP STORAGE to deallocate all space from the deleted rows from the table or cluster except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace.

Oracle also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This is the default."