OPTIMIZE PARTITION reclaims unused space and defragments data files in a partition or subpartition. Run it after deleting large numbers of rows from a partition, or after updating rows that contain variable-length columns (VARCHAR, BLOB, or TEXT).
When to run OPTIMIZE PARTITION
Run OPTIMIZE PARTITION after:
Deleting a large number of rows from a partition or subpartition
Updating rows that contain variable-length column types (VARCHAR, BLOB, or TEXT)
Syntax
ALTER TABLE table_name OPTIMIZE PARTITION {partition_names | ALL}partition_names is:
{partition_name | subpartition_name}Use the ALL keyword to optimize all partitions in the table at once, instead of listing individual partition names.
Parameters
| Parameter | Description |
|---|---|
table_name | The name of the partitioned table. Optionally schema-qualified. |
partition_name | The name of a partition to optimize. |
subpartition_name | The name of a subpartition to optimize. |
Examples
Optimize a specific partition and a specific subpartition:
ALTER TABLE sales_range_list OPTIMIZE PARTITION q1_2012, p5;Optimize a single subpartition:
ALTER TABLE sales_range_list OPTIMIZE PARTITION p5;Optimize all partitions in the table:
ALTER TABLE sales_range_list OPTIMIZE PARTITION ALL;