When the data in a table is no longer needed, you can delete all rows from the table to truncate it.
OceanBase AI Database supports the TRUNCATE TABLE and DELETE FROM statements to truncate a specified table while retaining the table structure, including the partition information defined in the table. Logically, the execution result of the TRUNCATE TABLE statement is the same as that of the DELETE FROM statement used to delete all rows.
Use the TRUNCATE TABLE statement
The TRUNCATE TABLE statement provides a fast and efficient way to delete all rows from a table. It is also a DDL statement and does not generate any rollback information.
Notice
To execute the TRUNCATE TABLE statement on a specified table, you must have the privileges to drop and create the table.
obclient>TRUNCATE TABLE table_name;
Use the DELETE FROM statement
Use the DELETE FROM statement to delete rows from a table.
obclient>DELETE FROM table_name;
When using the DELETE FROM statement to truncate a table, if the table has many rows, it consumes a significant amount of system resources.
Differences between the TRUNCATE TABLE and DELETE FROM statements
The
TRUNCATE TABLEoperation deletes and recreates the table, which is much faster than deleting rows one by one.The execution result of the
TRUNCATE TABLEstatement always shows 0 affected rows.When using the
TRUNCATE TABLEstatement, the table manager does not record the last usedAUTO_INCREMENTvalue; instead, it restarts the count from 1.You cannot execute the
TRUNCATE TABLEoperation during transaction processing or when the table is locked.As long as the table definition file is valid, you can use the
TRUNCATE TABLEstatement to recreate the table as an empty table, even if the data or index files are corrupted.