Creating Partitioned Tables

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.

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 [, ... ];