Creating Partitioned Tables

The SQL syntax tree is complex due to the powerful and flexible functions of the SQL language. So do partitioned tables. The creation of a partitioned table can be regarded as adding partition attributes to the original non-partitioned table. Therefore, the syntax API of a partitioned table can be regarded to extend the CREATE TABLE statement of a non-partitioned table with a PARTITION BY clause and specify the following three core elements related to the partition:

1. partType: describes the partitioning policy of a partitioned table. The options are RANGE, INTERVAL, LIST, and HASH.
2. partKey: describes the partition key of a partitioned table. Currently, range and list partitioning supports a partition key with up to 16 columns, while interval and hash partitioning supports a one-column partition key only.
3. partExpr: describes the specific partitioning type of a partitioned table, that is, the mapping between key values and partitions.

The three elements are reflected in the PARTITION BY clause of the CREATE TABLE statement, for example, PARTITION BY partType (partKey) (partExpr[,partExpr]...). The following is an example:

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. If the INTERVAL clause is declared, interval partitioning is used. */
PARTITION BY RANGE (partKey) [ INTERVAL ('interval_expr') [ STORE IN (tablespace_name [, ... ] ) ] ] (
    partition_start_end_item [, ... ]
    partition_less_then_item [, ... ]
)
/* In the list partitioning scenario, if AUTOMATIC is declared, automatic list partitioning is supported. */
PARTITION BY LIST (partKey) [ AUTOMATIC ]
(
    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 ];

Restrictions:

1. Range/List partitioning supports a partition key with up to 16 columns. Interval/Hash partitioning supports a one-column partition key only. All subpartitioning types support a one-column partition key only.
2. Interval partitioning supports only the numeric and date/time types. Interval partitions cannot be created in a level-2 partitioned table.
3. No MAXVALUE partition can be defined in an interval partitioned table. No DEFAULT partition can be defined in an automatically created list partitioned table.
4. The partition key value cannot be null except for hash partitioning. Otherwise, DML statements will report errors. The only exception is a MAXVALUE partition defined in a range partitioned table or a DEFAULT partition defined in a list partitioned table.
5. The maximum number of partitions is 1048575, which can meet the requirements of most service scenarios. If the number of partitions increases, the number of files in the system increases, which affects the system performance. It is recommended that the number of partitions for a single table be less than or equal to 200.

The level-2 partitioned table may be considered as an extension of the partitioned table. In the level-2 partitioned table, the partition is a logical table and does not actually store data, and the data is actually stored on the level-2 partition node. The subpartitioning solution is implemented by nesting two partitions. For details about the partitioning solution, see "CREATE TABLE PARTITION." Common subpartitioning solutions include range-range partitioning, range-list partitioning, range-hash partitioning, list-range partitioning, list-list partitioning, list-hash partitioning, hash-range partitioning, hash-list partitioning, and hash-hash partitioning. Currently, subpartitioning is only applicable to row-store tables. The following is an example of creating a level-2 partition:

CREATE TABLE [ IF NOT EXISTS ] subpartition_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 ]
/* For the definition of level-2 partitions, only the list partitioning policy supports the declaration of AUTOMATIC. */
PARTITION BY {RANGE | LIST | HASH} (partKey) [ AUTOMATIC ] SUBPARTITOIN BY {RANGE | LIST | HASH} (partKey) [ AUTOMATIC ]
(
    PARTITION partition_name partExpr…  /* Partition */
    (
        SUBPARTITION partition_name partExpr … /* Subpartition */
        SUBPARTITION partition_name partExpr … /* Subpartition */
    ),
    PARTITION partition_name partExpr…  /* Partition */
    (
        SUBPARTITION partition_name partExpr … /* Subpartition */
        SUBPARTITION partition_name partExpr … /* Subpartition */
    ),
    …
)
[ { ENABLE | DISABLE } ROW MOVEMENT ];

Restrictions:

1. A level-2 partitioned table can be created by using a combination of any two of the list, hash, and range partitioning methods.
2. A level-2 partitioned table supports the declaration of automatic list partitioning at any level.
3. If any second-level partition of a level-2 partitioned table declares automatic list partitioning, the definition of the second-level partition in the CREATE TABLE statement cannot be empty.
4. A level-2 partitioned table supports only a single partition key.
5. A level-2 partitioned table does not support interval partitions.
6. A level-2 partitioned table supports a maximum of 1048575 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:

1. ADD PARTITION
2. DROP PARTITION
3. TRUNCATE PARTITION
4. SPLIT PARTITION
5. MERGE PARTITION
6. MOVE PARTITION
7. EXCHANGE PARTITION
8. RENAME PARTITION

The preceding statements for modifying partition attributes are extended based on the ALTER TABLE statement of an ordinary table. Most of the statements are used in a similar way. The following is an example of the basic syntax framework for modifying partitioned table attributes:

/* Basic ALTER TABLE syntax */
ALTER TABLE [ IF EXISTS ] { table_name [*] | ONLY table_name | ONLY ( table_name )}
action [, ... ];