Performance Advantages of TRUNCATE

The TRUNCATE statement is not equivalent to DROP TABLE. After TRUNCATE successfully executes, the specified table and all its columns and indexes are still registered in the database, but with no rows of data. In information management applications that require replacing all of the records in a table after some time interval, TRUNCATE requires fewer updates to the system catalog than the equivalent DROP TABLE, CREATE TABLE, and any additional DDL statements to redefine any synonyms, views, constraints, triggers, privileges, fragmentation schemes, and other attributes and associated database objects of the table.

In contexts where no existing rows of a table are needed, the TRUNCATE statement is typically far more efficient than using the DELETE statement with no WHERE clause to empty the table, because TRUNCATE requires fewer resources and less logging overhead than DELETE:
  • DELETE FROM table deletes each row as a separately logged operation. If indexes exist on the table, each index must be updated when a row is deleted, and this update is also logged for each row. If an enabled Delete trigger is defined on the table, its triggered actions must also be executed and logged.
  • TRUNCATE table performs the removal of all rows and of the B-tree structures of every index on the table as a single operation, and writes a single entry in the logical log when the transaction that includes TRUNCATE is committed or rolled back. The triggered action of any enabled trigger is ignored.
These performance advantages of TRUNCATE over DELETE are reduced when the table has one or more columns with the following attributes:
  • Any simple large object data types stored in blobspaces
  • Any BLOB, CLOB, complex, or user-defined types stored in sbspaces
  • Any opaque types for which a destroy support function is defined.
Each of these features require the database server to read each row of the table, substantially reducing the speed of TRUNCATE.

If a table includes one or more UDTs for which you have registered an am_truncate( ) purpose function, then the performance difference between TRUNCATE and DELETE would reflect the relative costs of invoking the am_truncate interface once for TRUNCATE versus invoking the destroy( ) support function for each row.

As listed in the next section, certain conditions cause TRUNCATE to fail with an error. Some of these conditions have no effect on DELETE operations, so in those cases you can remove all rows more efficiently with a DELETE statement, as in the following operation on the customer table:

DELETE customer;
The FROM keyword that immediately follows DELETE can be omitted, as in this example, only if the DELIMIDENT environment variable is set.