Create a subpartitioned table group|V3.2.4|OceanBase Database| docs|Distributed Database

Create a subpartitioned table group

Last Updated：2023-10-24 09:23:03 Updated

This topic describes how to create a subpartitioned table group in OceanBase Database in Oracle mode.

Partitioning types

In OceanBase Database in Oracle mode, you can choose whether to use a template when you create a subpartitioned table group.

Partitioning type	Create a subpartitioned table group by using a template	Create a subpartitioned table group without using a template
RANGE-RANGE, RANGE-LIST, and RANGE-HASH	Supported	Supported
LIST-RANGE, LIST-LIST, and LIST-HASH	Supported	Supported
HASH-RANGE, HASH-LIST, and HASH-HASH	Not supported	Not supported

Considerations

You cannot separately manage partitions of tables in a table group. Instead, you must manage the partitions by using the table group in a unified manner.

SQL syntaxes to create a subpartitioned table group

SQL syntax to create a subpartitioned table group by using a template

CREATE TABLEGROUP tablegroup_name [opt_tablegroup_option_list] opt_tg_partition_option;

opt_tablegroup_option_list:
  tablegroup_option [tablegroup_option]

tablegroup_option:
    LOCALITY [=] locality_name
  | PRIMARY_ZONE [=] primary_zone_name

opt_tg_partition_option:
PARTITION BY
   RANGE [COLUMNS] column_num tg_subpartition_option {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
  | LIST [COLUMNS] column_num tg_subpartition_option {PARTITION partition_name VALUES  list_partition_expr, ...}

tg_subpartition_option:
SUBPARTITION BY
    RANGE [COLUMNS] column_num SUBPARTITION TEMPLATE {SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr, ...}
  | HASH [column_num] SUBPARTITIONS int_num
  | LIST [COLUMNS] column_num SUBPARTITION TEMPLATE {SUBPARTITION subpartition_name VALUES  list_partition_expr, ...}

SQL syntax to create a subpartitioned table group without using a template

CREATE TABLEGROUP  tablegroupname [opt_tablegroup_option_list] opt_tg_partition_option;

opt_tablegroup_option_list:
  tablegroup_option [tablegroup_option]

tablegroup_option:
    LOCALITY [=] locality_name
  | PRIMARY_ZONE [=] primary_zone_name

opt_tg_partition_option:
 PARTITION BY
    RANGE [COLUMNS] column_num tg_subpartition_option
     { PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option_list)
       [, PARTITION partition_name VALUES LESS THAN range_partition_expr (subpartition_option_list)]...
      }
  | LIST [COLUMNS] column_num tg_subpartition_option
     { PARTITION partition_name VALUES list_partition_expr (subpartition_option_list)
       [, PARTITION partition_name VALUES list_partition_expr (subpartition_option_list)]...
      }

tg_subpartition_option:
 SUBPARTITION BY
    RANGE [COLUMNS] column_num
  | LIST [COLUMNS] column_num
  | HASH [column_num] SUBPARTITIONS int_num

subpartition_option_list:
  range_subpartition_option | list_subpartition_option | hash_subpartition_option

range_subpartition_option:
  SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
    [,SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr]...

list_subpartition_option:
  SUBPARTITION subpartition_name VALUES list_partition_expr
    [, SUBPARTITION subpartition_name VALUES list_partition_expr]...

hash_subpartition_option:
  SUBPARTITION subpartition_name
    [, SUBPARTITION subpartition_name]...

Syntax description

opt_tablegroup_option_list: the partitioning method of the table group. The locality (the replica type, quantity, and locations) and primary zone (the leader location and priority) information of the table group must be consistent with that of the tables in the table group.
opt_tg_partition_option: the partitioning rules of the table group. The partitioning rules of the table group must be consistent with those of the tables in the table group. The partitioning type and number of partitions are specified by the rules.

The table group has no column definitions. Therefore, you do not need to specify columns. You only need to specify the column quantity for the column_num parameter.

Examples

Create a subpartitioned table group by using a template

Create a RANGE-RANGE-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rr
       PARTITION BY RANGE 1
       SUBPARTITION BY RANGE 1
       SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN (2019),
          SUBPARTITION mp1 VALUES LESS THAN (2020),
          SUBPARTITION mp2 VALUES LESS THAN (2021)
         )
         (PARTITION p0 VALUES LESS THAN (100),
          PARTITION p1 VALUES LESS THAN (200)
         );
Query OK, 0 rows affected

Create a RANGE-LIST-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rl
       PARTITION BY RANGE 1
       SUBPARTITION BY LIST 1
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES ('01'),
         SUBPARTITION mp1 VALUES ('02'),
         SUBPARTITION mp2 VALUES ('03')
        )
        (PARTITION p0 VALUES LESS THAN (100),
         PARTITION p1 VALUES LESS THAN (200)
        );
Query OK, 0 rows affected

Create a RANGE-HASH-subpartitioned table group by using a template.

 obclient> CREATE TABLEGROUP tg2_m_rh
        PARTITION BY RANGE 1
        SUBPARTITION BY HASH 1 SUBPARTITIONS 5
         (PARTITION p0 VALUES LESS THAN (100),
          PARTITION p1 VALUES LESS THAN (200)
         );
Query OK, 0 rows affected

Create a LIST-RANGE-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_lr  
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY RANGE COLUMNS 1
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES LESS THAN (100),
         SUBPARTITION mp1 VALUES LESS THAN (200),
         SUBPARTITION mp2 VALUES LESS THAN (300)
        )
        (PARTITION p0 VALUES ('A'),
         PARTITION p1 VALUES ('B')
        );
Query OK, 0 rows affected

Create a LIST-LIST-subpartitioned table group by using a template.

obclient> CREATE TABLEgroup tg2_m_ll  
       PARTITION BY LIST 1
       SUBPARTITION BY LIST 1
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES ('A'),
         SUBPARTITION mp1 VALUES ('B'),
         SUBPARTITION mp2 VALUES ('C')
        )
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02')
        );
Query OK, 0 rows affected

Create a LIST-HASH-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_lh
       PARTITION BY LIST 1
       SUBPARTITION BY HASH 1 SUBPARTITIONS 5
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02')
        );
Query OK, 0 rows affected

Create a subpartitioned table group without using a template

Create a RANGE-RANGE-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg2_f_rr
       PARTITION BY RANGE  1
       SUBPARTITION BY RANGE  1
        (PARTITION p0 VALUES LESS THAN (100)
          (SUBPARTITION sp0 VALUES LESS THAN (1000),
           SUBPARTITION sp1 VALUES LESS THAN (2000)
           ),
         PARTITION p1 VALUES LESS THAN (200)
          (SUBPARTITION sp2 VALUES LESS THAN (1000),
           SUBPARTITION sp3 VALUES LESS THAN (2000),
           SUBPARTITION sp4 VALUES LESS THAN (3000)
          ),
         PARTITION p2 VALUES LESS THAN(300)
          (SUBPARTITION sp5 VALUES LESS THAN (1000),
           SUBPARTITION sp6 VALUES LESS THAN (2000)
          )
        );
Query OK, 0 rows affected

Create a RANGE-LIST-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg2_f_rl
       PARTITION BY RANGE 1
       SUBPARTITION BY LIST 1
        (PARTITION p0 VALUES LESS THAN (100)
          (SUBPARTITION sp0 VALUES ('01'),
           SUBPARTITION sp1 VALUES ('02')
          ),
         PARTITION p1 VALUES LESS THAN (200)
          (SUBPARTITION sp2 VALUES ('01'),
           SUBPARTITION sp3 VALUES ('02'),
           SUBPARTITION sp4 VALUES ('03')
          )
        );
Query OK, 0 rows affected

Create a RANGE-HASH-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg2_f_rh
       PARTITION BY RANGE 1
       SUBPARTITION BY HASH 1
        (PARTITION p0 VALUES LESS THAN (100)
          (SUBPARTITION sp0,
           SUBPARTITION sp1
          ),
         PARTITION p1 VALUES LESS THAN (200)
          (SUBPARTITION sp2,
           SUBPARTITION sp3,
           SUBPARTITION sp4
          )
        );
Query OK, 0 rows affected

Create a LIST-HASH-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg2_f_lh
       PARTITION BY LIST  1
       SUBPARTITION BY HASH 1
        (PARTITION p0 VALUES ('A', 'B')
          (SUBPARTITION sp0,
           SUBPARTITION sp1
          ),
         PARTITION p1 VALUES ('C','D','E')
          (SUBPARTITION sp2,
           SUBPARTITION sp3
          )
        );
Query OK, 0 rows affected

Create a LIST-LIST-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg2_f_ll
       PARTITION BY LIST  1
       SUBPARTITION BY LIST  1
        (PARTITION p0 VALUES ('A')
          (SUBPARTITION sp0 VALUES ('01','03'),
           SUBPARTITION sp1 VALUES ('04','06'),
           SUBPARTITION sp2 VALUES ('07','09')
          ),
         PARTITION p0 VALUES ('B')
          (SUBPARTITION sp0 VALUES ('01','03'),
           SUBPARTITION sp1 VALUES ('04','06'),
           SUBPARTITION sp2 VALUES ('07','09')
          )
        );
Query OK, 0 rows affected

Create a LIST-RANGE-subpartitioned table group without using a template.

obclient> CREATE TABLEGROUP tg2_f_lr
       PARTITION BY LIST  1
       SUBPARTITION BY HASH 1
        (PARTITION p0 VALUES ('OR', 'WA')
          (SUBPARTITION sp0,
           SUBPARTITION sp1
          ),
         PARTITION p1 VALUES ('AZ', 'UT', 'NM')
          (SUBPARTITION sp2,
           SUBPARTITION sp3
           )
         );
Query OK, 0 rows affected