Difference between Delete and Truncate ~ OracleSource

Thursday, July 10, 2008

Difference between Delete and Truncate

Deleteing records from a table is a DML(Data Manipulation Language) operation.
The deleted records are written to redo log files in case they need to be retrieved again or the operation is ROLLBACKed.

Syntax of DELETE operation :-
DELETE FROM tablename
WHERE CONDITION

TRUNCATE is a DDL(Data Definition Language) operation. When we TRUNCATE a table all the records gets deleted in one go and not record by record. Also the deleted records are not written to the REDO LOG file.
When we have millions of records to delete TRUNACATE is the best option.

Syntax of TRUNCATE operation :-
TRUNCATE TABLE tablename

Writting of the deleted records one by one to the REDO LOG files takes time which makes DELETE operation slower than TRUNCATE.
One must take care that data once Truncated can't be Rolled back.

No comments: