PolarDB:Convert partitioned tables

Prerequisites

Before you begin, ensure that you have:

• A PolarDB for MySQL 8.0 cluster with revision version 8.0.2.2.0 or later

To check your revision version, see Query the engine version.

How it works

An interval range partitioned table uses a transition point to separate explicitly defined range partitions from automatically managed interval partitions. All data up to the transition point falls into named range partitions. Data beyond that point triggers automatic partition creation at the specified interval — no manual ADD PARTITION required.

When you remove the interval definition from a table, all existing interval partitions become standard range partitions.

Switching rules

After converting to an interval range partitioned table, the database creates new partitions automatically when data is inserted beyond the transition point. You cannot explicitly add partitions to an interval range partitioned table.

Syntax

Syntax 1 — redefine the full partition scheme:

ALTER TABLE table_name partition_options;

Where partition_options is:

PARTITION BY
    { RANGE{(expr) | COLUMNS(column_list)} }
    { INTERVAL(type, expr) | INTERVAL(expr) }
    [(partition_definition [, partition_definition] ...)]

Where partition_definition is:

PARTITION partition_name
        [VALUES LESS THAN {expr | MAXVALUE}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

Syntax 2 — toggle the interval on an existing range partitioned table (requires 8.0.2.2.1 or later):

ALTER TABLE table_name SET { INTERVAL(type, expr) | INTERVAL(expr) };

To disable interval partitioning and revert to a range partitioned table, call SET INTERVAL() with no arguments.

Parameters

Convert a common table to an interval range partitioned table

Define an initial range partition to set the transition point. Data inserted beyond that boundary automatically creates new monthly (or any other interval) partitions.

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey  BIGINT NOT NULL,
  orderdate DATE NOT NULL
);

-- Convert to an interval range partitioned table.
-- Partition p0 holds data before 2021-10-01 (the transition point).
-- Data from 2021-10-01 onward creates monthly partitions automatically.
ALTER TABLE orders
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);

Convert a range partitioned table to an interval range partitioned table

Two methods are available. Both add an interval to an existing range partitioned table.

Method 1: Redefine the partition scheme (requires 8.0.2.2.0 or later)

Use this method to change both the partition key and the interval in a single statement.

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey  BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE(orderkey) (
  PARTITION p0 VALUES LESS THAN(10000000)
);

-- Redefine the partition scheme: switch the key to orderdate and add a monthly interval.
ALTER TABLE orders
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);

Method 2: Set the interval on an existing range partitioned table (requires 8.0.2.2.1 or later)

Use this method when the table is already partitioned by the correct key and you only need to activate interval partitioning.

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey  BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(orderdate) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);

-- Activate interval partitioning with a monthly interval.
ALTER TABLE orders SET INTERVAL(MONTH, 1);

Convert an interval range partitioned table to a range partitioned table

Removing the interval definition converts all existing interval partitions to named range partitions.

Method 1: Redefine the partition scheme (requires 8.0.2.2.0 or later)

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey  BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE(orderkey) INTERVAL(100000) (
  PARTITION p0 VALUES LESS THAN(10000000)
);

-- Redefine without the INTERVAL clause to convert to a range partitioned table.
ALTER TABLE orders
PARTITION BY RANGE COLUMNS(orderdate) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);

Method 2: Clear the interval (requires 8.0.2.2.1 or later)

Call SET INTERVAL() with no arguments to disable interval partitioning. Existing interval partitions become range partitions.

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey  BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE(orderkey) INTERVAL(100000) (
  PARTITION p0 VALUES LESS THAN(10000000)
);

-- Disable interval partitioning. Existing interval partitions are retained as range partitions.
ALTER TABLE orders SET INTERVAL(); /* Switch an interval range partitioned table to a range partitioned table without specifying the interval type and value. */