Updated on 2024-05-20 GMT+08:00

TRUNCATE PARTITION

You can run this command to quickly clear data in a partition. The function is similar to that of DROP PARTITION. The difference is that TRUNCATE PARTITION deletes only data in a partition, and the definition and physical files of the partition are retained. You can clear a partition by specifying the partition name or partition value.

  • Running this command will invalidate the global index. You can use the UPDATE GLOBAL INDEX clause to update the global index synchronously or rebuild the global index.

You can run ALTER TABLE TRUNCATE PARTITION to clear any partition in a specified partitioned table.

For example, truncate the partition date_202005 in the range partitioned table range_sales by specifying the partition name and update the global index.
ALTER TABLE range_sales TRUNCATE PARTITION date_202005 UPDATE GLOBAL INDEX;
Alternatively, truncate the partition corresponding to the partition value '2020-05-08' in the range partitioned table range_sales. Global indexes become invalid after this command is executed because the UPDATE GLOBAL INDEX clause is not used.
ALTER TABLE range_sales TRUNCATE PARTITION FOR ('2020-05-08');