Difference between DELETE and TRUNCATE in Oracle

The DELETE and TRUNCATE statements in Oracle are both used to remove rows from a table, but they have significant differences in terms of performance, transaction handling, and how they affect the structure of the table and its indexes. Below are the key differences between DELETE and TRUNCATE.

DELETE Statement

• Row-by-Row Deletion: The DELETE statement removes rows one at a time, logging each row deletion in the transaction log. This can be slower, especially for large tables.

• Transactional: DELETE operations can be rolled back if necessary, because they are fully logged. This means you can use COMMIT and ROLLBACK statements to control the transaction.

• WHERE Clause: You can use a WHERE clause with DELETE to remove specific rows from a table. Without a WHERE clause, all rows are deleted.

• Triggers and Constraints: DELETE fires any ON DELETE triggers defined on the table. It also checks constraints row by row.

• Space Usage: Space is not immediately freed after a DELETE operation. The space used by the deleted rows remains allocated to the table and can be reused by subsequent insert operations.

• Performance: DELETE can be slower for large datasets because it processes each row individually and maintains transactional logs for each row.

TRUNCATE Statement

• All Rows at Once: The TRUNCATE statement removes all rows from a table by deallocating the data pages used by the table. This is much faster than DELETE for large tables because it doesn't log individual row deletions.

• Non-Transactional: TRUNCATE is a DDL (Data Definition Language) operation and cannot be rolled back once executed. It implicitly commits the transaction, so you cannot use ROLLBACK to undo a TRUNCATE.

• No WHERE Clause: TRUNCATE does not allow a WHERE clause. It always removes all rows from the table.

• Triggers and Constraints: TRUNCATE does not fire ON DELETE triggers because it does not operate row by row. It also doesn't check constraints on individual rows, but it can fail if there are enabled referential integrity constraints.

• Space Usage: Space is freed immediately after a TRUNCATE operation. The table and its associated indexes are reset to their initial size.

• Performance: TRUNCATE is generally much faster than DELETE for large tables due to the way it handles data removal and space deallocation.

Examples

Using DELETE

Deleting specific rows with a WHERE clause:

DELETE FROM employees
WHERE department_id = 10;

Deleting all rows:

DELETE FROM employees;

Using TRUNCATE

Removing all rows from a table:

TRUNCATE TABLE employees;

DELETE

1. Syntax:

   
   DELETE FROM table_name WHERE condition;
   

   • Omitting the WHERE clause will delete all rows in the table.

2. Functionality:

   • Removes rows one at a time.
   • Can delete specific rows based on a condition.
   • Generates redo and undo logs, making it slower compared to TRUNCATE.

3. Transaction Control:

   • Can be rolled back if enclosed in a transaction.
   • Requires a commit to make changes permanent.

4. Triggers:

   • Fires DELETE triggers if they exist on the table.

5. Foreign Key Constraints:

   • Checks and enforces referential integrity constraints.

6. Usage:

   • Suitable when you need to delete specific rows or require the ability to rollback.
   • Used when triggers need to be fired.

7. Space Reclamation:

   • Does not automatically reclaim space.
   • The space occupied by the deleted rows remains allocated to the table.

TRUNCATE

1. Syntax:

   TRUNCATE TABLE table_name;

2. Functionality:

   • Removes all rows from a table.
   • Operates on entire tables rather than individual rows.
   • Much faster than DELETE as it uses minimal logging and does not generate individual row delete entries in the log.

3. Transaction Control:

   • Cannot be rolled back once executed.
   • Implicitly commits the transaction, even if issued within a transaction block.

4. Triggers:

   • Does not fire DELETE triggers because it is a DDL operation.

5. Foreign Key Constraints:

   • Requires the table to not have any enabled foreign key references.
   • Will fail if there are enabled foreign key constraints referencing the table.

6. Usage:

   • Suitable for quickly removing all rows from a table when rollback is not needed.
   • Used for performance reasons when you do not need to fire triggers and can handle the constraints.

7. Space Reclamation:

   • Releases the space allocated to the table back to the tablespace.
   • The high-water mark (HWM) is reset, making the table appear as if it is empty without any allocated extents.

Examples

DELETE Example

Delete specific rows from a table:

DELETE FROM employees WHERE department_id = 10;
COMMIT;

Delete all rows from a table (can be rolled back):

DELETE FROM employees;
COMMIT;

TRUNCATE Example

Remove all rows from a table quickly:

TRUNCATE TABLE employees;

Key Differences Summary

Feature	DELETE	TRUNCATE
Type	DML (Data Manipulation Language)	DDL (Data Definition Language)
Row-Level Operation	Yes	No
Condition-Based Deletion	Yes	No
Rollback Capability	Yes	No
Logging	Generates redo and undo logs	Minimal logging
Trigger Activation	Yes	No
Foreign Key Constraints	Enforced	Not allowed if constraints exist
Performance	Slower	Faster
Space Reclamation	Does not automatically reclaim space	Reclaims space and resets HWM

Practical Considerations

• Use DELETE:

  • When you need to remove specific rows.
  • When you need the ability to rollback.
  • When you need to fire triggers.

• Use TRUNCATE:

  • When you need to remove all rows quickly.
  • When you do not need to rollback.
  • When there are no foreign key constraints referencing the table.
  • For better performance and space management.

By understanding these differences, you can choose the appropriate command based on your specific requirements and ensure optimal performance and functionality in your Oracle database operations.