CREATE TABLEGROUP|V4.1.0| docs|Distributed Database

CREATE TABLEGROUP

Last Updated：2023-07-28 02:55:43 Updated

Purpose

You can use this statement to create a table group.

In the current version of OceanBase Database, you can use non-template-based subpartitions when you create an unbound table group. That is, you can add multiple non-template-based subpartitions to the table group.

Note

Only an administrator of a tenant can create a table group.

Syntax

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

opt_tg_partition_option:
PARTITION BY
 HASH column_num [tg_subpartition_option] PARTITIONS int_num
| 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 partition_name VALUES LESS THAN range_partition_expr, ...}
| HASH column_num [SUBPARTITIONS INTNUM]
| LIST [COLUMNS] column_num SUBPARTITION TEMPLATE {SUBPARTITION partition_name VALUES  list_partition_expr, ...}

Syntax for creating non-template-based subpartitions for a 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
 ...
opt_tg_partition_option:
 PARTITION BY
 | HASH column_num [tg_subpartition_option] PARTITIONS int_num
 | 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 {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
 | LIST [COLUMNS] column_num SUBPARTITION  {SUBPARTITION partition_name VALUES  list_partition_expr, ...}
 | HASH [SUBPARTITIONS int_num]

Parameters

Parameter	Description
tablegroup_name	The name of the table group, which is a string of a maximum of 64 letters, digits, and underscores (). It must start with a letter or underscore (), and cannot contain any database keywords.
opt_tablegroup_option_list	The partitioning method of the table group. The locality of the table group must be consistent with that of the tables in the group. You cannot modify a single item in a table. You must perform batch operations on the entire 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.
opt_tg_partition_option	The definition of partitioning rules of the table group. The same partitioning method as `CREATE TABLE` is used. A table group does not have specific column definitions. Therefore, you do not need to specify the column names for the `HASH`, `RANGE`, and `LIST` options. You only need to specify the number of columns (`COLUMN_NUM`). Consistent partitioning method: Tables in the same table group must share the same partitioning method such as HASH-RANGE partitioning. HASH-partitioned tables must have the same number of referenced columns and the same number of partitions. RANGE-partitioned tables must have the same number of referenced columns, the same number of partitions, and the same range definitions. Subpartitions in the table group must also meet the preceding requirements.

Examples

Create a table group named tblgroup1, and two tables respectively named tbl1 and tbl2 for which tblgroup1 is specified for the TABLEGROUP attribute.

obclient> CREATE TABLEGROUP tblgroup1;
Query OK, 0 rows affected

obclient> CREATE TABLE tbl1 (col1 int, col2 int ) tablegroup = tblgroup1;
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2 (col1 int, col2 int ) tablegroup = tblgroup1;
Query OK, 0 rows affected

Create a HASH-partitioned table group named tblgroup2, and two HASH-partitioned tables respectively named tbl1 and tbl2 that have the same number of partitions.

obclient> CREATE TABLEGROUP tblgroup2 LOCALITY = 'F,R{ALL_SERVER}@zone1' partition by hash partitions 10;
Query OK, 0 rows affected

obclient> CREATE TABLE tbl1(col1 int, col2 int) TABLEGROUP = tblgroup2 LOCALITY = 'F,R{ALL_SERVER}@zone1' partition by hash(col1) partitions 10;
Query OK, 0 rows affected

obclient> CREATE TABLE tbl2(col1 int, col2 int) TABLEGROUP = tblgroup2 LOCALITY = 'F,R{ALL_SERVER}@zone1' partition by hash(col2) partitions 10;
Query OK, 0 rows affected

Create a subpartitioned table group by using a template

Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table group named tblgroup3.

obclient> CREATE TABLEGROUP tblgroup3 PARTITION BY RANGE COLUMNS 1
SUBPARTITION BY RANGE  COLUMNS 1
SUBPARTITION TEMPLATE
(
 SUBPARTITION rp1 VALUES LESS THAN (2019),
 SUBPARTITION rp2 VALUES LESS THAN (2020),
 SUBPARTITION rp3 VALUES LESS THAN (2021)
)
(
 PARTITION p0 VALUES LESS THAN (100),
 PARTITION p1 VALUES LESS THAN (200),
 PARTITION p2 VALUES LESS THAN (300)
)
;
Query OK, 0 rows affected

Create a RANGE COLUMNS-LIST COLUMNS-subpartitioned table group named tblgroup4.

obclient> CREATE TABLEGROUP tblgroup4 PARTITION BY RANGE COLUMNS 1
SUBPARTITION BY LIST COLUMNS 1
SUBPARTITION TEMPLATE
(
 SUBPARTITION rp0 VALUES  ('00','01'),
 SUBPARTITION rp1 VALUES  ('02','03'),
 SUBPARTITION rp2 VALUES  ('04','05')
)
(
 PARTITION p0 VALUES LESS THAN (2019),
 PARTITION p1 VALUES LESS THAN (2020),
 PARTITION p2 VALUES LESS THAN (2021)
);
Query OK, 0 rows affected

Create a subpartitioned table group without using a template

Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table group named tblgroup6.

obclient> CREATE TABLEGROUP tblgroup6 PARTITION BY RANGE COLUMNS 1
  SUBPARTITION BY RANGE COLUMNS 1
(
PARTITION p0 VALUES LESS THAN (100)
   (
   SUBPARTITION sp0 VALUES LESS THAN (2018),
   SUBPARTITION sp1 VALUES LESS THAN (2019),
   SUBPARTITION sp2 VALUES LESS THAN (2020),
   SUBPARTITION sp3 VALUES LESS THAN (2021)
   ),
PARTITION p1 VALUES LESS THAN (200)
  (
  SUBPARTITION sp4 VALUES LESS THAN (2018),
  SUBPARTITION sp5 VALUES LESS THAN (2019),
  SUBPARTITION sp6 VALUES LESS THAN (2020),
  SUBPARTITION sp7 VALUES LESS THAN (2021)
  )
);
Query OK, 0 rows affected

Create a RANGE COLUMNS-LIST COLUMNS-subpartitioned table group named tblgroup7.

obclient> CREATE TABLEGROUP tb1 PARTITION BY RANGE COLUMNS 1
 SUBPARTITION BY LIST COLUMNS 1
(
PARTITION p0 VALUES LESS THAN (2019)
  (SUBPARTITION p0sp0 VALUES ('00', '03'),
   SUBPARTITION p0sp1 VALUES ('04', '06'),
   SUBPARTITION p0sp2 VALUES ('07', '09')
  ),
PARTITION p1 VALUES LESS THAN ( 2020)
  (SUBPARTITION p1sp0 VALUES ('00', '03'),
   SUBPARTITION p1sp1 VALUES ('04', '06'),
   SUBPARTITION p1sp2 VALUES ('07', '09')
  ),
PARTITION p2 VALUES LESS THAN (2021)
  (SUBPARTITION p2sp0 VALUES ('00', '03'),
   SUBPARTITION p2sp1 VALUES ('04', '06'),
   SUBPARTITION p2sp2 VALUES ('07', '09'))
 );
Query OK, 0 rows affected

Create a RANGE COLUMNS-HASH-subpartitioned table group named tblgroup8.

obclient> CREATE TABLEGROUP tblgroup8  PARTITION BY RANGE COLUMNS 1
SUBPARTITION BY HASH SUBPARTITIONS 1
(
PARTITION p0 VALUES LESS THAN (2020)
  (
   SUBPARTITION p0sp1,
   SUBPARTITION p0sp2
   ),
PARTITION p1 VALUES LESS THAN (2021)
   (
    SUBPARTITION p1sp1,
    SUBPARTITION p1sp2,
    SUBPARTITION p1sp3
    )
);
Query OK, 0 rows affected