All Products
Search
Document Center

PolarDB:KEY-LIST

Last Updated:Feb 06, 2024

This topic describes how to create a key-list partitioned table.

Syntax

The following statement is used to create one or more key-list partitioned table where each partition may contain one or more subpartitions.

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

partition_definition is:

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

subpartition_definition is:

SUBPARTITION subpartition_name
     VALUES IN ( value_list)

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.

value_list

The values of 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-list partitioned table:

CREATE TABLE sales_key_list
(
  dept_no     varchar(20),
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY KEY(part_no)
     	SUBPARTITION BY LIST(part_no)
(
PARTITION dp0 (
     		SUBPARTITION p0 VALUES in (1, 2),
  			SUBPARTITION p1 VALUES in (3, 4),
  			SUBPARTITION p2 VALUES in (5, 6)
  ),
  PARTITION dp1
  (
   			SUBPARTITION p3 VALUES in (1, 2),
  			SUBPARTITION p4 VALUES in (3, 4),
  			SUBPARTITION p5 VALUES in (5, 6)
  ),
  PARTITION dp2
  (
    		SUBPARTITION p6 VALUES in (1, 2),
  			SUBPARTITION p7 VALUES in (3, 4),
  			SUBPARTITION p8 VALUES in (5, 6)
  )
);