Performance Advantages of TRUNCATE

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

In contexts where no existing rows of a table are needed, the TRUNCATE 4statement is typically far more efficient than using the DELETE statement 4with no WHERE clause to empty the table, because TRUNCATE requires fewer resources 4and less logging overhead than DELETE:

4
• DELETE FROM table deletes each row as a separately 4logged operation. If indexes exist on the table, each index must be updated 4when a row is deleted, and this update is also logged for each row. If an 4enabled Delete trigger is defined on the table, its triggered actions must 4also be executed and logged.
4
• TRUNCATE table performs the removal of all rows 4and of the B-tree structures of every index on the table as a single 4operation, and writes a single entry in the logical log when the transaction 4that includes TRUNCATE is committed or rolled back. The triggered action of 4any enabled trigger is ignored.

These performance advantages of TRUNCATE over DELETE are reduced when the 4table has one or more columns with the following attributes:

4
• Any simple large object data types stored in blobspaces
4
• Any BLOB, CLOB, complex, or user-defined types stored in sbspaces
4
• Any opaque types for which a destroy support function 4is defined.

Each of these features require the database server to read each row of 4the 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 4between TRUNCATE and DELETE would reflect the relative costs of invoking the am_truncate interface once for TRUNCATE versus invoking 4the destroy support function for each row.

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

DELETE customer;

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