TRUNCATE TABLE

TRUNCATE TABLE

Syntax

truncate ::=

Prerequisites

The SYS user, the owner of the schema containing the table, and users having the ALTER ANY TABLE system privilege can execute the TRUNCATE TABLE statement.

Description

The TRUNCATE TABLE statement is used to remove all records from the specified table.

user_name

This is used to specify the name of the owner of the table to be truncated. If omitted, Altibase will assume that the table belongs to the schema of the user connected via the current session.

tbl_name

This is used to specify the name of the table to be truncated.

If the name of a queue table is specified in tbl_name, all ENQUEUE messages are also deleted at the same time.

TRUNCATE vs DELETE

When the TRUNCATE statement is executed, all of the pages in the table are returned to the database as free pages. Therefore, these pages are available for use by other tables. In contrast, when the DELETE statement is used to remove all of the rows from a table, any pages that are emptied are not returned to the database, but remain in a state in which they are reserved for future use by the same table, meaning that memory usage is not reduced.

Because the TRUNCATE statement is a DDL statement, it cannot be rolled back once it has executed successfully.

Consideration

Once the records have been successfully deleted, they cannot be recovered. However, if an error occurs before the completion of execution of the statement, or in the event of a server error, the statement can be rolled back.

Example

<Query> Use the TRUNCATE statement to remove all data from the employees table.

iSQL> TRUNCATE TABLE employee;
Truncate success.