Deleting All Rows using TRUNCATE TABLE

You can use the TRUNCATE TABLE 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 TABLE statement on tables that contain any type of columns, including smart large objects.

Removing rows with the TRUNCATE TABLE statement is faster than removing them with the DELETE statement for these reasons:

• Truncating a table does not fire the table's DELETE triggers. For an explanation on using triggers, see Creating and Using Triggers.
• Logical logging is not done for each row of the table that is being truncated. For a description of logging, see Transaction Logging.

For information on the performance impact of using the TRUNCATE TABLE statement, see your IBM Informix: Performance Guide. For complete syntax, see the IBM Informix: Guide to SQL Syntax.

Deleting Rows from a Supertable using TRUNCATE TABLE

When you use the TRUNCATE TABLE statement with the supertable of a hierarchy, the ONLY keyword allows truncating only the supertable, or truncating the supertable and all its subtables. By default (without ONLY), the supertable and all of its subtables are truncated. Suppose you create a supertable person that has two subtables, employee and sales_rep defined under it. The following TRUNCATE TABLE statement deletes rows from the person, employee, and sales_rep tables:

TRUNCATE TABLE person

To limit a delete to rows of the supertable only, you must use the ONLY keyword in the TRUNCATE TABLE statement. For example, the following statement deletes rows of the person table only:

TRUNCATE TABLE ONLY person