Deletes all rows from a table or materialized view without deleting the
table definition.
Syntax
Syntax 1
TRUNCATE
TABLE [ <owner>.]<table-name>
| MATERIALIZED VIEW <owner>.] <materialized-view-name>
Syntax
2
TRUNCATE TABLE [ <owner> .]<table>
[ PARTITION <partition-name>
| SUBPARTITION <subpartition-name> ]
Parameters
(back to top)
- PARTITION specifies which partition to truncate, and does
not affect data in other partitions.
Note Specifying an
RLV-enabled table in the PARTITION clause results in an
error.
- SUBPARTITION to truncate tables partitioned by a composite
partitioning scheme.
Note Specifying an RLV-enabled table in
the SUBPARTITION clause results in an error.
Usage
(back to top)
TRUNCATE 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 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.
Standards
(back to top)
- SQL—Vendor extension to ISO/ANSI SQL grammar.
- SAP Database products—Supported by SAP ASE.
Permissions
(back to top)
Requires one of:
- TRUNCATE ANY TABLE system privilege.
- ALTER ANY TABLE system privilege.
- ALTER ANY OBJECT system privilege.
- TRUNCATE
privilege on the table.
- You own the object.
For both temporary and base tables, you can execute TRUNCATE TABLE while other users have read access to
the table. This behavior differs from SAP SQL Anywhere, which requires exclusive access to truncate a base
table. SAP IQ table versioning ensures
that TRUNCATE TABLE can occur while other users
have read access; however, the version of the table these users see depends on when
the read and write transactions commit.