Delete all rows using TRUNCATE

You can use the TRUNCATE statement to quickly remove all rows from a table and also remove all corresponding index data. You cannot recover deleted rows after the transaction is committed. You can use the TRUNCATE statement on tables that contain any type of columns, including smart large objects.

Removing rows with the TRUNCATE statement is faster than removing them with the DELETE statement. It is not necessary to run the UPDATE STATISTICS statement immediately after the TRUNCATE statement. After TRUNCATE executes successfully, HCL OneDB™ automatically updates the statistics and distributions for the table and for its indexes in the system catalog to show no rows in the table or in its dbspace partitions.

For a description of logging, see Transaction logging.

TRUNCATE is a data-definition language statement that does not activate DELETE triggers, if any are defined on the table. For an explanation on using triggers, see Create and use triggers.

If the table that the TRUNCATE statement specifies is a typed table, a successful TRUNCATE operation removes all the rows and B-tree structures from that table and from all its subtables within the table hierarchy. TRUNCATE has no equivalent to the ONLY keyword of the DELETE statement to restricts the operation to a single table within the typed table hierarchy.

always logs the TRUNCATE operation, even for a non-logging table. In databases that support transaction logging, only the COMMIT WORK or ROLLBACK WORK statement of SQL is valid after TRUNCATE within the same transaction. For information on the performance impact of using the TRUNCATE statement, see your HCL OneDB Performance Guide. For the complete syntax, see the HCL OneDB Guide to SQL: Syntax.