SQL in a Nutshell

Name

TRUNCATE TABLE

Synopsis

The TRUNCATE TABLE command is a non-ANSI statement that removes all rows from a table without logging the individual row deletes. It is a very handy command because it quickly erases all the records in a table without altering the table structure, while taking very little space in the redo logs or transaction logs. However, it has a dark side; since it is not logged, it cannot be recovered or backed up.

Vendor	Command
SQL Server	Supported
MySQL	Not supported
Oracle	Supported
PostgreSQL	Supported

SQL99 Syntax and Description

TRUNCATE TABLE name

The TRUNCATE TABLE statement has the same effect as a DELETE statement with no WHERE clause; both erase all rows in a given table. However, there are two important differences. TRUNCATE TABLE is faster, and it is non-logged, meaning it cannot roll back if issued in error.

Typically, TRUNCATE TABLE does not activate triggers and does not function when foreign keys are in place on a given table.

Example

This example removes all data from the publishers table:

TRUNCATE TABLE publishers

Oracle Syntax and Variations

TRUNCATE { CLUSTER [owner.]cluster 
   | TABLE [owner.]table [{PRESERVE | PURGE} SNAPSHOT LOG]}
[{DROP | REUSE} STORAGE]

Oracle allows a table or an indexed cluster (but not a hash cluster) to be truncated.

When truncating a table, Oracle allows the option of preserving or purging the snapshot log, if one is defined on the table. PRESERVE maintains the snapshot log when the master table is truncated, while ...