Thursday, June 9, 2022

Oracle SQL Difference between Delete and Truncate

  Topic

DELETE

TRUNCATE

Definition

It is used to delete one or multiple rows from a table using conditions.

It is used to delete the entire data from the table without using any condition.

Language

It is a DML(Data Manipulation Language) command.

It is a DDL(Data Definition Language) command.

Commit

We have to manually COMMIT the changes

The changes are automatically COMMIT in the TRUNCATE command.

Process

It deletes rows one by one with conditions.

It deletes all the data at once.

Condition

It uses the WHERE clause as a condition.

It does not take any condition i.e. does not use WHERE clause.

Lock

It locks all the rows for deletion.

It uses table lock to lock the pages for deletion.

Log

It logs every single record in the transaction log.

It only logs the deallocation of the pages where the data is stored.

Transaction space

It uses more transaction space compared to the TRUNCATION command.

It uses less transaction space compared to the DELETE command.

Identity

It does not reset the table identity to its seed value if there is an identity column.

It resets the table identity to its seed value.

Permission

It needs delete permission.

It needs alter table permission.

Speed

It is much slower when comes to big databases.

It is faster or in other words, instant.

No comments:

Post a Comment