Empty a table

If you no longer need the data in a table, you can empty the table. Emptying a table means clearing all the data in the table.

OceanBase Database allows you to use the TRUNCATE TABLE or the DELETE FROM statement to empty a table. After a table is emptied, the table schema is retained, including its partitioning information. From a logical perspective, using TRUNCATE TABLE and using DELETE FROM to delete all the rows in a table have the same effect.

Use the TRUNCATE TABLE statement

The TRUNCATE TABLE statement provides a quick and effective method to delete all the rows in a table. TRUNCATE TABLE is a DDL statement. It does not generate rollback information.

To execute the TRUNCATE TABLE statement on a specified table, you must have privileges to drop and create the table. For information about how to query and grant user privileges, see Grant direct privileges.

obclient>TRUNCATE TABLE table_name;

Use the DELETE FROM statement

You can also use DELETE FROM to clear the rows in a table. DELETE FROM is a DML statement and supports rollback operations.

Here is an example:

obclient>DELETE FROM table_name;

Using DELETE FROM to empty a table that has many rows consumes a large number of system resources.

Differences between TRUNCATE TABLE and DELETE FROM

TRUNCATE TABLE and DELETE FROM have the following differences:

• The output of TRUNCATE TABLE always indicates that 0 rows were affected.

• When you use TRUNCATE TABLE, the table management program does not record the last AUTO_INCREMENT value, but resets it to zero.

• You cannot perform the TRUNCATE TABLE operation on a table that is involved in a transaction or locked.

• If the table definition file is valid, you can use the TRUNCATE TABLE statement to recreate the table as an empty table, even if the data or indexes are corrupted.