Which one is faster DELETE/TRUNCATE? Why?

Answer 1

truncate is faster than delete bcoz truncate is a ddl command so it does not produce any rollback information and the storage space is released while the delete command is a dml command and it produces rollback information too and space is not deallocated using delete command.

Answer 2

Truncate is more faster than delete. Since it has a power of releasing the structure of table storage size and deallocates whereas delete is used to mere deletion of records in tables with the usage of where clause as a optional one. Truncate is also fall under DDL part.

Answer 3

A TRUNCATE statement can be rolled back if it is performed within a transaction. The speed of the TRUNCATE statement itself testifies that the actual data is not erased untill it's rewritten. So it is possible to rollback A TRUNCATE Statement.

Answer 4

Truncate is very faster than delete becuase
1.no need to write any date in redo log files
2.no need to fire any triggers

Answer 5

Vaibhav
Apr 3rd, 2007

Below is the best suitable reson:

1. Truncate is an autocommit transaction; therefore as soon as this is executed the database is commited.
2. Delete is a forced-commit transaction which gives luxury of rollback.

Therefore once we want to delete all the records of any table we use truncate instread of delete which will be much faster.

Answer 6

Truncate table
------------------
1) Truncate table is DDL and can be run by the owner of the table.
2) Can't Rollback the truncate table command.
3) It release the space and re-set the high water mark in the segment.
4) Selective record deletion is not possible with truncate table . i.e you can not specifiy where condition in truncate table command.

Delete
--------
1) User with delete permission can delete records from the table.
2) Delete can be rollback.
3) Does not release the occupied space in the segment.
4) We can specify selective record deletion in delete i.e. delete from <table_name> where <condition>