This page shows you how to use the TRUNCATE statement. The TRUNCATE statement is a DDL statement. It lets you drop data from a table while preserving the table’s structure. You can’t undo the TRUNCATE command and recover the data, like you would from a DML DELETE command.
Oracle
You can use the TRUNCATE statement to remove data. It doesn’t log the deletion of the data and doesn’t enable a ROLLBACK statement because there isn’t any logging of the deletion. It does leave the definition of a table intact.
This is helpful when you don’t need the data but do need the table structure. The most frequent use of this command is for tables that contain derived data. Derived data is calculated from source data, and thereby easy to replace. There are often a number of temporary reporting tables that hold derived data to speed query resolution.
Since Oracle 10g, these typically evolve into materialized views because they can be refreshed easily and less expensively than truncating and repopulating tables with derived data.
TRUNCATE TABLE item_rental_histogram; |
TRUNCATE TABLE item_rental_histogram;
MySQL
Like Oracle, MySQL provides a TRUNCATE statement. It removes the data but leave the definition of a table intact. This accomplished by not logging the row deletions, which makes the data deletion permanent. Logged deletions are required to support a transactional ROLLBACK statement.
This is helpful when you don’t need the data but do need the table structure. The most frequent use of this command is for tables that contain derived data. Derived data is calculated from source data, and thereby easy to replace. There are often a number of temporary reporting tables that hold derived data to speed query resolution.
TRUNCATE TABLE item_rental_histogram; |
TRUNCATE TABLE item_rental_histogram;
