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 structure 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 yield the same effect.

Use the TRUNCATE TABLE statement

The TRUNCATE TABLE statement provides a quick and effective method to delete all rows from a table. At the same time, TRUNCATE TABLE is a data definition language (DDL) statement. It does not generate rollback information.

To execute the TRUNCATE TABLE statement on a specified table, you must have the permission to delete and create the table.

obclient>TRUNCATE TABLE table_name;

Use the DELETE FROM statement

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

Example:

obclient>DELETE FROM table_name;

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

Differences between TRUNCATE TABLE and DELETE FROM

TRUNCATE TABLE and DELETE FROM have the following differences:

• The TRUNCATE TABLE statement deletes 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.