ALTER TABLEGROUP|V5.0.1|OceanBase Database|docs|OceanBase Documentations

ALTER TABLEGROUP

Last Updated：2026-09-04 11:28:25 Updated

Purpose

This statement is used to add multiple tables to a table group and modify the attributes of the table group.

Limitations and considerations

During the upgrade of OceanBase Database, modifying table group attributes is prohibited.
For V5.x versions, starting from V5.0.1, the distribution of partitions within a table group is determined jointly by the SHARDING and SCOPE attributes. The current version only supports certain combinations of the SHARDING and SCOPE attributes. Specifying an unsupported combination will result in an error. Supported combinations include:
- SHARDING = 'NONE' + SCOPE = 'SERVER'
- SHARDING = 'NONE' + SCOPE = 'ZONE'
- SHARDING = 'NONE' + SCOPE = 'CLUSTER'
- SHARDING = 'PARTITION' + SCOPE = 'CLUSTER'
- SHARDING = 'ADAPTIVE' + SCOPE = 'CLUSTER'
- SHARDING = 'SUBPARTITION' + SCOPE = 'CLUSTER'

Syntax

ALTER TABLEGROUP tablegroup_name
    {ADD [TABLE] table_name_list
    | [SET] [sharding_option,] [scope_option]
    };

table_name_list:
    table_name [, table_name ...]

sharding_option:
    SHARDING = '{NONE | PARTITION | ADAPTIVE | SUBPARTITION}'

scope_option:
    SCOPE = '{SERVER | ZONE | CLUSTER}'

Parameters

Parameter	Description
tablegroup_name	The name of the table group to be modified.
ADD [TABLE] table_name_list	This statement is used to add tables to a table group. When adding multiple tables to a table group, separate the table names with commas (','). Table names can be repeated when adding multiple tables. If a table to be added already belongs to the target table group, OceanBase Database does not report an error.
sharding_option	Optional. Specifies the value to modify the table group's`SHARDING`Attribute.`SHARDING`Determines the aggregation method for partitions among tables within a table group. A group of aggregated partitions is called a partition group, which is the smallest unit of distribution and sharding. For more details, see sharding_option.
scope_option	Optional. Specifies the value to be used to modify the table group's`SCOPE`Attribute.`SCOPE`Determines the distribution range of all aggregated partition groups within the table group. For more information, see scope_option. Note For V5.x versions, the `SCOPE` attribute for table groups is supported starting from V5.0.1.

sharding_option

NONE: No restrictions on tables that can be added to the table group.
PARTITION: Indicates the partition of the table to be added to the table group. It must have the same partitioning method as all tables in the table group, including the partition type, number of partitions, and partition values.
ADAPTIVE: Indicates that the partitions of the tables added to the table group, including both primary and secondary partitions, must be consistent with those of all other tables in the group in terms of partition type, number of partitions, and partition values. The tables added to the table group must all be of the same type (either all primary partitioned tables or all subpartitioned tables).
SUBPARTITION: Indicates that the tables added to the table group must be subpartitioned tables. The partitioning methods for the primary partitions of different tables must be the same, and so must the subpartitioning methods, including the partitioning key type, partitioning function, number of partitions, and partition boundary values.

Note

For V5.x versions, the SHARDING = 'SUBPARTITION' attribute for table groups is supported starting from V5.0.1.

The same partitioning methods include:

The partition types are the same (for example, both are HASH+RANGE partitions).
For HASH partitioning, the number of referenced columns and the number of partitions must be the same.
For RANGE partitions, the number of referenced columns, the number of partitions, and the RANGE partitioning points must be the same.

For subpartitions, the requirements are the same as those above, depending on their partition type.

scope_option

SERVER: Indicates that the leaders of all partition groups are located on the same node.
ZONE: Indicates that the leaders of all partition groups are distributed within the same zone and are scattered across nodes within that zone.
CLUSTER: Indicates that the leaders of all partition groups are distributed across nodes in the cluster.

Examples

Create a table group named tblgroup1 with its SHARDING attribute set to NONE, and add the partitioned tables tbl1 and tbl2 to this table group.

obclient> CREATE TABLEGROUP tblgroup1 SHARDING = 'NONE';
obclient> CREATE TABLE tbl1(col1 INT,col2 VARCHAR(50))
    PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
      (PARTITION p0 VALUES in (01)
         (SUBPARTITION mp0 VALUES in ('01'),
          SUBPARTITION mp1 VALUES in ('02')),
       PARTITION p1 VALUES in (02)
          (SUBPARTITION mp3 VALUES in ('01'),
           SUBPARTITION mp4 VALUES in ('02'))
       );
obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50))
    PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
      (PARTITION p0 VALUES in (01)
        (SUBPARTITION mp0 VALUES in ('01'),
          SUBPARTITION mp1 VALUES in ('02')),
       PARTITION p1 VALUES in (02)
         (SUBPARTITION mp3 VALUES in ('01'),
          SUBPARTITION mp4 VALUES in ('02'))
       );
obclient> ALTER TABLEGROUP tblgroup1 ADD tbl1,tbl2;

Since tbl1 and tbl2 use the same partitioning method, the SHARDING attribute can be changed to PARTITION or ADAPTIVE.

obclient> ALTER TABLEGROUP tblgroup1 SHARDING = 'PARTITION';
obclient> ALTER TABLEGROUP tblgroup1 SHARDING = 'ADAPTIVE';

Create a table group named tblgroup2 with its SHARDING attribute set to NONE. Add the non-partitioned table tbl1 and the partitioned table tbl2 to the table group.

obclient> CREATE TABLEGROUP tblgroup2 SHARDING = 'NONE';
obclient> CREATE TABLE tbl1 (col INT);
obclient> CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
    PARTITION BY LIST(col1)
      (PARTITION p0 VALUES IN (1, 2, 3),
       PARTITION p1 VALUES IN (5, 6),
       PARTITION p2 VALUES IN (DEFAULT)
      );
obclient> ALTER TABLEGROUP tblgroup2 ADD tbl1,tbl2;

Since the table group tblgroup2 contains both non-partitioned tables and partitioned tables, the SHARDING attribute cannot be changed to PARTITION or ADAPTIVE.

obclient> ALTER TABLEGROUP tblgroup2 SHARDING = 'PARTITION';
ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, modify tablegroup sharding attribute not allowed

obclient> ALTER TABLEGROUP tblgroup2 SHARDING = 'ADAPTIVE';
ERROR 4179 (HY000): not all tables are non-partitioned or partitioned, modify tablegroup sharding attribute not allowed

Create a table group named tblgroup3 with its SHARDING attribute set to NONE. Add the partitioned table tbl1 and the partitioned table tbl2 to the table group.

obclient> CREATE TABLEGROUP tblgroup3 SHARDING = 'NONE';


obclient> CREATE TABLE tbl1(col1 INT,col2 VARCHAR(50))
   PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
    (PARTITION p0 VALUES in (01)
      (SUBPARTITION mp0 VALUES in ('01'),
       SUBPARTITION mp1 VALUES in ('02'),
       SUBPARTITION mp2 VALUES in ('03')
       ),
     PARTITION p1 VALUES in (02)
       (SUBPARTITION mp3 VALUES in ('01'),
        SUBPARTITION mp4 VALUES in ('02'),
        SUBPARTITION mp5 VALUES in ('03'))
     );
obclient> CREATE TABLE tbl2(col1 INT,col2 VARCHAR(50))
   PARTITION BY LIST(col1) SUBPARTITION BY LIST COLUMNS(col2)
    (PARTITION p0 VALUES in (01)
      (SUBPARTITION mp0 VALUES in ('01'),
       SUBPARTITION mp1 VALUES in ('02')),
     PARTITION p1 VALUES in (02)
      (SUBPARTITION mp3 VALUES in ('01'),
       SUBPARTITION mp4 VALUES in ('02'))
     );
obclient> ALTER TABLEGROUP tblgroup3 ADD tbl1,tbl2;

Since the partition types of the partitions in tables tbl1 and tbl2 within the table group tblgroup3 are the same, but the subpartition types are different (the number of subpartitions is different), the SHARDING attribute can be changed to PARTITION, but not to ADAPTIVE.

obclient> ALTER TABLEGROUP tblgroup3 SHARDING = 'PARTITION';

obclient> ALTER TABLEGROUP tblgroup3 SHARDING = 'ADAPTIVE';
ERROR 4179 (HY000): subpartition num not matched, modify tablegroup sharding attribute not allowed

Create a table group named tblgroup4 with its SHARDING attribute set to ADAPTIVE. Then, modify the number of partitions for tables tbl1 and tbl2 within the table group. At this point, the partitioning methods for all tables in the table group are inconsistent, and the new table tbl3 cannot be added to the table group.

obclient> CREATE TABLEGROUP tblgroup4 SHARDING = 'ADAPTIVE';

obclient> CREATE TABLE tbl1(col1 INT,col2 INT)
    PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
      SUBPARTITION TEMPLATE
       (SUBPARTITION mp0 VALUES LESS THAN(2020),
        SUBPARTITION mp1 VALUES LESS THAN(2021),
        SUBPARTITION mp2 VALUES LESS THAN(2022))
       (PARTITION p0 VALUES LESS THAN(100),
        PARTITION p1 VALUES LESS THAN(200));
obclient> CREATE TABLE tbl2(col1 INT,col2 INT)
    PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
      SUBPARTITION TEMPLATE
       (SUBPARTITION mp0 VALUES LESS THAN(2020),
        SUBPARTITION mp1 VALUES LESS THAN(2021),
        SUBPARTITION mp2 VALUES LESS THAN(2022))
      (PARTITION p0 VALUES LESS THAN(100),
       PARTITION p1 VALUES LESS THAN(200));
obclient> ALTER TABLEGROUP tblgroup4 ADD tbl1,tbl2;
obclient> ALTER TABLE tbl1
    ADD PARTITION (PARTITION p3 VALUES LESS THAN(400),
                   PARTITION p4 VALUES LESS THAN(500));
obclient> ALTER TABLE tbl2 DROP SUBPARTITION p0smp0,p0smp1;
obclient> CREATE TABLE tbl3(col1 INT,col2 INT)
    PARTITION BY RANGE(col1) SUBPARTITION BY RANGE(col2)
      SUBPARTITION TEMPLATE
       (SUBPARTITION mp0 VALUES LESS THAN(2020),
        SUBPARTITION mp1 VALUES LESS THAN(2021),
        SUBPARTITION mp2 VALUES LESS THAN(2022))
       (PARTITION p0 VALUES LESS THAN(100),
        PARTITION p1 VALUES LESS THAN(200));
obclient> ALTER TABLEGROUP tblgroup4 ADD tbl3;
ERROR 4179 (HY000): partition num not equal, add table to tablegroup not allowed

Create a table group named tblgroup5 with its SHARDING attribute set to ADAPTIVE, and then change the SHARDING attribute of table group tblgroup5 to SUBPARTITION.
```
obclient> CREATE TABLEGROUP tblgroup5 SHARDING = 'ADAPTIVE';
```
```
obclient> ALTER TABLEGROUP tblgroup5 SET SHARDING = 'SUBPARTITION';
```

OceanBase

Customer Stories

Documentation