Empty a table

When you no longer need the data in a table, you can empty it. Emptying a table means deleting all the rows 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 clear 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 clear all the rows in a table. TRUNCATE TABLE is a DDL statement. It does not generate rollback information.

obclient>TRUNCATE TABLE table_name;

Use the DELETE FROM statement

You can use DELETE FROM to clear the rows in a table.

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

• The TRUNCATE TABLE statement drops a table and creates it again. It is much faster than deleting data row by row.

• 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.