TRUNCATE TABLE

Purpose

This statement is used to completely empty a specified table while retaining its structure, including the partition information defined in the table. Logically, this statement is equivalent to the DELETE FROM statement for removing all rows.

Limitations and considerations

The TRUNCATE TABLE statement differs from the DELETE FROM statement in the following ways:

• The TRUNCATE operation deletes and recreates the table, which is much faster than deleting rows one by one, especially for large tables.

• The execution result of the TRUNCATE TABLE statement always shows the number of affected rows as 0.

• When using the TRUNCATE TABLE statement, the table management program does not remember the last used AUTO_INCREMENT value, but starts counting from the beginning.

• The TRUNCATE statement cannot be executed during transaction processing or table locking. If it is used, an error will be returned.

• As long as the table definition file is valid, the TRUNCATE TABLE statement can recreate the table as an empty table, even if the data or index files are damaged.

Privilege requirements

To execute the TRUNCATE TABLE statement, the current user must have the DROP privilege on the table. For more information about OceanBase Database privileges, see Privilege classification in Oracle mode.

Syntax

TRUNCATE [TABLE] [schema.]table_name;

Parameters

Examples

Empty the tb1 table completely.

obclient> TRUNCATE TABLE tbl1;
Query OK, 0 rows affected

References

Clear a table