TRUNCATE

Syntax

TRUNCATE [TABLE] <table_name>

Remarks

• For rowstore tables, TRUNCATE is the preferred method (vs DELETE).

• This command can be run on the master aggregator or a child aggregator (refer to Node Requirements for SingleStore Commands ).

• Table memory can be freed when the TRUNCATE command is run. For information on when/how much table memory is freed when this command is run, see Memory Management.

• The TRUNCATE command does not support temporary tables.

• This command causes implicit commits. Refer to COMMIT for more information.

• Refer to the Permissions Matrix for the required permissions.

Locking Behavior

SingleStore supports online TRUNCATE, which waits for DML queries that are already running on the target table to finish before it begins truncating the table. This allows any in-progress queries to complete before the truncate operation starts. New reads and writes on the table are blocked once the TRUNCATE command is run on the table. This blocking mechanism preserves the consistency of the results. Once the ongoing read and write operations are completed, TRUNCATE acquires a database-level lock on the target table and then truncates the table.

Because TRUNCATE and ALTER TABLE share the database-level DDL operation lock, a concurrent ALTER TABLE or TRUNCATE on another table in the same database waits while the currently running operation holds the lock. This includes the period when an ongoing TRUNCATE is waiting for DML on its target table.

TRUNCATE uses the same distributed DDL machinery as ALTER TABLE. On a given database, only one ALTER TABLE or TRUNCATE operation can be in the two-phase commit (2PC) or commit path at a time.

Additionally, concurrent ALTER TABLE and TRUNCATE operations are not guaranteed to complete in the order in which they were issued. Applications must not rely on FIFO ordering among ALTER TABLE and TRUNCATE statements waiting for the database-level DDL operation lock.

For online ALTER TABLE, data-population that occurs after the 2PC commit can overlap with other operations after the database-level commit lock is released. TRUNCATE does not have a long-running data-population phase.

Example

TRUNCATE TABLE testTable;

Related Topics

• DELETE

• ALTER TABLE