Truncating table in Informix

This article describes how to use truncate table feature in Informix Dynamic Server (IDS) versions 10 or higher.

TRUNCATE is a SQL keyword that quickly deletes active rows from a table and the b-tree structures of its indexes. The active rows are deleted without dropping the table or its schema, access privileges, triggers, constraints, and other attributes.

The TRUNCATE table statement quickly removes all rows from a table and all corresponding b-tree index data. The space formerly occupied by data rows and indexes becomes available for other tables, or can optionally reserve the space for reuse in subsequent operations on the same table or index partition.

TRUNCATE is not equivalent to DROP TABLE. After TRUNCATE successfully executes, the specified table (and all its columns, and any synonyms, views, constraints, indexes, triggers, and access privileges) still exists in the database schema, but with no rows of data.

The truncate statement works on tables that contain any type of columns, including smart large objects.

The table that you truncate can be a raw, temporary, or standard table, synonym (no view name). It can also be a virtual table, or a table with a virtual-index interface, provided that the virtual table or the virtual-index interface has a valid am_truncate() purpose function that was explicitly registered in the system catalog table.

The Dynamic Server automatically updates the statistics and distributions for the table and its indexes. It is not necessary to run the UPDATE STATISTICS statement immediately after committing the TRUNCATE statement.

Dynamic Server always logs the TRUNCATE operation, even for a non-logging database.


Advantages

Removing rows with the TRUNCATE TABLE statement is faster than removing them with the DELETE statement. It 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.

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.

The triggered action of any enabled trigger on table is ignored.


Restrictions
 

• The table cannot be truncated if it has an open cursor or dirty readers.
• The table cannot be truncated if it has at least one non-empty child table with referential constraints.
• To truncate a table that has an R-tree, you must first drop the index. After truncate the table re-create the R-tree index.
• A view name can’t be use with truncate statement.
• The TRUNCATE statement does not automatically reset the serial value of a column. To reset the serial value of a column, you must do so explicitly after you run the TRUNCATE statement.

What products have it?

The feature is available only in these products and versions.

Product Name	Product Version(s)	Hardware Vendor	Operating System
IBM Informix® Dynamic Server	10.00.xC3 and above	All	All

x can be T, U, H, or F depending on your operating system


How to use it?

You must be the owner of the table or have DBA privilege to use the TRUNCATE TABLE statement.

Syntax
TRUNCATE [TABLE] [owner].<tablename> [DROP STORAGE | REUSE STORAGE]

• • owner
    • • The table owner
    tablename
    • • Name of the table that needs to truncate

The TABLE keyword has no effect, but it can be included to make it more legible.

By default, all of the partition extents that had been allocated to the specified table and to its indexes are released after TRUNCATE successfully executes, but you can include the DROP STORAGE keywords to achieve the same result, making the storage space available for other database objects. You can keep the same storage space allocated to the same table for subsequently loaded data, specify the REUSE STORAGE keywords to prevent the space from being de-allocated.

Examples
 

• All following statements have the same effect, delete all rows and any related index data from the sales table:
  
  
  • TRUNCATE TABLE sales
    TRUNCATE sales
    TRUNCATE sales DROP STORAGE
  
  
  
   
• The following statement deletes all rows and any related index data from the sales table but prevent the space from being de-allocated:
  
  
  • TRUNCATE TABLE sales REUSE STORAGE