TRUNCATE TABLE

Purpose

This statement is used to completely empty a specified table while retaining its structure, including partition information. 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 significantly faster, especially for large tables.

• The result of executing the TRUNCATE TABLE statement always shows 0 rows affected.

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

• The TRUNCATE statement cannot be executed during transaction processing or table locking; if it is, 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 corrupted.

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-compatible 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