Modifying Partitioned Tables

ADD PARTITION

Description

Adds partitions and subpartitions to an existing partitioned table.

Syntax

ALTER TABLE…ADD PARTITION adds partitions and subpartitions to an existing partitioned table and the table must already be subpartitioned.

New partitions and subpartitions must use the same partitioning type as the existing ones. The new partitioning rules must reference and define the same columns specified in the existing partitioning rules.

ALTER TABLE table_name ADD PARTITION partition_definition;

{list_partition | range_partition | hash_partition | key_partition}

PARTITION [partition_name] VALUES IN (value[, value]...) [TABLESPACE tablespace_name], (subpartition, ...)

PARTITION partition_name VALUES LESS THAN (value[, value]...) [TABLESPACE tablespace_name] [(subpartition, ...)]

PARTITION partition_name [TABLESPACE tablespace_name] (subpartition, ...)

{list_subpartition | range_subpartition | hash_partition | key_partition}

SUBPARTITION [subpartition_name] VALUES IN (value[, value]...) [TABLESPACE tablespace_name]

SUBPARTITION [subpartition_name ] VALUES LESS THAN (value[, value]...) [TABLESPACE tablespace_name]

SUBPARTITION [subpartition_name ] [TABLESPACE tablespace_name]

Examples

DROP PARTITION

Description: deletes partitions and subpartitions along with the stored data.

Syntax

ALTER TABLE table_name DROP PARTITION partition_names;

This statement cannot drop subpartitions individually or HASH and KEY partitions.

Examples

Drop partition s1 from the sales_order table:

ALTER TABLE sales_order DROP PARTITION s1;

TRUNCATE PARTITION

Description: removes all data from a specified partition or subpartition while preserving its subpartition structure.

Syntax

ALTER TABLE table_name
   TRUNCATE PARTITION partition_name [,partition_name] ...

When this statement is executed on a subpartitioned table with a partition name specified, all subpartitions belonging to that partition are included in this operation.

partition_name

{partition_name | subpartition_name}

Examples

• Truncate data in partition r1 of the part_range_list table:

  ALTER TABLE part_range_list TRUNCATE PARTITION r1;

• Truncate data in subpartition s0 belonging to partition r1 of the part_range_list table:

  ALTER TABLE part_range_list TRUNCATE PARTITION r1_s0;

COALESCE PARTITION

Description: reduces the number of HASH and KEY partitions and their subpartitions and merges the data into other partitions and subpartitions.

Syntax

ALTER TABLE table_name COALESCE PARTITION num;

Examples

• Reduce two partitions from the part_hash_hash table:

  ALTER TABLE part_hash_hash COALESCE PARTITION 2;

• Reduce two partitions from the part_key_key table:

  ALTER TABLE part_key_key COALESCE PARTITION 2;

REORGANIZE PARTITION

Description: reorganizes partitions in LIST or RANGE partitioned tables (such as merging, splitting, or modifying partitions) and automatically redistributes data without loss.

Syntax

ALTER TABLE table_name
    REORGANIZE PARTITION partition_names INTO (partition_definitions)

partition_definitions: {list_partition | range_partition}

subpartition_definition: {list_subpartition | range_subpartition | hash_subpartition | key_subpartition}

Examples

1. Prepare data.

   CREATE TABLE sales_data (
       order_id INT AUTO_INCREMENT,    
       order_date DATE NOT NULL,     
       amount DECIMAL(10,2),
       customer_id INT,
       product_id INT,
       quantity INT,
       status VARCHAR(20),
       PRIMARY KEY (order_id, order_date)
   ) 
   PARTITION BY RANGE (TO_DAYS(order_date))
   SUBPARTITION BY KEY(order_id)
   SUBPARTITIONS 4 (
       PARTITION part_before_2022 VALUES LESS THAN (TO_DAYS('2022-01-01')),
       PARTITION part_2022      VALUES LESS THAN (TO_DAYS('2023-01-01')),
       PARTITION part_2023      VALUES LESS THAN (TO_DAYS('2024-01-01')),
       PARTITION part_future    VALUES LESS THAN MAXVALUE
   );
   
   INSERT INTO sales_data (order_date, amount, customer_id, product_id, quantity, status) VALUES
   ('2020-08-10', 150.00, 101, 2001, 2, 'completed'),
   ('2021-11-05', 250.00, 102, 2002, 1, 'pending'),
   ('2022-07-19', 350.00, 103, 2003, 5, 'shipped'),
   ('2023-04-02', 450.00, 104, 2004, 3, 'completed');

2. Split a partition.

   Split part_2023 into two partitions.

   ALTER TABLE sales_data 
   REORGANIZE PARTITION part_2023 INTO (
       PARTITION part_2023_h1 VALUES LESS THAN (TO_DAYS('2023-07-01')),
       PARTITION part_2023_h2 VALUES LESS THAN (TO_DAYS('2024-01-01'))
   );

3. Merge partitions.

   Merge part_2022 and part_2023 into a single part_2022_2023 partition:

   ALTER TABLE sales_data 
   REORGANIZE PARTITION part_2022, part_2023 INTO (
       PARTITION part_2022_2023 VALUES LESS THAN (TO_DAYS('2024-01-01'))
   );

4. Modify partitions.

   Reorganize all partition definitions to redefine value ranges:

   ALTER TABLE sales_data 
   REORGANIZE PARTITION part_before_2022, part_2022, part_2023, part_future INTO (
       PARTITION p_2020_2021 VALUES LESS THAN (TO_DAYS('2022-01-01')),
       PARTITION p_2022      VALUES LESS THAN (TO_DAYS('2023-01-01')),
       PARTITION p_2023      VALUES LESS THAN (TO_DAYS('2024-01-01')),
       PARTITION p_2024      VALUES LESS THAN (TO_DAYS('2025-01-01')),
       PARTITION p_future    VALUES LESS THAN MAXVALUE
   );

EXCHANGE PARTITION

Description: exchanges a partition or subpartition of a partitioned table with a non-partitioned table that has the same schema.

Description

• Executing ALTER TABLE ... EXCHANGE PARTITION swaps data between tables as follows:
  1. Data originally in target_partition is migrated to source_table.
  2. Data originally in source_table is migrated to target_partition.
• When performing an EXCHANGE PARTITION operation, ensure that source_table shares an identical schema with target_table (meaning that both tables must have identical columns, data types, engines, table attributes, and indexes).

Syntax

ALTER TABLE target_table 
EXCHANGE PARTITION target_partition 
WITH TABLE source_table 
[{WITH | WITHOUT} VALIDATION];

Examples

Exchange partition p2022 of partitioned table sales_data with the non-partitioned table sales_data_temp:

ALTER TABLE sales_data 
    EXCHANGE PARTITION p2022 
    WITH TABLE sales_data_temp;

ANALYZE PARTITION

Description: updates statistics for a partition or subpartition.

Syntax

ALTER TABLE table_name ANALYZE PARTITION {partition_names | ALL}

Where partition_names is:

{partition_name | subpartition_name}

Examples

• Analyze partition part_2022 of the sales_data table:

  ALTER TABLE sales_data ANALYZE PARTITION part_2022;

• Analyze subpartition part_2022_sp1 of the sales_data table:

  ALTER TABLE sales_data ANALYZE PARTITION part_2022_sp1;

CHECK PARTITION

Description: checks a partition or subpartition and indicates whether its data or indexes are corrupted.

Syntax

ALTER TABLE table_name CHECK PARTITION {partition_names | ALL}

Where partition_names is:

{partition_name | subpartition_name}

Examples

• Check partition part_2022 of the sales_data table:

  ALTER TABLE sales_data CHECK PARTITION part_2022;

• Check subpartition part_2022_sp1 of the sales_data table:

  ALTER TABLE sales_data CHECK PARTITION part_2022_sp1;

OPTIMIZE PARTITION

Description

If a large number of rows are deleted from a partition or subpartition, or if updates are made to variable-length rows (containing VARCHAR, BLOB, or TEXT columns), run ALTER TABLE ... OPTIMIZE PARTITION to optimize the partition or subpartition, reclaim unused space, and defragment partition data files.

Syntax

ALTER TABLE table_name OPTIMIZE PARTITION {partition_names | ALL}

Where partition_names is:

{partition_name | subpartition_name}

Examples

• Optimize partition part_2022 of the sales_data table:

  ALTER TABLE sales_data OPTIMIZE PARTITION part_2022;

• Optimize subpartition part_2022_sp1 of the sales_data table:

  ALTER TABLE sales_data OPTIMIZE PARTITION part_2022_sp1;

REBUILD PARTITION

Description: rebuilds a partition.

Syntax

ALTER TABLE table_name REBUILD PARTITION {partition_names | ALL}

Examples

Rebuild partition part_2022 of the sales_data table:

ALTER TABLE part_range_list REBUILD PARTITION part_2022;

REPAIR PARTITION

Description: repairs a corrupted partition or subpartition.

Syntax

ALTER TABLE table_name REPAIR PARTITION {partition_names | ALL}

Where partition_names is:

{partition_name | subpartition_name}

Examples

• Repair partition part_2022 of the sales_data table:

  ALTER TABLE sales_data REPAIR PARTITION part_2022;

• Repair subpartition part_2022_sp1 of the sales_data table:

  ALTER TABLE sales_data REPAIR PARTITION part_2022_sp1;

REMOVE PARTITION

Description: removes partition and subpartition structures from a partitioned table.

Syntax

ALTER TABLE ... REMOVE PARTITIONING removes partition and subpartition structures from a partitioned table and converts it into a non-partitioned table without data loss.

ALTER TABLE table_name REMOVE PARTITIONING

Examples

Remove all partitioning structures from the sales_data table:

ALTER TABLE sales_data REMOVE PARTITIONING;