Converting a Non-Partitioned Table to a Partitioned Table

Scenarios

A partitioned table is recommended when the data volume of a single non-partitioned table exceeds 1 billion rows or 1 TB. This section describes how to use the ALTER statement to convert a non-partitioned table to a partitioned table. This conversion is supported across all partitioned table types.

Prerequisites

When converting a non-partitioned table to a partitioned table, any existing primary key or unique key on the table must include the partition key. This ensures the uniqueness of data within each partition as well as across the entire table.

Precautions

Converting a non-partitioned table to a partitioned table requires re-reading and re-writing all table data, which operates as a COPY DDL execution. As a result, the conversion takes a long time and all DML operations on the table are blocked during the process.

Syntax

ALTER TABLE table_name 
PARTITION BY RANGE {(expr) | COLUMNS(column_list)} 
(partition_definition [, partition_definition] ...);

The definition of partition_definition is consistent with that used in each type of partitioned table. Subpartitioning is supported.

Examples

The following example uses a non-partitioned table named sales to demonstrate how to convert a non-partitioned table to a partitioned table.

1. Create a non-partitioned table and insert data:

   CREATE TABLE sales (
       id INT NOT NULL,
       sale_date DATE NOT NULL,
       region VARCHAR(10) NOT NULL,
       amount DECIMAL(10,2),
       PRIMARY KEY (id, sale_date, region)
   );
   
   INSERT INTO sales (id, sale_date, region, amount) VALUES
   (1, '2023-01-15', 'east',  100.00),
   (2, '2023-02-20', 'west',  150.00),
   (3, '2023-03-10', 'north', 200.00),
   (4, '2023-04-05', 'south', 250.00),
   (5, '2023-05-12', 'east',  300.00),
   (6, '2023-06-18', 'west',  350.00);

2. Convert the non-partitioned table to a partitioned table.
   • Convert it to a LIST partitioned table:

     ALTER TABLE sales
     PARTITION BY LIST COLUMNS(region) (
         PARTITION p_east  VALUES IN ('east'),
         PARTITION p_west  VALUES IN ('west'),
         PARTITION p_north VALUES IN ('north'),
         PARTITION p_south VALUES IN ('south')
     );

   • Convert it to a HASH partitioned table:

     ALTER TABLE sales
       PARTITION BY HASH(id)
       PARTITIONS 4;

   • Convert it to a RANGE partitioned table:

     ALTER TABLE sales
     PARTITION BY RANGE (YEAR(sale_date)) (
         PARTITION p2022 VALUES LESS THAN (2023),
         PARTITION p2023 VALUES LESS THAN (2024),
         PARTITION p2024 VALUES LESS THAN (2025),
         PARTITION p_future VALUES LESS THAN MAXVALUE
     );