Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.
Deletes all rows from a table without deleting the table definition.
TRUNCATE TABLE [ owner.]table-name | MATERIALIZED VIEW [ owner.]materialized-view-name
The TRUNCATE statement deletes all rows from the table or materialized view.
After a TRUNCATE statement, the object's schema and all the indexes continue to exist until you execute a DROP statement. The schema definitions and constraints remain intact, and triggers and privileges remain in effect.
table-name can be the name of a base table or a temporary table.
With TRUNCATE TABLE, if all the following criteria are satisfied, the database server performs a fast form of table truncation (fast truncate):
There are no foreign keys either to or from the table.
The TRUNCATE TABLE statement is not executed within a trigger.
The TRUNCATE TABLE statement is not executed within an atomic statement.
If a fast truncation is carried out, individual DELETEs are not recorded in the transaction log, and a COMMIT is carried out before and after the operation. If a fast truncation is not possible, individual DELETES are recorded in the transaction log.
Fast truncation cannot be used within snapshot transactions.
If you attempt to use TRUNCATE TABLE on a table on which an immediate text index is built, or that is referenced by an immediate view, the truncation fails. This does not occur for non-immediate text indexes or materialized views; however, it is strongly recommended that you truncate the data in dependent indexes and materialized views before executing the TRUNCATE TABLE statement on a table, and then refreshing the indexes and materialized views after.
For base tables and materialized views, the TRUNCATE statement requires exclusive access to the table, as the operation is atomic (either all rows are deleted, or none are). Any cursors that were previously opened and that reference the table being truncated must be closed and a COMMIT or ROLLBACK must be executed to release the reference to the table.
For temporary tables, each user has their own copy of the data, and exclusive access is not required when executing the TRUNCATE statement.
To execute this statement, one of the following conditions must be true:
When you truncate a materialized view, you change the status of the view to uninitialized.
Delete triggers are not fired by the TRUNCATE statement.
A COMMIT is performed before and after a TRUNCATE statement is executed.
Individual deletions of rows are not entered into the transaction log, so the TRUNCATE operation is not replicated. Do not use this statement in SQL Remote replication or on a MobiLink remote database.
If the table contains a column defined as DEFAULT AUTOINCREMENT or DEFAULT GLOBAL AUTOINCREMENT, the truncation operation resets the next available value for the column.
The TRUNCATE TABLE statement is optional Language Feature F200. TRUNCATE MATERIALIZED VIEW is not in the standard.
Delete all rows from the SalesOrderItems table:
TRUNCATE TABLE GROUPO.SalesOrderItems;