Create a partitioned table group

This topic describes how to create a partitioned table group in OceanBase Database in Oracle 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 Oracle mode allows you to create RANGE-, LIST-, or HASH-partitioned table groups.

Create a table group

You can create a table group only if you have tenant administrator privileges. At present, a table group supports only the RANGE, LIST, and HASH partitioning strategies.

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
  HASH [column_num] 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]...}

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. Partitioning rules of the table group must be consistent with those of the tables in the table group.

  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.

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

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