Oracle DELETE vs TRUNCATE: Complete Guide

There are two methods to empty all data from a table in an Oracle database.

Option 1: Utilize the DELETE statement.

DELETE FROM 表名;

For example, to delete all data from a table named “employees”:

DELETE FROM employees;

Option Two: Utilize the TRUNCATE statement.

TRUNCATE TABLE 表名;

For example, to delete all data from the table named “employees”:

TRUNCATE TABLE employees;

The difference between these two methods lies in:

1. The DELETE statement removes data from a table one row at a time, processing each row as a separate transaction and writing to the transaction log after the deletion. This means that the execution speed of the DELETE statement is relatively slow, but it can be rolled back using the ROLLBACK statement.
2. The TRUNCATE statement deletes data from a table by releasing the storage space it occupies, deleting all data in the table at once without being logged in the transaction log. As a result, TRUNCATE statement executes quickly but cannot be rolled back using a ROLLBACK statement.