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

Syntax and examples

• Syntax

  In theALTER TABLE statement, add theWITHOUT VALIDATION keyword.

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

  where partition_definition:

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

• Parameters

  Parameter	Parameters
  table_name	The name of the table.
  column_list	LIST COLUMNS type. The list of partition keys. Expressions are not supported.
  RANGE(expr)	RANGEThe expression used for range partitioning.
  partition_name	The name of the partition.
  value_list	The boundary values of the partition.
  MAXVALUE	The maximum value.

• Example

  Convert the non-partitioned tablet1into 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;

  t1, all data is moved topartition p0 without validation. If you confirm that all data in the non-partitioned table falls withinp0 declared boundary, you can use this syntax to quickly convert the non-partitioned table into a range-partitioned table.

  Note

  • Make sure that all data in the non-partitioned table falls within the boundary of the first partition. Otherwise, after the conversion, data that does not meet the partitioning rules may not be retrieved.

  • When the WITHOUT VALIDATION option is used, all data in the non-partitioned table is moved to the first partition without validation. Make sure that the partition boundary specified in the DDL statement matches all data in the non-partitioned table.

Combined syntax

WITHOUT VALIDATION can be used withINTERVAL partitioned tables, which automatically createRANGE partitions at the same interval. 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;