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 methods

OceanBase Database in Oracle mode allows you to create RANGE-, LIST-, or HASH-partitioned table groups, template-based subpartitioned table groups, and non-template-based subpartitioned table groups.

The following table describes the support for partition management operations on a subpartitioned table group.

Partitioning methods	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

Usage notes

Only users with the tenant administrator privileges can create table groups.
Before you create a partitioned table group, you must specify the partitioning strategy of the table group. The partitioning strategy of the table group must be consistent with that of the partitioned tables to be added to the table group.
You cannot separately manage partitions of tables in a table group. Instead, you must manage partitions by using the table group in a unified manner.

Create a partitioned table group

Syntax

CREATE TABLEGROUP tablegroup_name 
                  [tablegroup_option_list] tg_partition_option;

tablegroup_option_list:
  tablegroup_option [tablegroup_option]

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

tg_partition_option:
PARTITION BY 
  HASH PARTITIONS int_num
  | RANGE [COLUMNS] column_num {PARTITION partition_name VALUES LESS THAN range_partition_expr [, PARTITION partition_name VALUES LESS THAN range_partition_expr]...}
  | LIST [COLUMNS] column_num {PARTITION partition_name VALUES list_partition_expr [, PARTITION partition_name VALUES list_partition_expr]...}

Parameters

Parameter Description

tablegroup_option_list

Parameter	Description
tablegroup_option_list	The partitioning method, locality, and primary zone of the table group must be the same as those of the tables in the table group. Consistent locality means that the replica type, quantity, and location must be consistent. Consistent primary zone means that the location and priority of the leaders must be consistent.
tg_partition_option	The partitioning rules of a table group. Partitioning rules of the table group must be consistent with those of the tables in the table group. A table group does not have specific column definitions. Therefore, you do not need to specify the column names. You only need to specify the number of columns for the `column_num` parameter. Requirements on partitioning rules mainly include the following points: A table group and tables in the group must use the same partitioning rule. For example, both the table group and tables in it use the RANGE partitioning rule. In the case of HASH partitioning, the table group and tables in the group must have the same number of partitions. In the case of RANGE partitioning, the table group and tables in the group must have the same number of partitions and the same range definitions.

The partitioning method, locality, and primary zone of the table group must be the same as those of the tables in the table group.

Consistent locality means that the replica type, quantity, and location must be consistent.
Consistent primary zone means that the location and priority of the leaders must be consistent.

tg_partition_option

The partitioning rules of a table group. Partitioning rules of the table group must be consistent with those of the tables in the table group. A table group does not have specific column definitions. Therefore, you do not need to specify the column names. You only need to specify the number of columns for the column_num parameter. Requirements on partitioning rules mainly include the following points:

A table group and tables in the group must use the same partitioning rule. For example, both the table group and tables in it use the RANGE partitioning rule.
In the case of HASH partitioning, the table group and tables in the group must have the same number of partitions.
In the case of RANGE partitioning, the table group and tables in the group must have the same number of partitions and the same range definitions.

Examples

Create a RANGE-partitioned table group named tg1_r.

obclient> CREATE TABLEGROUP tg1_r PARTITION BY RANGE 1
          (PARTITION p0 VALUES LESS THAN(2019), 
          PARTITION p1 VALUES LESS THAN(2020),
          PARTITION p1 VALUES LESS THAN(2021)
          );
Query OK, 0 rows affected

Create a LIST-partitioned table group named tg1_l.

obclient> CREATE TABLEGROUP tg1_l PARTITION BY LIST 1  
          (PARTITION p0 VALUES ('A'), 
          PARTITION p1 VALUES ('B'),
          PARTITION p2 VALUES ('C')
          );
Query OK, 0 rows affected

Create a HASH-partitioned table group named tg1_h.

obclient> CREATE TABLEGROUP tg1_h PARTITION BY HASH PARTITIONS 2;
Query OK, 0 rows affected

Create a subpartitioned table group by using a template

Syntax

CREATE TABLEGROUP tablegroup_name [tablegroup_option_list] tg_partition_option;

tablegroup_option_list:
  tablegroup_option [tablegroup_option]

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

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 SUBPARTITIONS int_num
  | LIST [COLUMNS] column_num SUBPARTITION TEMPLATE {SUBPARTITION subpartition_name VALUES list_partition_expr, ...}

Parameters

Parameter Description

tablegroup_option_list The partitioning method of a 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.

tg_partition_option The partitioning rules of a table group. The partitioning rules of the table group must be consistent with those of the tables in the table group. The partitioning method and number of partitions are specified by the rules. A table group does not have specific column definitions. Therefore, you do not need to specify the column names. You only need to specify the number of columns for the column_num parameter.

Parameter	Description
tablegroup_option_list	The partitioning method of a 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.
tg_partition_option	The partitioning rules of a table group. The partitioning rules of the table group must be consistent with those of the tables in the table group. The partitioning method and number of partitions are specified by the rules. A table group does not have specific column definitions. Therefore, you do not need to specify the column names. You only need to specify the number of columns for the `column_num` parameter.

Examples

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 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 1
            SUBPARTITION BY RANGE 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 SUBPARTITIONS 5
        (PARTITION p0 VALUES ('01'),
         PARTITION p1 VALUES ('02')
        );
Query OK, 0 rows affected

Create a subpartitioned table group without using a template

Syntax

CREATE TABLEGROUP  tablegroupname [tablegroup_option_list] tg_partition_option;

tablegroup_option_list:
  tablegroup_option [tablegroup_option]

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

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 SUBPARTITION 
  | LIST[ COLUMNS] column_num SUBPARTITION 
  | HASH [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]...

Parameters

Parameter Description

Parameter	Description
tablegroup_option_list	The partitioning method of a 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.
tg_partition_option	The partitioning rules of a table group. The partitioning rules of the table group must be consistent with those of the tables in the table group. The partitioning method and number of partitions are specified by the rules. A table group does not have specific column definitions. Therefore, you do not need to specify the column names. You only need to specify the number of columns for the `column_num` parameter.

Examples

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
        (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
        (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 p1 VALUES ('B')
             (SUBPARTITION sp3 VALUES ('01','03'),
             SUBPARTITION sp4 VALUES ('04','06'),
             SUBPARTITION sp5 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 RANGE 1
           (PARTITION p0 VALUES ('OR', 'WA')
           (SUBPARTITION sp0 VALUES LESS THAN (1000),
           SUBPARTITION sp1 VALUES LESS THAN (2000)
           ),
           PARTITION p1 VALUES ('AZ', 'UT', 'NM')
          (SUBPARTITION sp2 VALUES LESS THAN (1000),
           SUBPARTITION sp3 VALUES LESS THAN (2000)
           )
         );
Query OK, 0 rows affected