Updated on 2025-05-29 GMT+08:00

Creating Partitioned Tables

Creating Partitioned Tables

SQL has powerful and flexible functions, and its syntax tree is complex. This is also true for partitioned tables. Creating a partitioned table can be regarded as adding table partition attributes to a non-partitioned table. Therefore, the syntax of partitioned tables can be regarded as an extension of the CREATE TABLE statement of non-partitioned tables. Specifically, the PARTITION BY clause is added, and the following three core elements related to partitioning are specified:

  • partType: describes the partitioning policy used by a partitioned table. The partitioning policy can be range partitioning, list partitioning, or hash partitioning.
  • partKey: determines the partition key of a partitioned table. Currently, range partitioning and list partitioning support multiple columns (no more than 16 columns) as the partition key, while hash partitioning supports only single-column partitioning.
  • partExpr: describes the partitioning mode of a partitioned table, that is, the mapping between key values and partitions.
The three important elements are reflected in the PARTITION BY clause of the table creation statement. The specific format is PARTITION BY partType (partKey) (partExpr[,partExpr]…). The basic syntax framework is as follows:
CREATE TABLE [ IF NOT EXISTS ] partition_table_name
(
    [ /* Inherited from the CREATE TABLE statement of an ordinary table */
    { column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
    | table_constraint
    | LIKE source_table [ like_option [...] ] }[, ... ]
    ]
)
[ WITH ( {storage_parameter = value} [, ... ] ) ]
[ COMPRESS | NOCOMPRESS ]
[ TABLESPACE tablespace_name ]
/* Range partitioning */
PARTITION BY RANGE (partKey) (
    { partition_start_end_item [, ... ] | partition_less_then_item [, ... ] }
)
/* List partitioning */
PARTITION BY LIST (partKey)
(
    PARTITION partition_name VALUES (list_values_clause) [ TABLESPACE tablespace_name [, ... ] ]
...
)
/* Hash partitioning */
PARTITION BY HASH (partKey) (
    PARTITION partition_name [ TABLESPACE tablespace_name [, ... ] ]
...
)
/* Enable or disable row migration for a partitioned table. */
[ { ENABLE | DISABLE } ROW MOVEMENT ];
The usage of partitioned tables has the following limitations:
  • Range partitioning and list partitioning support a maximum of 16 partition keys, while hash partitioning supports only one partition key.
  • The partition key value cannot be null except for hash partitioning. Otherwise, the DML statement reports an error. However, null values can be inserted when the MAXVALUE partition is defined for a range partitioned table or the DEFAULT partition is defined for a list partitioned table.
  • The maximum number of partitions is 1,048,575, which can meet the requirements of most service scenarios. As the number of partitions increases, the number of files in the system increases accordingly, affecting system performance. Therefore, it is not recommended that a single table contain more than 200 partitions.

Modifying Partition Attributes

You can run the ALTER TABLE command similar to that of a non-partitioned table to modify attributes related to partitioned tables and partitions. Common statements for modifying partition attributes are as follows:
  • ADD PARTITION
  • DROP PARTITION
  • TRUNCATE PARTITION
  • SPLIT PARTITION
  • MERGE PARTITION
  • MOVE PARTITION
  • EXCHANGE PARTITION
  • RENAME PARTITION
The preceding partition attribute change statements are extensions of the ALTER TABLE statement for ordinary tables. The usage of the statements in partitioned tables is similar to that in ordinary tables. The basic syntax framework for changing partition attributes is as follows:
/* Basic ALTER TABLE syntax */
ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
action [, ... ];

For details about how to use the ALTER TABLE statement, see Partitioned Table O&M Management and "SQL Reference > SQL Syntax > ALTER TABLE PARTITION" in Developer Guide.