Automatic Partitioning of Level-2 Partitioned Tables

Updated on 2024-06-07 GMT+08:00

View PDF

When creating a level-2 partitioned table, you can specify the AUTOMATIC keyword in the list partition definition to support automatic level-1 and level-2 partitioning of the level-2 partitioned table. Automatic extension of level-2 list partitioned tables supports only single-column partition keys.

When creating a level-2 partitioned table, specify AUTOMATIC in the level-1 partition definition to support automatic level-1 partitioning.

gaussdb=# CREATE TABLE autolist_range (c1 int, c2 int)
PARTITION BY LIST (c1) AUTOMATIC SUBPARTITION BY RANGE (c2)
(
    PARTITION p1 VALUES (1, 2, 3) (
        SUBPARTITION sp11 VALUES LESS THAN (5),
        SUBPARTITION sp12 VALUES LESS THAN (10)
    ),
    PARTITION p2 VALUES (4, 5, 6) (
        SUBPARTITION sp21 VALUES LESS THAN (5),
        SUBPARTITION sp22 VALUES LESS THAN (10)
    )
);

If the inserted data cannot match any existing level-1 partition, a level-1 partition is automatically created. The range of the new level-1 partition is defined as a single key (new partition key corresponding to the new data), and a level-2 partition is defined under the new level-1 partition.

-- Insert data 9 into the level-1 partition key. Because the key values of the existing level-1 partitions p1 and p2 do not contain 9, a level-1 partition sys_p1 is automatically created and the partition is defined as VALUES (9).
gaussdb=# INSERT INTO autolist_range VALUES (9, 0);

This function is equivalent to the following commands:

gaussdb=# ALTER TABLE autolist_range ADD PARTITION sys_p1 VALUES (9); 
gaussdb=# INSERT INTO autolist_range VALUES (9, 0);
gaussdb=# DROP TABLE autolist_range;

When creating a level-2 partitioned table, specify AUTOMATIC in the level-2 partition definition to support automatic level-2 partitioning.

gaussdb=# CREATE TABLE range_autolist (c1 int, c2 int)
PARTITION BY RANGE (c1) SUBPARTITION BY LIST (c2) AUTOMATIC
(
    PARTITION p1 VALUES LESS THAN (5) (
        SUBPARTITION sp11 VALUES (1, 2, 3),
        SUBPARTITION sp12 VALUES (4, 5, 6)
    ),
    PARTITION p2 VALUES LESS THAN (10) (
        SUBPARTITION sp21 VALUES (1, 2, 3),
        SUBPARTITION sp22 VALUES (4, 5, 6)
    )
);

If the inserted data cannot match any existing level-2 partition, a level-2 partition is automatically created under the corresponding level-1 partition. The range of the new level-2 partition is defined as a single key (new partition key value corresponding to the new data).

-- Insert data 0 into the level-2 partition key. Because the key value of the existing level-2 partition does not contain 0, a level-2 partition sys_sp1 is automatically created. The partition is defined as VALUES (0).
gaussdb=# INSERT INTO range_autolist VALUES (4, 0);

This function is equivalent to the following commands:

gaussdb=# ALTER TABLE range_autolist MODIFY PARTITION p1 ADD SUBPARTITION sys_sp1 VALUES (0); 
gaussdb=# INSERT INTO range_autolist VALUES (4, 0);

-- Cleanup example
gaussdb=# DROP TABLE range_autolist;

When creating a level-2 partitioned table, specify AUTOMATIC in both level-1 and level-2 partition definitions to support automatic level-1 and level-2 partitioning.

gaussdb=# CREATE TABLE autolist_autolist (c1 int, c2 int)
PARTITION BY LIST (c1) AUTOMATIC SUBPARTITION BY LIST (c2) AUTOMATIC
(
    PARTITION p1 VALUES (1, 2, 3) (
        SUBPARTITION sp11 VALUES (1, 2, 3),
        SUBPARTITION sp12 VALUES (4, 5, 6)
    ),
    PARTITION p2 VALUES (4, 5, 6) (
        SUBPARTITION sp21 VALUES (1, 2, 3),
        SUBPARTITION sp22 VALUES (4, 5, 6)
    )
);

-- Insert data 9 into the level-1 partition key. Because the key values of the existing level-1 partitions p1 and p2 do not contain 9, a level-1 partition sys_p1 is automatically created and the partition is defined as VALUES (9). At the same time, data 0 is inserted into the level-2 partition key. Because the key value of the existing level-2 partition does not contain 0, a level-2 partition sys_sp1 is defined in the new level-1 partition sys_p1. The partition is defined as VALUES (0).
gaussdb=# INSERT INTO autolist_autolist VALUES (9, 0);

This function is equivalent to the following commands:

gaussdb=# ALTER TABLE autolist_autolist ADD PARTITION sys_p1 VALUES (9) (SUBPARTITION sys_sp1 VALUES (0)); 
gaussdb=# INSERT INTO autolist_autolist VALUES (9, 0);

-- Insert data 0 into the level-2 partition key. Because the key value of the existing level-2 partition does not contain 0, a level-2 partition sys_sp2 is automatically created and the partition is defined as VALUES (0).
gaussdb=# INSERT INTO autolist_autolist VALUES (4, 0);

This function is equivalent to the following commands:

gaussdb=# ALTER TABLE autolist_autolist MODIFY PARTITION p2 ADD SUBPARTITION sys_sp2 VALUES (0); 
gaussdb=# INSERT INTO autolist_autolist VALUES (4, 0);

-- Cleanup example
gaussdb=# DROP TABLE autolist_autolist;

NOTE:

The position specifying the AUTOMATIC keyword affects automatic list partitioning of level-2 partitioned tables as follows:

If the AUTOMATIC keyword is specified after a level-1 partition, only automatic level-1 partitioning is supported. No level-2 partition can be automatically created, and no DEFAULT level-1 partition key is allowed.
If the AUTOMATIC keyword is specified after a level-2 partition, only automatic level-2 partitioning is supported. No level-1 partition can be automatically created, and no DEFAULT level-2 partition key is allowed.

If the AUTOMATIC keyword is specified after both the level-1 and level-2 partitions, automatic level-1 and level-2 partitioning is supported. No DEFAULT level-1 and level-2 partition keys are allowed.

Parent topic: Automatic List Partitioning

Previous topic: Automatic Partitioning of Level-1 Partitioned Tables

Next topic: Enabling/Disabling Automatic Partitioning