List Partitioning

List partitioning allows you to specify a list of discrete values for the partition key in the description of each partition to control how data rows are mapped to partitions. The advantage of this partitioning method is that it can partition data in the form of enumerated partition values, which is especially suitable for grouping and organizing unordered and unrelated data sets.

When processing partition key values, if some values are not defined in the list, you can use the DEFAULT partition to store these data. In this way, no error is generated for rows that cannot be mapped to any other partition. Example:

gaussdb=# CREATE TABLE bmsql_order_line (
    ol_w_id          INTEGER   NOT NULL,
    ol_d_id          INTEGER   NOT NULL,
    ol_o_id          INTEGER   NOT NULL,
    ol_number        INTEGER   NOT NULL,
    ol_i_id          INTEGER   NOT NULL,
    ol_delivery_d    TIMESTAMP,
    ol_amount        DECIMAL(6,2),
    ol_supply_w_id   INTEGER,
    ol_quantity      INTEGER,
    ol_dist_info     CHAR(24)
)
PARTITION BY LIST(ol_d_id)
(
    PARTITION p0 VALUES (1,4,7),
    PARTITION p1 VALUES (2,5,8),
    PARTITION p2 VALUES (3,6,9),
    PARTITION p3 VALUES (DEFAULT)
);

-- Drop the table.
gaussdb=# DROP TABLE bmsql_order_line;

The preceding example is similar to the hash partitioning example provided earlier. The table is partitioned based on the ol_d_id column. However, in list partitioning, the possible value range of ol_d_id is directly limited. Data that is not in the list enters the p3 partition (DEFAULT partition).

Compared with hash partitioning, list partitioning has stronger controllability over partition keys and can accurately store target data in the expected partition. However, if there are a large number of values in the list, the partition definition becomes cumbersome. In this case, hash partitioning is recommended.

In general, list partitioning and hash partitioning are often used to group and organize unordered and unrelated data sets.

List partitioning has a clear limit on the number of partition keys. A partition key can contain a maximum of 16 columns. Moreover, the rules for processing null values in the enumerated value list during subpartitioning are different when the number of partition-key columns is different. When a single-column partition key is defined, no null values are allowed in the enumerated value list. When a multi-column partition key is defined, null values are allowed in the enumerated value list.