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 the SHARDING attribute of a table group, you must first check whether any tables exist in the table group and the partition information of those 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 its SHARDING attribute.

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

  • To 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. You can modify the attribute directly.

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

    • All tables in the table group are non-partitioned tables. In this case, you can modify the attribute directly.
    • The table group contains both non-partitioned tables and partitioned tables. In this case, you cannot modify the attribute.
    • The table group contains only partitioned tables or only subpartitioned tables. In this case, all partitions of all tables in the table group must have the same partition type, the same number of partitions, and the same partition values. Otherwise, you cannot modify the attribute.

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

    • All tables in the table group are non-partitioned tables. In this case, you can modify the attribute directly.
    • The table group contains both non-partitioned tables and partitioned tables. In this case, you cannot modify the attribute.
    • The table group contains both partitioned tables and subpartitioned tables. In this case, you cannot modify the attribute.
    • All tables in the table group are partitioned tables. In this case, all partitioned tables must have the same partition type, the same number of partitions, and the same partition values.
    • All tables in the table group are subpartitioned tables. In this case, all subpartitioned tables must have the same partition type, the same number of subpartitions, and the same subpartition values.

• 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 for table groups is supported starting from V5.0.1.

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

• To 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.
  • All tables must use the same partitioning method and the same number of partitions.
  • This check is not required if no tables exist in the table group.

Syntax

The SQL statement for modifying table group attributes 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: Specifies the SHARDING attribute of the table group. The SHARDING attribute 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 of tables added to the table group, and partitions are not aggregated across tables. In versions earlier than V5.0.1 (excluding V4.4.2), the NONE semantics also meant all partitions were 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 partition definitions that match those of all other tables in the table group, including the partition type, number of partitions, and partition values. Identical partition definitions specifically mean:

    • The same partition type, for example, both are RANGE partitions.
    • For HASH partitions, 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 split points must be the same.

  • ADAPTIVE: The default value. Requires that all tables added to the table group have partition and subpartition definitions that match those of all other tables in the table group, including the partition type, number of partitions, and partition values.

    • All tables are partitioned tables: 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 are subpartitioned tables: Partitions indexed by the same partitioning key form a balanced group, and subpartitions within the same 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.
    • For HASH partitions, 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 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 subpartitioned tables. The partitioning and subpartitioning methods must be the same across different tables, including the partitioning key type, partitioning function, number of partitions, and partition boundary values.

    Note

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

• scope_option: Optional. Specifies the SCOPE attribute of the table group. The SCOPE attribute 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 partition definitions, and add them to the tblgroup1 table group.

   CREATE TABLE tbl1(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup1
   PARTITION BY LIST(col1)
   (PARTITION p0 VALUES('01'),
   PARTITION p1 VALUES('02')
   );
   

   CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup1
   PARTITION BY LIST(col1)
   (PARTITION p0 VALUES('01'),
   PARTITION p1 VALUES('02')
   );
   

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       | SYS           | ADAPTIVE |
   | TBLGROUP1       | TBL2       | SYS           | ADAPTIVE |
   +-----------------+------------+---------------+----------+
   2 rows in set
   

   The tables TBL1 and TBL2 within the table group are both partitioned tables, with the same partition type, number of partitions, and partition values, which complies with the modification rules. The SHARDING attribute has been 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 NUMBER PRIMARY KEY, col2 VARCHAR2(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 NUMBER PRIMARY KEY, col2 VARCHAR2(50))
       TABLEGROUP = tblgroup2
       PARTITION BY LIST(col1)
       (PARTITION p0 VALUES('01'),
        PARTITION p1 VALUES('02')
        );
   

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_USER001  | NONE     | SERVER |
   | TBLGROUP2       | TEST_TBL4  | TEST_USER001  | 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 NUMBER PRIMARY KEY, col2 VARCHAR2(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 NUMBER PRIMARY KEY, col2 VARCHAR2(50))
       TABLEGROUP = tblgroup2
       PARTITION BY LIST(col1)
       (PARTITION p0 VALUES('01'),
        PARTITION p1 VALUES('02')
        );
   

4. Change 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 query result is as follows:

   +-----------------+------------+---------------+----------+--------+
   | TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME | SHARDING | SCOPE  |
   +-----------------+------------+---------------+----------+--------+
   | TBLGROUP2       | TEST_TBL3  | TEST_USER001  | NONE     | SERVER |
   | TBLGROUP2       | TEST_TBL4  | TEST_USER001  | 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 in a table group

• Delete a table group