Deletion of all rows from a table

You can use the TRUNCATE TABLE statement as a fast method of deleting all the rows in a table. It is faster than a DELETE statement with no conditions, because the DELETE logs each change, while TRUNCATE does not record individual rows deleted.

The table definition for a table emptied with the TRUNCATE TABLE statement remains in the database, along with its indexes and other associated objects, unless you execute a DROP TABLE statement.

You cannot use TRUNCATE TABLE if another table has rows that reference it through a referential integrity constraint. Delete the rows from the foreign table, or truncate the foreign table and then truncate the primary table.

Truncating base tables or performing bulk loading operations causes data in indexes (regular or text) and dependent materialized views to become stale. You should first truncate the data in the indexes and dependent materialized views, execute the INPUT statement, and then rebuild or refresh the indexes and materialized views.

TRUNCATE TABLE syntax

The syntax of TRUNCATE TABLE is:

TRUNCATE TABLE table-name

For example, to remove all the data in the SalesOrders table, enter the following:

TRUNCATE TABLE SalesOrders;

A TRUNCATE TABLE statement does not fire triggers defined on the table.

Cancel these changes to the database by executing a ROLLBACK statement:

ROLLBACK;

Deletion of all rows from a table

TRUNCATE TABLE syntax

See also