PolarDB:Convert a non-partitioned table into a range-partitioned table

Limits

If a non-partitioned table contains fields that are added by using the INSTANT ADD COLUMN statement, you cannot convert the table into a range-partitioned table.

Usage

Convert a non-partitioned table into a range-partitioned table

Syntax

Add the WITHOUT VALIDATION keyword to the ALTER TABLE statement.

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

partition_definition:

PARTITION partition_name
       VALUES LESS THAN {(value | value_list) | MAXVALUE}

Parameters

Example

Convert the t1 non-partitioned table into a range-partitioned table.

CREATE TABLE t1 (
`a` int ,
`b` int ,
Primary Key(a, b));

insert into t1 values(1,1),(2,1),(3,1),(4,1),(111,111),(3333,333);

alter table t1 partition by range(a) (
  partition p0 values less than (100),
  partition p1 values less than (200)
) WITHOUT VALIDATION;

All data in the t1 table is transferred into the p0 partition without verification. If you confirm that all data in the non-partitioned table falls within the value range of the p0 partition, you can execute this statement to convert the non-partitioned table into a range-partitioned table.

Usage in other partitioned tables

The WITHOUT VALIDATION keyword for converting a non-partitioned table into a range-partitioned table can be used for INTERVAL-partitioned tables to automatically create RANGE partitions at the same intervals.

Example

CREATE TABLE t1(
 ID int,
 DATE DATE,
 PRIMARY KEY (ID,DATE)
);
ALTER TABLE t1 
partition by RANGE COLUMNS(date) INTERVAL(DAY, 1) (
  PARTITION p0 VALUES LESS THAN ('2023-01-31')
) without validation;

Performance