TRUNCATE

• TRUNCATE removes all rows from a table or specific partition.
• The structure of the table (schema) is retained.
• It is a DDL (Data Definition Language) command.
• Faster than DELETE, as it does not log each row deletion.
• Does not fire triggers.
• It is generally irreversible unless in a transaction.
• Does not generate individual row delete operations.

 

Syntax:

TRUNCATE TABLE table_name;

 

Key Characteristics:

DML vs DDL:

• TRUNCATE is a DDL operation.
• Once executed, it cannot be rolled back (unless within a transaction).
• DELETE is a DML operation, and it can be rolled back if wrapped in a transaction.

Faster than DELETE:

• TRUNCATE is faster than DELETE because it does not log individual row deletions.
• It only logs the deallocation of data pages, making it efficient for large tables.

Resets High Water Mark:

• Resets the high water mark (the point where data has been inserted).
• Frees up space previously occupied by rows for future inserts.
• This helps improve performance for subsequent data insertions.

Cannot Be Rolled Back:

• Since TRUNCATE is a DDL command, the operation is permanent and cannot be rolled back.
• You cannot undo the data removal unless backups are available.

No Triggers Fired:

• TRUNCATE does not fire any triggers.
• DELETE can activate DELETE triggers defined on the table.

Cannot Be Used with Foreign Key Constraints:

• If the table has foreign key constraints, TRUNCATE cannot be executed unless:
• The constraints are temporarily disabled or
• The referencing tables are also truncated.

Preserves Table Structure:

• The table's structure (columns, constraints, indexes) remains intact after TRUNCATE.
• Only the data is removed, not the table schema.

Space Management:

• TRUNCATE does not immediately release space on disk.
• It marks the space as reusable.
• The table’s size on disk remains unchanged unless unused space is explicitly released using a command like ALTER TABLE.

Can Be Used on Partitioned Tables:

• TRUNCATE can be used on specific partitions of a partitioned table.

Example:  

TRUNCATE TABLE table_name PARTITION partition_name;

No WHERE Clause:

• Unlike DELETE, TRUNCATE does not support a WHERE clause.
• It removes all rows from the table, without any conditions.

 

Advantages:

Efficiency: 

• TRUNCATE is much more efficient than DELETE in terms of performance because it doesn't log each row deletion.

Faster Execution: 

• TRUNCATE does not scan the table or log individual deletions, making it faster for clearing a table quickly.

No Row-Level Locking: 

• TRUNCATE locks the entire table, but does not lock individual rows like DELETE does, making it less resource-intensive for large tables.

 

Disadvantages of TRUNCATE:

No Flexibility: 

•  You cannot delete specific rows with TRUNCATE. It removes all rows in the table.

Cannot Be Rolled Back: 

•  Since TRUNCATE is a DDL operation, it cannot be rolled back once executed.

Foreign Key Constraints: 

• If the table has foreign key relationships, you must disable the constraints before performing a TRUNCATE.

Does Not Fire Triggers: 

• TRUNCATE does not activate any triggers that might be set for auditing or logging purposes.

 

5. When to Use TRUNCATE:

• Resetting Tables: When you need to quickly remove all data and don’t need to worry about triggers or rolling back, TRUNCATE is ideal.
• Performance Considerations: For large tables, TRUNCATE is more efficient than DELETE, especially when you need to clear the data quickly.
• Data Archiving or Backup: When preparing a table for archiving or backup and you need to empty the table fast, TRUNCATE is a good choice.

6. Examples:

• Truncate All Data in a Table:

TRUNCATE TABLE employees;

• Truncate a Specific Partition:

TRUNCATE TABLE employees PARTITION (partition_name);

• Truncate and Release Space (optional):

ALTER TABLE employees DEALLOCATE UNUSED;

7. Conclusion:

TRUNCATE is a fast and efficient way to clear all data from a table in Oracle. It is particularly useful when performance is critical and when you don’t need the flexibility of DELETE. However, it comes with the trade-off of being irreversible and less flexible in terms of row-level deletions. Understanding the benefits and limitations of TRUNCATE helps you make the best decision for managing your database effectively.