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 nameThe 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 ...