TRUNCATE TABLE

The TRUNCATE TABLE statement is similar to a DELETE statement that deletes all rows.

In TimesTen Classic, the TRUNCATE operation is faster than the DELETE operation in most circumstances, as DELETE removes each row individually.

In TimesTen Scaleout, TRUNCATE TABLE is similar to a DDL statement that invalidates all commands that depend on the table being truncated. It is preferable to use the DELETE statement rather than the TRUNCATE statement to delete all rows in a table.

Required privilege

No privilege is required for the table owner.

DELETE for another user's table.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

TRUNCATE TABLE [Owner.]TableName

Parameters

Parameter	Description
`[Owner.]TableName`	Identifies the table to be truncated.

Description

TRUNCATE is a DDL statement. A commit is performed before and after execution of the TRUNCATE statement.
If your table has out of line columns and there are millions of rows to truncate, consider calling the ttCompact built-in procedure to free memory.
Concurrent read committed read operations are allowed, and semantics of the reads are the same as for read committed reads in presence of DELETE statements.
TRUNCATE is allowed even when there are child tables. However, child tables need to be empty for TRUNCATE to proceed. If any of the child tables have any rows in them, TimesTen returns an error indicating that a child table is not empty.
TRUNCATE is not supported with any detail table of a materialized view, table that is a part of a cache group, or temporary table.
When a table contains out of line varying-length data, the performance of TRUNCATE TABLE is similar to that of DELETE statement that deletes all rows in a table. For more details on out-of line data, see "Numeric Data Types".
Where tables are being replicated, the TRUNCATE statement replicates to the subscriber, even when no rows are operated upon.
When tables are being replicated with timestamp conflict checking enabled, conflicts are not reported.
DROP TABLE and ALTER TABLE operations cannot be used to change hash pages on uncommitted truncated tables.

Examples

To delete all the rows from the recreation.clubs table, use:

TRUNCATE TABLE recreation.clubs;