This topic describes how to optimize a partition or a subpartition, reclaim the unused space, and defragment the space of the data files.
Description
If you delete a large number of rows from a partition or a subpartition, or modify a row of variable-length data (columns of the VARCHAR, BLOB, or TEXT type), you can execute the ALTER TABLE... OPTIMIZE PARTITION statement to reclaim the unused space and defragment the space of data files.
Syntax
The ALTER TABLE ... OPTIMIZE PARTITION statement is used to optimize a partition or a subpartition, reclaim the unused space, and defragment the space of data files.
ALTER TABLE table_name OPTIMIZE PARTITION {partition_names | ALL}partition_names is:{partition_name | subpartition_name}Parameters
| Parameter | Description |
| table_name | The name (optionally schema-qualified) of the partitioned table. |
| partition_name | The name of the partition. |
| subpartition_name | The name of the subpartition. |
Examples
Optimize the
q1_2021 partition and the p5 subpartition of the sales_range_list table:ALTER TABLE sales_range_list optimize PARTITION q1_2012,p5;Optimize the
p5 subpartition of the sales_range_list table:ALTER TABLE sales_range_list optimize PARTITION p5;