Create a partitioned table group

This topic describes how to create a partitioned table group in OceanBase Database in MySQL mode.

Considerations

• 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.

Partitioning types

OceanBase Database in MySQL mode supports the following partitioning types for a partitioned table group:

• RANGE and RANGE COLUMNS

• LIST and LIST COLUMNS

• HASH and KEY

Create a table group

Only users with the tenant administrator privileges can create table groups.

SQL syntax to create a partitioned table group:

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
 KEY column_num  PARTITIONS INTNUM
| HASH PARTITIONS INTNUM
| RANGE {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| RANGE COLUMNS column_num {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| LIST {PARTITION partition_name VALUES IN list_partition_expr, ...}
| LIST COLUMNS column_num {PARTITION partition_name VALUES IN list_partition_expr, ...}
  KEY column_num PARTITIONS INTNUM

Syntax description:

• opt_tablegroup_option_list: the partitioning method of the table group. The locality and primary zone information of the table group must be consistent with that of the tables in the table group. The locality specifies the replica type, quantity, and location. The primary zone specifies the leader location and priority.

• 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.

  In RANGE COLUMNS, LIST COLUMNS, and KEY partitioning, you must specify the number of columns by setting the COLUMN_NUM parameter.

  The partitioning rules mainly include the following aspects:

  • 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.

  • If the RANGE COLUMNS partitioning strategy is used, the table group and tables in the group must have the same number of referenced columns and partitions and the same range definitions.

Examples

• Create a RANGE-partitioned table group.

  obclient> CREATE TABLEGROUP tblgroup1 PARTITION BY RANGE
              ( PARTITION p0 VALUES LESS THAN(100),
                PARTITION p1 VALUES LESS THAN(200)
               );
  Query OK, 0 rows affected
  

• Create a RANGE COLUMNS-partitioned table group.

  obclient> CREATE TABLEGROUP tblgroup1 PARTITION BY RANGE COLUMNS 1
              ( PARTITION p0 VALUES LESS THAN(100),
                PARTITION p1 VALUES LESS THAN(200)
               );
  Query OK, 0 rows affected
  

• Create a LIST-partitioned table group.

  obclient> CREATE TABLEGROUP tblgroup1 PARTITION BY LIST
              ( PARTITION p_northwest VALUES IN(100),
                PARTITION p_southwest VALUES IN(200)
              );
  Query OK, 0 rows affected
  

• Create a LIST COLUMNS-partitioned table group.

  obclient> CREATE TABLEGROUP tblgroup1 PARTITION BY LIST COLUMNS 1
              ( PARTITION p_northwest VALUES IN('OR', 'WA'),
                PARTITION p_southwest VALUES IN('AZ', 'UT', 'NM')
              );
  Query OK, 0 rows affected
  

• Create a HASH-partitioned table group.

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

• Create a KEY-partitioned table group.

  obclient> CREATE TABLEGROUP tblgroup1 PARTITION BY KEY 1 PARTITIONS 10;
  Query OK, 0 rows affected