Describe the difference between DELETE and TRUNCATE in Oracle.

DELETE vs. TRUNCATE in Oracle

When it comes to managing data in Oracle databases, DELETE and TRUNCATE are two commonly used commands. While both commands are used for removing data from a table, they differ in their functionality and impact on the database.

DELETE Command

The DELETE command is used to remove specific rows from a table based on a condition specified in the WHERE clause. This command is more flexible and allows for selective deletion of data. However, using DELETE can be slower and can lead to fragmentation of the table space.

TRUNCATE Command

The TRUNCATE command is used to remove all rows from a table, effectively resetting the table to its initial state. Unlike DELETE, TRUNCATE does not require a WHERE clause and is faster as it doesn't log individual row deletions. However, TRUNCATE cannot be rolled back and also resets any auto-increment values in the table.

Key Differences

• DELETE is used for selective deletion, while TRUNCATE removes all rows from a table.
• DELETE is slower compared to TRUNCATE.
• TRUNCATE does not generate undo logs, making it non-recoverable.
• TRUNCATE resets auto-increment values, while DELETE does not.

Overall, the choice between DELETE and TRUNCATE in Oracle depends on the specific requirements of the task at hand.