CREATE TABLEGROUP|V4.1.0| docs|Distributed Database

CREATE TABLEGROUP

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

Purpose

You can use this statement to create a table group.

A table group is used to create a group of tables with the same distribution characteristics. These tables can be joined locally to avoid cross-node data requests.

When you create a table group, you must first plan the use of the table group. For non-partitioned tables, you do not need to partition the table group. For partitioned tables, you need to specify the partitioning strategy for the table group. The partitioning strategy must be the same as that of the partitioned tables.

In the current version of OceanBase Database, you can use non-template-based subpartitions when creating 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.

To add a table to a table group, make sure that the table has the same partitioning strategy as that of the table group.

In addition, the BINDING attribute of the table group must be set to FALSE. You can execute the following statement to query the attribute of the table group.

You can modify the table group attribute of a table to add it to a table group, or directly add it to a table group.

Modify the table group attribute of a table:

obclient> ALTER TABLE $table tablegroup=$tbgroup;
Query OK, 0 rows affected

Add a table to a table group:

obclient> ALTER TABLEGROUP $tbgroup ADD $table;
Query OK, 0 rows affected

Syntax

Create a template-based subpartitioned table group:

CREATE TABLEGROUP [IF NOT EXISTS] 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
      KEY COLUMN_NUM [tg_subpartition_option] PARTITIONS INTNUM
    | HASH [tg_subpartition_option] PARTITIONS INTNUM
    | RANGE [tg_subpartition_option]
          {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
    | RANGE COLUMNS COLUMN_NUM [tg_subpartition_option]
          {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
    | LIST [tg_subpartition_option]
          {PARTITION partition_name VALUES in list_partition_expr, ...}
    | LIST COLUMNS COLUMN_NUM [tg_subpartition_option]
          {PARTITION partition_name VALUES in list_partition_expr, ...}

tg_subpartition_option:
SUBPARTITION BY
      RANGE SUBPARTITION TEMPLATE
          {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
    | RANGE COLUMNS COLUMN_NUM SUBPARTITION TEMPLATE
          {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
    | HASH [SUBPARTITIONS INTNUM]
    | KEY COLUMN_NUM [SUBPARTITIONS INTNUM]
    | LIST SUBPARTITION TEMPLATE
          {SUBPARTITION partition_name VALUES in list_partition_expr, ...}
    | LIST COLUMNS COLUMN_NUM SUBPARTITION TEMPLATE
          {SUBPARTITION partition_name VALUES in list_partition_expr, ...}

Create a non-template-based subpartitioned table group:

CREATE TABLEGROUP <tablegroupname> [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 INTNUM
    | 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 in list_partition_expr, ...}

tg_subpartition_option:
    SUBPARTITION BY
    RANGE COLUMN_NUM SUBPARTITION TEMPLATE {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
    | HASH COLUMN_NUM [SUBPARTITIONS INTNUM]
    | LIST COLUMN_NUM SUBPARTITION TEMPLATE {SUBPARTITI_name VALUES ON partitionin   list_partition_expr, ...}

Parameters

Parameter	Description
tablegroup_name	The table group name. It can contain up to 64 characters in length and contain uppercase and lowercase letters, digits, and underscores (`_`). It must start with a letter or an underscore (`_`), and cannot contain keywords of OceanBase Database. If the name of the created table group already exists and `IF NOT EXISTS` is not specified, an error is returned.
opt_tablegroup_option_list	The partitioning mode and locality of the table group must be the same as those of the tables in the table group. Identical locality: The replica type, quantity, and location must be consistent. Consistent partitioning method: Tables in the same table group must share the same partitioning method such as `HASH`-`RANGE` partitioning. `KEY`-partitioned tables must have the same number of referenced columns and the same number of partitions. `HASH`-partitioned tables must have the same number of partitions. `RANGE COLUMNS`-partitioned tables must have the same number of partitions, the same number of referenced columns, and the same range definitions. `RANGE`-partitioned tables must have the same number of partitions and the same range definitions. Subpartitions in a table group must meet the preceding requirements. Notice You cannot modify a single item of a table. You must perform batch operations on the entire table group.
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 column names for the `KEY`, `RANGE COLUMNS`, and `LIST COLUMNS` partitioning methods. You only need to specify the number of columns for the `COLUMN_NUM` parameter.

Examples

Create a table group named myTableGroup1.

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

obclient> CREATE TABLE myt1 (c1 int, c2 int ) TABLEGROUP = myTableGroup1;
Query OK, 0 rows affected

obclient> CREATE TABLE myt2 (c1 int, c2 int ) TABLEGROUP = myTableGroup1;
Query OK, 0 rows affected

Create a HASH-partitioned table group named tgh, and create HASH-partitioned tables named ttgh and ttgh2 with the same number of partitions.

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

obclient> CREATE TABLE ttgh(c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 10;
Query OK, 0 rows affected

obclient> CREATE TABLE ttgh2(c1 INT, c2 INT) PARTITION BY HASH(c1) PARTITIONS 10;
Query OK, 0 rows affected

Create a subpartitioned table group without using a template

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

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

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

obclient> CREATE TABLEGROUP tg1_rh
    PARTITION BY RANGE COLUMNS 1
    SUBPARTITION BY HASH  (
        PARTITION p0 VALUES LESS THAN (1990) (
            SUBPARTITION s0,
            SUBPARTITION s1
        ),
        PARTITION p1 VALUES LESS THAN (2000) (
            SUBPARTITION s2,
            SUBPARTITION s3
        ),
        PARTITION p2 VALUES LESS THAN (2010) (
            SUBPARTITION s4,
            SUBPARTITION s5
        )
    );
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg1_rl
PARTITION BY RANGE COLUMNS 1
  SUBPARTITION BY LIST COLUMNS 2
(PARTITION p0 VALUES LESS THAN (100)
 ( SUBPARTITION sp0 VALUES IN (('US',1),('US',2))
, SUBPARTITION sp1 VALUES IN  (('CN',1),('CN',2))
),
 PARTITION p1 VALUES LESS THAN (200)
( SUBPARTITION sp3 VALUES IN (('US',1),('US',2))
, SUBPARTITION sp4 VALUES IN  (('CN',1),('CN',2))
)
);
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg1_lh
PARTITION BY LIST COLUMNS 1
SUBPARTITION BY HASH
(
 PARTITION p_northwest VALUES in ('OR', 'WA')
 (SUBPARTITION sp0, SUBPARTITION sp1)
 , PARTITION p_southwest VALUES in ('AZ', 'UT', 'NM')
  (SUBPARTITION sp2, SUBPARTITION sp3)
);
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg1_ll
PARTITION BY LIST COLUMNS 1
     SUBPARTITION BY LIST COLUMNS 1
(PARTITION p0 VALUES IN (1,2,3)
     ( SUBPARTITION sp0 VALUES IN (2020)
     , SUBPARTITION sp1 VALUES IN (2021)
     , SUBPARTITION sp2 VALUES IN (2022)
     , SUBPARTITION sp3 VALUES IN (2023)
     ) ,
 PARTITION p1 VALUES IN (4,5,6)
     ( SUBPARTITION sp4 VALUES IN (2020)
     , SUBPARTITION sp5 VALUES IN (2021)
     , SUBPARTITION sp6 VALUES IN (2022)
     , SUBPARTITION sp7 VALUES IN (2023)
     )
);
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg1_lr
PARTITION BY LIST COLUMNS 1
     SUBPARTITION BY RANGE COLUMNS 1
(PARTITION p0 VALUES IN (1,2,3)
( SUBPARTITION sp0 VALUES LESS THAN (2020)
     , SUBPARTITION sp1 VALUES LESS THAN (2021)
     , SUBPARTITION sp2 VALUES LESS THAN (2022)
     , SUBPARTITION sp3 VALUES LESS THAN (2023)
     ) ,
 PARTITION p1 VALUES IN (4,5,6)
( SUBPARTITION sp4 VALUES LESS THAN (2020)
     , SUBPARTITION sp5 VALUES LESS THAN (2021)
     , SUBPARTITION sp6 VALUES LESS THAN (2022)
     , SUBPARTITION sp7 VALUES LESS THAN (2023)
     )
);

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

obclient> CREATE TABLEGROUP tg2_f_rck
       PARTITION BY RANGE COLUMNS 1
       SUBPARTITION BY KEY 1
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0,
          SUBPARTITION sp1,
          SUBPARTITION sp2
         ),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3,
          SUBPARTITION sp4,
          SUBPARTITION sp5
         ),
        PARTITION p2 VALUES LESS THAN(300)
         (SUBPARTITION sp6,
          SUBPARTITION sp7,
          SUBPARTITION sp8
         )
       );
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg2_f_lck
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY KEY 1
       (PARTITION p0 VALUES IN('01','02')
         (SUBPARTITION sp0,
          SUBPARTITION sp1,
          SUBPARTITION sp2),
        PARTITION p1 VALUES IN('03','04')
         (SUBPARTITION sp3,
          SUBPARTITION sp4,
          SUBPARTITION sp5),
        PARTITION p2 VALUES IN('05','06')
         (SUBPARTITION sp6,
          SUBPARTITION sp7,
          SUBPARTITION sp8)
        );
Query OK, 0 rows affected

Create a subpartitioned table group by using a template

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

obclient> CREATE TABLEGROUP tg2_m_rcrc
        PARTITION BY RANGE COLUMNS 1
        SUBPARTITION BY RANGE COLUMNS 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),
         PARTITION p2 VALUES LESS THAN(300)
        );
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg2_m_rclc
       PARTITION BY RANGE COLUMNS 1
       SUBPARTITION BY LIST COLUMNS 1
       SUBPARTITION TEMPLATE
       (SUBPARTITION mp0 VALUES IN('A','B'),
        SUBPARTITION mp1 VALUES IN('C','D'),
        SUBPARTITION mp2 VALUES IN('E','F')
       )
       (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-HASH-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rch
       PARTITION BY RANGE COLUMNS 1
       SUBPARTITION BY HASH SUBPARTITIONS 5
       (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-KEY-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_rck
       PARTITION BY RANGE COLUMNS 1
       SUBPARTITION BY KEY 1 SUBPARTITIONS 3
       (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 LIST COLUMNS-RANGE COLUMNS-subpartitioned table group by using a template.

obclient> CREATE TABLEGROUP tg2_m_lcrc
       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 IN('A','B'),
        PARTITION p1 VALUES IN('C','D'),
        PARTITION p2 VALUES IN('E','F')
       );
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg2_m_lclc
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY LIST COLUMNS 1
       SUBPARTITION TEMPLATE
       (SUBPARTITION mp0 VALUES IN('A','B'),
        SUBPARTITION mp1 VALUES IN('C','D'),
        SUBPARTITION mp2 VALUES IN('E','F')
       )
       (PARTITION p0 VALUES IN('01','02'),
        PARTITION p1 VALUES IN('03','04'),
        PARTITION p2 VALUES IN('05','06')
       );
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg2_m_lch
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY HASH SUBPARTITIONS 5
       (PARTITION p0 VALUES IN('01','02'),
        PARTITION p1 VALUES IN('03','04'),
        PARTITION p2 VALUES IN('05','06')
       );
Query OK, 0 rows affected

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

obclient> CREATE TABLEGROUP tg2_m_lck
       PARTITION BY LIST COLUMNS 1
       SUBPARTITION BY KEY 1 SUBPARTITIONS 5
       (PARTITION p0 VALUES IN('01','02'),
        PARTITION p1 VALUES IN('03','04'),
        PARTITION p2 VALUES IN('05','06')
       );
Query OK, 0 rows affected

For more information about how to create a subpartitioned table group, see Manage partitions in the OceanBase Database Administrator Guide.