Difference Between Delete And Truncate in Oracle SQL

In this Section, We will explore about the Difference between Delete and Truncate in Oracle SQL.

We have different-different SQL command to delete or remove data from Oracle database table. Today we will discuss that SQL command which is that Delete and Truncate commands.

What is Delete Command in Oracle?

Delete is a DML command. Delete Command is used to remove specific rows from the table by using WHERE clause and Delete Command also helps to remove all rows from the table. 

Syntax of Delete Command

Delete from Table_Name [Where condition];

Example of Delete Command

DELETE FROM ADD_CART WHERE PRODUCT_ID=7;

Let's understand about the above statement, I have table which name is ADD_CART and I am removing rows where product_id will be 7.

What is Truncate Command?

Truncate is a DDL command. This command is use for the remove all records from the table. We can't use the Where clause in truncate command.

Syntax of Truncate

Truncate table Table_Name;

Example of Truncate

TRUNCATE table ADD_CART;

Difference Between Delete and Truncate in Oracle

Delete Command

1. Delete is a DML command.
2. Delete is used to delete a particular record as well as all records also.
3. Delete is not an auto-commit statement.
4. The delete can be  also used with a WHERE Clause.
5. Database trigger fired on Delete Statement.
6. Delete Command is slow.
7. Delete Command can be the rollback.
8. Delete Command does not remove or clear table space size.. It's mean after removing the rows from the table still table space size. same. You can run following SQL Query to check the table status.
9. We can capture deleted data info using Returning clause.

Truncate Command

1. Truncate is a DDL command.
2. Truncate is used to remove all data from the  table.
3. Truncate is an auto-commit statement.
4. Truncate can’t be used with a WHERE clause.
5. The database trigger does not fire on truncate statement.
6. Truncate Command is Faster.
7. The truncate command cannot be the rollback.
8. Truncate Command  removes table space size. 

Conclusion:- Based on requirements you can select your way that which way is necessary for your requirements. Delete is mostly used for remove specific rows from the table while Truncate Command is use for remove all rows from the table.

Note:- Run Following SQL Query to check table space size.

(select segment_name,sum(bytes)/1024/1024/1024 GB from dba_segments where segment_type='TABLE' and segment_name=upper('table_name') group by segment_name; )

That's all for today, I think (Basic Difference Between Delete And Truncate in Oracle SQL) post will helpful for you. If you like this post then share your view in the comment box.