Empty a table

If the data in a table is no longer needed, you can delete all the data in the table, which is referred to as emptying the table.

OceanBase Database allows you to use the TRUNCATE TABLE or DELETE FROM statement to empty a table, while preserving the table schema, including the defined partition information. In terms of logic, the output of TRUNCATE TABLE is the same as that of DELETE FROM used to delete all rows.

Use the TRUNCATE TABLE statement

The TRUNCATE TABLE statement provides a fast and effective method to delete all rows in a table. As a DDL statement, TRUNCATE TABLE does not generate any rollback information.

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

obclient>TRUNCATE TABLE table_name;

Use the DELETE FROM statement

You can also use DELETE FROM to delete rows from a table. As a DML statement, DELETE FROM can be rolled back.

Here is an example:

obclient>DELETE FROM table_name;

When using DELETE FROM to empty a table, it may consume a large amount of system resources if the table has many rows.

Differences between TRUNCATE TABLE and DELETE FROM

TRUNCATE TABLE and DELETE FROM have the following differences:

• The output of TRUNCATE TABLE always shows 0 affected rows.

• When you use TRUNCATE TABLE, the table manager does not record the last AUTO_INCREMENT value, but starts counting from the beginning.

• TRUNCATE TABLE cannot be performed on a locked table or a table in a transaction.

• As long as the table definition file is valid, TRUNCATE TABLE can be used to re-create a table as an empty table, even if the data or index files have been corrupted.