About table clearing

You can use the DELETE (without conditions) or TRUNCATE statement to clear a table in OceanBase Database in Oracle mode.

Prerequisites

The current user has the DELETE privilege for the target table.

DELETE (without conditions)

Description

When you use the DELETE statement on a table, you can choose whether to specify conditions.

• A DELETE statement with conditions such as WHERE deletes the data meeting the specified condition from the table.

• A DELETE statement without conditions clears the table.

Syntax

DELETE [hint_options] [FROM] table_factor

Parameters

Examples

Clear the data in the dws_ny table.

1. Query the data in the dws_ny table.

   obclient> SELECT * FROM dws_ny;
   +------+-----------+
   | COL1 | COL2      |
   +------+-----------+
   |    1 | 11-NOV-70 |
   |    2 | 11-NOV-71 |
   |    3 | 11-NOV-71 |
   +------+-----------+
   3 rows in set
   

2. Clear the data in the dws_ny table.

   obclient> DELETE FROM dws_ny;
   Query OK, 3 rows affected
   

3. Query the data in the dws_ny table again.

   obclient> SELECT * FROM dws_ny;
   Empty set
   

TRUNCATE statement

Description

You can use this statement to truncate a specified table but retain the table structure, including the partitions of the table. Logically, this statement is equivalent to the DELETE FROM statement that is used to delete all rows.

To execute the TRUNCATE TABLE statement, you must have the DROP and CREATE privileges on the table.

The TRUNCATE TABLE statement differs from the DELETE FROM statement in the following aspects:

• The TRUNCATE TABLE operation drops a table and creates it again. It is much faster than deleting data row by row, especially for large tables.

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

• The TRUNCATE TABLE statement cannot be executed during transactions or when the table is locked. Otherwise, an error is returned.

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

Syntax

TRUNCATE [TABLE] [schema.]table_name;

Parameters

Examples

Clear the data in the dws_ny table.

1. Query the data in the dws_ny table.

   obclient> SELECT * FROM dws_ny;
   +------+-----------+
   | ID   | DATA      |
   +------+-----------+
   |    1 | 11-NOV-70 |
   |    2 | 11-NOV-71 |
   |    3 | 11-NOV-71 |
   +------+-----------+
   3 rows in set
   

2. Clear the data in the dws_ny table.

   obclient> TRUNCATE dws_ny;
   Query OK, 0 rows affected
   

3. Query the data in the dws_ny table again.

   obclient> SELECT * FROM dws_ny;
   Empty set