Outdated Version

You are viewing an older version of this section. View current production version.

TRUNCATE

Removes all rows from the table. TRUNCATE is equivalent to running a DELETE which removes all the rows from the table but without all the overhead of deleting one row at a time.

Syntax

TRUNCATE [TABLE] tbl_name

Remarks

  • SingleStore DB supports online TRUNCATE, which means that you can read and write while the table is being truncated. Note that online TRUNCATE will not begin truncating the table, but it will wait until all DML queries that were already running on the table finish. This allows any in-progress queries to complete execution before truncating the table, and ensures consistency of results from queries on the table since the time of execution of TRUNCATE. As soon as the in-progress reads and writes complete and the TRUNCATE command begins truncating the table, new reads and writes will proceed as normal. This blocking period usually lasts approximately for milliseconds. The TRUNCATE operation throws a timeout error if there are long running DML queries in your workload, which extends the blocking period to several minutes.

    If you are frequently running TRUNCATE statements on a table and have a lot of long-running queries on that table, then your normal workload may experience some periods of delay since the TRUNCATE operation blocks other queries from starting while it waits for completion of long-running queries.

    Refer to the Query Errors) topic for resolving query timeout errors due to long running queries in a workload.

  • This command must be run on the master aggregator node (see Node Requirements for SingleStore DB 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.

Example

TRUNCATE TABLE mytbl;

Related Topics