| 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. |
Thursday, June 9, 2022
Oracle SQL Difference between Delete and Truncate
Labels:
Oracle SQL
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment