Automatic Partitioning of Level-2 Partitioned Tables

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.

• 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)
      )
  );

  • 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 with a single key is defined.

    -- 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);

  • 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_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;

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.