11.1.5 Deleting all rows from a base table

If you want to delete all rows from a base table, we recommend that as a rule, you execute the TRUNCATE TABLE data manipulation SQL statement.

Executing the TRUNCATE TABLE statement allows you to delete all rows more quickly than using the DELETE statement (with the WHERE clause omitted). Moreover, since segments are released, you can reuse the area where the deleted row data was stored.

The following table shows the differences between the TRUNCATE TABLE and DELETE statements.

Table 11‒1: Differences between TRUNCATE TABLE and DELETE statements
No.	Compared item	TRUNCATE TABLE statement	DELETE statement (with the WHERE clause omitted)
1	Processing performance	High-speed Can delete all rows more quickly than the `DELETE` statement.	Low-speed Deleting all rows takes longer compared with the `TRUNCATE TABLE` statement.
2	Releasing of segments	Released The area that stored the deleted row data can be reused.	Not released Although the deleted rows become invalid, they are not deleted from the disk. Consequently, the area that stored the deleted row data cannot be reused. If this statement is executed for a column store table, the number of used segments might increase because invalid row information pages need to be reserved.
3	Need to execute the `COMMIT` statement	Automatically committed When the processing terminates normally, it is committed as soon as it is completed. Execution of the `COMMIT` statement is unnecessary.	Not automatically committed The `COMMIT` statement must be executed after processing is complete.
4	Locking (whether the statement can be concurrently executed on the same table with the `SELECT` statement)^#	Cannot be concurrently executed When the `TRUNCATE TABLE` statement is executed, it locks the table to be processed in exclusive mode. Therefore, the `SELECT` and `TRUNCATE TABLE` statements cannot be concurrently executed on the same table.	Can be concurrently executed The `SELECT` and `DELETE` statements can be concurrently executed on the same table.
5	Recommended execution timing	When you want to delete all rows quickly When you want to reuse the area that was allocated to the deleted row data	When you want to execute deletion and retrieval concurrently on the same table When you want to use the statement as an element of a transaction

#: For details about the locked resources that are reserved when the TRUNCATE TABLE and DELETE statements are executed, and their lock modes, see 2.10.4 Locked resources that are reserved and their lock modes.

The following shows a specification example of deleting all rows from a base table.

Specification example

All rows are deleted from the sales history table (SALESLIST).

TRUNCATE TABLE "SALESLIST"

For details about the specification format and rules for the TRUNCATE TABLE statement, see TRUNCATE TABLE (delete all rows in a base table) in Data Manipulation SQL in the manual HADB SQL Reference.

Important

Before you delete all rows, check whether you need to re-execute the command for the base table for which the target rows are defined (whether the base table is non-updatable). For details about the check method, see (1) Checking whether a base table is non-updatable in 10.9.2 Checking the status and usage of a base table.

If re-execution of the command is necessary: Re-execute the command. Then, delete all rows.

If you deleted all rows when command re-execution was necessary (when the base table was non-updatable)

Temporary work files created by the interrupted command might remain. To delete the temporary work files, you must release all base tables from the non-updatable status.

For details about deleting temporary work files, see (2) When there are unneeded temporary work files on the disk in 15.2.5 Steps to take in the event of a shortage of disk space for storing temporary work files during command execution.

To Page Top