Modify table group attributes

After creating a table group, you can modify its attributes based on business requirements.

Limitations

• Modifying table group attributes is prohibited during an OceanBase Database upgrade.
• You must have the global ALTER privilege to execute this statement.

Modification rules

When modifying table group attributes, you must first check whether any tables exist in the table group and the partition information of the tables. For more information about how to view tables in a table group, see View table group information.

• If no tables exist in the table group, you can freely modify the SHARDING attribute of the table group.

• If tables exist in the table group, follow these modification rules:

  • When you change the SHARDING attribute of a table group to NONE, you do not need to consider the partition definitions of the tables in the table group and can modify the attribute directly.

  • When you change the SHARDING attribute of a table group to PARTITION:

    • If all tables in the table group are non-partitioned tables, you can modify the attribute directly.
    • If the table group contains both non-partitioned and partitioned tables, you cannot modify the attribute.
    • If the table group contains only partitioned tables or only subpartitioned tables, the partition types, numbers, and values of the partitions of all tables in the table group must be the same; otherwise, you cannot modify the attribute.

  • When you change the SHARDING attribute of a table group to ADAPTIVE, you must meet the following conditions:

    • If all tables in the table group are non-partitioned tables, you can modify the attribute directly.
    • If the table group contains both non-partitioned and partitioned tables, you cannot modify the attribute.
    • If the table group contains both partitioned and subpartitioned tables, you cannot modify the attribute.
    • If all tables in the table group are partitioned tables, the partition types, numbers, and values of the partitions of all tables in the table group must be the same.
    • If all tables in the table group are subpartitioned tables, the partition types, numbers, and values of the partitions and subpartitions of all tables in the table group must be the same.

• When modifying table group attributes in V5.0.1 or later, you must also comply with the following combinations of SHARDING and SCOPE:

  Note

  The SCOPE attribute of table groups is supported starting from V5.0.1 in V5.x series.

  • SHARDING = 'NONE' + SCOPE = 'SERVER'
  • SHARDING = 'NONE' + SCOPE = 'ZONE'
  • SHARDING = 'NONE' + SCOPE = 'CLUSTER'
  • SHARDING = 'PARTITION' + SCOPE = 'CLUSTER'
  • SHARDING = 'ADAPTIVE' + SCOPE = 'CLUSTER'
  • SHARDING = 'SUBPARTITION' + SCOPE = 'CLUSTER'

• When you change the SHARDING attribute of a table group to SUBPARTITION, you must meet the following conditions:

  • All tables in the table group must be subpartitioned tables.
  • The partitioning methods of all tables must be the same.
  • This check is not required if no tables exist in the table group.

Syntax

The SQL statement for modifying a table group attribute is as follows:

ALTER TABLEGROUP tablegroup_name
    [sharding_option,]
    [scope_option];

table_name_list:
    table_name [, table_name ...]

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

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

Parameter description:

• tablegroup_name: The name of the table group to be modified.

• sharding_option: Used to modify the SHARDING attribute of a table group. SHARDING determines how partitions are aggregated among tables within the table group. A group of aggregated partitions is called a Partition Group, which is the smallest unit of distribution. Valid values:

  • NONE: Indicates no restrictions on the partitioning method for tables added to the table group, and partitions are not aggregated across tables. In versions prior to V5.0.1 (excluding V4.4.2), the NONE semantics also included all partitions remaining together on the same log stream without dispersion. This semantics has been removed in newer versions; whether dispersion occurs now depends on the SCOPE attribute.

    • For a table with partitions: Each partition serves as an independent Partition Group.
    • For a table with subpartitions: Each subpartition serves as an independent Partition Group.
    • For a non-partitioned table: The entire table serves as a Partition Group.
    • Global indexes: Non-partitioned tables are treated as partitions, with each partition serving as a Partition Group.

  • PARTITION: The entire table group is a Balance Group, where partitions indexed by the same partitioning key are aggregated into the same Partition Group. All tables added to the table group must have partitions with the same definition, including partition type, number of partitions, and partition values. Identical partition definitions specifically mean:

    • The same partition type, for example, both are RANGE partitions.
    • If it is a HASH partition, the number of referenced columns and the number of partitions must be the same.
    • If it is a RANGE partition, the number of referenced columns, the number of partitions, and the RANGE split points must be the same.

  • ADAPTIVE: The default value. Requires that all tables added to the table group have partitions and subpartitions with the same definition as all tables in the table group, including partition type, number of partitions, and partition values.

    • All are tables with partitions: The entire table group is a Balance Group, where partitions indexed by the same partitioning key are aggregated into the same Partition Group.
    • All are tables with subpartitions: Partitions indexed by the same partitioning key form a balanced group, and subpartitions within the same subpartitioning key within this balanced group are aggregated into the same Partition Group.

    Identical partition definitions specifically mean:

    • The same partition type, for example, both are HASH + RANGE partitions.
    • If it is a HASH partition, the number of referenced columns and the number of partitions must be the same.
    • If it is a RANGE partition, the number of referenced columns, the number of partitions, and the RANGE split points must be the same.
    • For subpartitions, the requirements are the same as those for partitions based on their partition type.

  • SUBPARTITION: Indicates that tables added to the table group must be tables with subpartitions. The partitioning methods (including partitioning key types, partitioning functions, number of partitions, and partition boundary values) must be the same across different tables.

    Note

    For V5.x versions, support for the SHARDING = 'SUBPARTITION' attribute of table groups starts from V5.0.1.

• scope_option: Optional. Used to modify the SCOPE attribute of a table group. SCOPE determines the distribution scope of all aggregated Partition Groups within the table group. Valid values:

  • SERVER: Indicates that the Leaders of all Partition Groups are aggregated 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 scattered across nodes within the cluster.

  Note

  For V5.x versions, the SCOPE attribute for table groups is supported starting from V5.0.1.

Examples

Example 1: Modify the SHARDING = 'PARTITION' table group attribute

1. Create a table group named tblgroup1 with its SHARDING attribute set to PARTITION.

   CREATE TABLEGROUP tblgroup1 SHARDING = 'PARTITION'; 
   

2. Create two partitioned tables named tbl1 and tbl2 with identical definitions, and add them to the tblgroup1 table group.

   CREATE TABLE tbl1 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup1
   PARTITION BY LIST(col1)
   (PARTITION p0 VALUES IN (1, 2, 3),
   PARTITION p1 VALUES IN (5, 6),
   PARTITION p2 VALUES IN (DEFAULT)
   );
   

   CREATE TABLE tbl2 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50)) TABLEGROUP = tblgroup1
   PARTITION BY LIST(col1)
   (PARTITION p0 VALUES IN (1, 2, 3),
   PARTITION p1 VALUES IN (5, 6),
   PARTITION p2 VALUES IN (DEFAULT)
   );
   

3. Change the SHARDING attribute of the tblgroup1 table group to ADAPTIVE.

   ALTER TABLEGROUP tblgroup1 SHARDING = "ADAPTIVE";
   

4. View the table group attributes.

   SHOW TABLEGROUPS WHERE tablegroup_name = 'tblgroup1';
   

   The result is as follows:

   +-----------------+------------+---------------+----------+
   | Tablegroup_name | Table_name | Database_name | Sharding |
   +-----------------+------------+---------------+----------+
   | tblgroup1       | tbl1       | test          | ADAPTIVE |
   | tblgroup1       | tbl2       | test          | ADAPTIVE |
   +-----------------+------------+---------------+----------+
   2 rows in set
   

   The tables tbl1 and tbl2 within the table group are both partitioned tables, and they have the same partition type, number of partitions, and partition values, which complies with the modification rules. The SHARDING attribute was successfully modified.

Example 2: Modify the SHARDING = 'NONE' + SCOPE = 'ZONE' table group attribute

1. Create a table group named tblgroup2 with its SHARDING attribute set to 'NONE' + SCOPE = 'ZONE'.

   obclient> CREATE TABLEGROUP tblgroup2
       SHARDING = 'NONE',
       SCOPE = 'ZONE';
   

2. Create a non-partitioned table named test_tbl3 and add it to the tblgroup2 table group.

   obclient> CREATE TABLE test_tbl3 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
       TABLEGROUP = tblgroup2;
   

3. Create a partitioned table named test_tbl4 and add it to the tblgroup2 table group.

   obclient> CREATE TABLE test_tbl4 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
       TABLEGROUP = tblgroup2
       PARTITION BY LIST(col1)
       (PARTITION p0 VALUES IN (1, 2, 3),
        PARTITION p1 VALUES IN (5, 6),
        PARTITION p2 VALUES IN (DEFAULT)
        );
   

4. Modify the attributes of table group tblgroup2 to SHARDING = 'NONE' + SCOPE = 'SERVER'.

   obclient> ALTER TABLEGROUP tblgroup2 SCOPE = 'SERVER';
   

5. View the attributes of the table group.

   obclient> SHOW TABLEGROUPS WHERE tablegroup_name = 'tblgroup2';
   

   The result is as follows:

   +-----------------+------------+---------------+----------+--------+
   | Tablegroup_name | Table_name | Database_name | Sharding | Scope  |
   +-----------------+------------+---------------+----------+--------+
   | tblgroup2       | test_tbl3  | test_db       | NONE     | SERVER |
   | tblgroup2       | test_tbl4  | test_db       | NONE     | SERVER |
   +-----------------+------------+---------------+----------+--------+
   2 rows in set
   

Example 2: Modify the SHARDING = 'NONE' + SCOPE = 'ZONE' table group attribute

1. Create a table group named tblgroup2 with the attribute SHARDING = 'NONE' + SCOPE = 'ZONE'.

   obclient> CREATE TABLEGROUP tblgroup2
       SHARDING = 'NONE',
       SCOPE = 'ZONE';
   

2. Create a non-partitioned table named test_tbl3 and add it to the tblgroup2 table group.

   obclient> CREATE TABLE test_tbl3 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
       TABLEGROUP = tblgroup2;
   

3. Create a partitioned table named test_tbl4 and add it to the tblgroup2 table group.

   obclient> CREATE TABLE test_tbl4 (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
       TABLEGROUP = tblgroup2
       PARTITION BY LIST(col1)
       (PARTITION p0 VALUES IN (1, 2, 3),
        PARTITION p1 VALUES IN (5, 6),
        PARTITION p2 VALUES IN (DEFAULT)
        );
   

4. Modify the attribute of the tblgroup2 table group to SHARDING = 'NONE' + SCOPE = 'SERVER'.

   obclient> ALTER TABLEGROUP tblgroup2 SCOPE = 'SERVER';
   

5. Query the attributes of the table group.

   obclient> SHOW TABLEGROUPS WHERE tablegroup_name = 'tblgroup2';
   

   The result is as follows:

   +-----------------+------------+---------------+----------+--------+
   | Tablegroup_name | Table_name | Database_name | Sharding | Scope  |
   +-----------------+------------+---------------+----------+--------+
   | tblgroup2       | test_tbl3  | test_db       | NONE     | SERVER |
   | tblgroup2       | test_tbl4  | test_db       | NONE     | SERVER |
   +-----------------+------------+---------------+----------+--------+
   2 rows in set
   

References

• About table groups

• Create a table group

• View table group information

• Add tables to a table group

• Manage tables within a table group

• Delete a table group