All Products
Search
Document Center

PolarDB:KEY-RANGE

Last Updated:Aug 06, 2024

This topic describes how to create a KEY-RANGE partitioned table.

Syntax

The following statement is used to create one or more KEY-RANGE partitioned tables where each partition may contain one or more subpartitions:

CREATE TABLE [ schema. ]table_name
 table_definition
   PARTITION BY [LINEAR] KEY (column_list)
   SUBPARTITION BY RANGE (expr)
   (partition_definition [, partition_definition] ...)

partition_definition is:

 PARTITION partition_name
        (subpartition_definition [, subpartition_definition] ...)

subpartition_definition is:

SUBPARTITION subpartition_name
   VALUES LESS THAN {value| MAXVALUE}

Parameters

Parameter

Description

table_name

The name of the table.

expr

The expression of the partition. It must be of the INT type. The string type is not supported.

column_list

The list of partitions. It is used in RANGE COLUMNS(). Expressions are not supported.

value

The boundary value of the partition.

MAXVALUE

The maximum value in the partition.

partition_name

The name of the partition. The name must be unique within the table.

subpartition_name

The name of the subpartition. The name must be unique within the table.

Examples

Create a KEY-RANGE partitioned table.

CREATE TABLE sales_key_range
(
  dept_no     varchar(20),
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
 PARTITION BY KEY(dept_no)
 SUBPARTITION BY RANGE(amount)
(
  PARTITION p0(
            SUBPARTITION p0s0 VALUES LESS THAN(10000),
            SUBPARTITION p0s1 VALUES LESS THAN(20000),
            SUBPARTITION p0s2 VALUES LESS THAN(30000),
            SUBPARTITION p0s3 VALUES LESS THAN(MAXVALUE)
        ),
  PARTITION p1(
            SUBPARTITION p1s0 VALUES LESS THAN(10000),
            SUBPARTITION p1s1 VALUES LESS THAN(20000),
            SUBPARTITION p1s2 VALUES LESS THAN(30000),
            SUBPARTITION p1s3 VALUES LESS THAN(MAXVALUE)
        ),
  PARTITION p2(
            SUBPARTITION p2s0 VALUES LESS THAN(10000),
            SUBPARTITION p2s1 VALUES LESS THAN(20000),
            SUBPARTITION p2s2 VALUES LESS THAN(30000),
            SUBPARTITION p2s3 VALUES LESS THAN(MAXVALUE)
        )
);