On this page
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.
TRUNCATE [TABLE] <table_name>;
SingleStoreDB Cloud supports online
TRUNCATE, which means that you can read and write while the table is being truncated.
For Rowstore tables,
TRUNCATEis the preferred method (vs
Blocking: Note that online
TRUNCATEwill 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
New read or write queries are blocked until the in-progress queries are finished and
TRUNCATEhas begun truncating the table.
This blocking period lasts until all in-progress reads and writes have completed. TRUNCATE blocks the table via a lock.
If you are frequently running
TRUNCATEstatements 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
TRUNCATEoperation blocks other queries from starting while it waits for completion of long-running queries.
TRUNCATEoperation throws a timeout error if there are long running DML queries in your workload, which extends the blocking period to several minutes.
Refer to ISSUE: Long Running Queries Blocking DDL Operations and Workload for resolving query timeout errors due to long running queries in a workload.
This command causes implicit commits.
See COMMIT for more information.
Refer to the Permission Matrix for the required permission.
TRUNCATE TABLE table_name;
Last modified: January 9, 2023