TRUNCATE TABLE statement

Description

Deletes all rows from a table without deleting the table definition.

Syntax

Syntax 1

TRUNCATE TABLEowner.]table-name

Syntax 2

TRUNCATE TABLE  [ owner .]table  [ PARTITION partition-name ]

Examples

Example 1

Deletes all rows from the Sale table:

TRUNCATE TABLE Sale

Usage

TRUNCATE TABLE is equivalent to a DELETE statement without a WHERE clause, except that each individual row deletion is not entered into the transaction log. After a TRUNCATE TABLE statement, the table structure and all of the indexes continue to exist until you issue a DROP TABLE statement. The column definitions and constraints remain intact, and permissions remain in effect.

The TRUNCATE TABLE statement is entered into the transaction log as a single statement, like data definition statements. Each deleted row is not entered into the transaction log.

The partition clause specifies which partition to truncate. It does not affect data in other partitions.


Side effects

None.

Standards

Permissions

See also

DELETE statement

Chapter 10, “Transactions and Versioning” in System Administration Guide: Volume 1