TRUNCATE statement

Use the TRUNCATE statement to quickly delete all rows from a local table and free the associated storage space. You can optionally reserve the space for the same table and its indexes. Only Informix® supports this implementation of the TRUNCATE statement, which is an extension to the ANSI/ISO standard for SQL.

Syntax


              .- TABLE -.                                    
>>- TRUNCATE -+---------+--+--------------+--+- table ---+-------->
                        '- 'owner' -- . --'  '- synonym -'   

   .- DROP STORAGE --.                       
>--+-----------------+- -+-------------------+-----------------><
   '- REUSE STORAGE –'   '- KEEP STATISTICS -'


Element	Description	Restrictions	Syntax
`owner`	Owner of `table` or `synonym`	See Usage notes.	Owner name
`synonym`	Synonym for the table from which to remove all data	Must exist, and USETABLENAME must not be set	Identifier
`table`	Name of table from which to remove all data and all B-tree structures of its indexes	Must exist in the database	Identifier

Usage

The TRUNCATE statement rapidly deletes from a local table all active data rows and the B-tree structures of indexes on the table. You have the option of releasing the storage space that was occupied by the rows and index extents, or of reusing the same space when the table is subsequently repopulated with new rows.

To execute the TRUNCATE statement, at least one of the following conditions must be satisfied:

You are the owner of the table.
You hold the Delete access privilege on the table.
You hold the DBA access privilege on the current database.

If an enabled Delete trigger is defined on the table, you must also hold the Alter privilege on the table, even though the TRUNCATE statement does not activate triggers.

Although it requires the Delete privilege for a non-DBA user who does not own the table, TRUNCATE is a data definition language (DDL) statement. Like other DDL statements, TRUNCATE cannot operate on any table outside the database to which you are connected, nor on a table that a concurrent session is reading in Dirty Read isolation mode.

Informix® 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 the TRUNCATE statement within the same transaction. Here the ROLLBACK statement must cancel the entire transaction that includes the TRUNCATE statement. Informix® issues an error if ROLLBACK TO SAVEPOINT (or any other SQL statement except for COMMIT WORK or ROLLBACK WORK without the TO SAVEPOINT clause) immediately follows the TRUNCATE statement.

When you successfully rollback the TRUNCATE statement, no rows are removed from the table, and the storage extents that hold the rows and index partitions continue to be allocated to the table. Only databases with transaction logging can support the ROLLBACK WORK statement.

After the TRUNCATE statement successfully executes, Informix® automatically updates the statistics and distributions for the table and for its indexes in the system catalog to show no rows in the table nor in its dbspace partitions. It is not necessary to run the UPDATE STATISTICS statement immediately after you commit the TRUNCATE statement. If the KEEP STATISTICS operation is used, then TRUNCATE TABLE does not update the system catalog.

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.

The TRUNCATE statement does not reset the serial value of SERIAL, BIGSERIAL, or SERIAL8 columns. To reset the counter of a serial column, use the MODIFY clause of the ALTER TABLE statement, either before or after you execute the TRUNCATE statement.