Create a table group

You can create a table group based on your business requirements.

Limitations

• Creating a table group is prohibited during an OceanBase Database upgrade.

• For V5.x versions, starting from V5.0.1, the distribution of partitions within a table group is determined by both 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

Creating a table group requires the current user to have global CREATE (*.*) privileges. The SQL statement for creating a table group is as follows:

CREATE TABLEGROUP [IF NOT EXISTS] tablegroup_name
    [sharding_option,]
    [scope_option];

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

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

Parameter description:

• IF NOT EXISTS: If the table group name to be created already exists and the IF NOT EXISTS option is not used, an error message is triggered. When IF NOT EXISTS is specified, no error is returned; only a warning message is generated.

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

• sharding_option: Specifies the SHARDING attribute of the table group. SHARDING determines how partitions corresponding to tables within the table group are aggregated. An aggregated set of partitions is called a Partition Group, which is the smallest unit of distribution. Valid values are as follows:

  • NONE: Indicates that there are no restrictions on the partitioning method of tables added to the table group, and partitions are not aggregated between tables. In versions prior to V5.0.1 (excluding V4.4.2), the NONE semantics also meant that all partitions were on the same log stream without being dispersed. This semantics has been removed in newer versions; whether dispersion occurs specifically depends on the SCOPE attribute.

    • For a partitioned table: Each partition serves as an independent Partition Group.
    • For a subpartitioned table: Each subpartition serves as an independent Partition Group.
    • For a non-partitioned table: The entire table serves as a Partition Group.
    • Global indexes: In a SHARDING = NONE table group, the global index tables of tables within the group are automatically bound to the same table group. Global non-partitioned index tables are treated as a single Partition Group, while each partition of a global partitioned index table serves as an independent Partition Group.

  • PARTITION: The entire table group is a Balance Group. Partitions indexed by the same partition serve are aggregated into the same Partition Group. All tables added to the table group must have partition definitions that match those of all 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 segmentation points must be the same.

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

    • All are partitioned tables: The entire table group is a Balance Group. Partitions indexed by the same partition serve are aggregated into the same Partition Group.
    • All are subpartitioned tables: Partitions indexed by the same partition serve form a Balance Group, and subpartitions within the same subpartition serve under this Balance Group are aggregated into the same Partition Group.

    Identical partition definitions specifically mean:

    • The partitioning types must be the same. For example, both must be HASH+RANGE partitioning.
    • For HASH partitioning, the number of referenced columns and the number of partitions must be the same.
    • For RANGE partitioning, 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 for partitions based on their partitioning type.

  • SUBPARTITION: Indicates that tables added to the table group must be subpartitioned tables. Different tables must have the same partitioning method and subpartitioning method, including the partitioning key type, partitioning function, number of partitions, partition boundary values, etc.

    Note

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

• scope_option: Optional. Specifies the SCOPE attribute for the 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 in the cluster.

  If the SCOPE attribute is not specified for a table group, it will be automatically set to the corresponding default SCOPE based on the SHARDING attribute. The specific correspondence is as follows:

  • When SHARDING = 'NONE', the value of SCOPE is SERVER.
  • When SHARDING = 'PARTITION', the value of SCOPE is CLUSTER.
  • When SHARDING = 'ADAPTIVE', the value of SCOPE is CLUSTER.

  Notice

  • For V5.x versions, the SCOPE attribute for table groups is supported starting from V5.0.1.
  • Table groups upgraded from lower versions also determine their SCOPE according to the same rules.

Partition distribution method

The distribution method of partitions within a table group is jointly determined by SHARDING and SCOPE. The distribution methods for various combinations of SHARDING and SCOPE attributes are as follows:

Examples

• Create a table group named tblgroup1 with SHARDING = 'NONE' and SCOPE = 'ZONE'.

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

• Create a table group named tblgroup2 with SHARDING = 'NONE'.

  obclient> CREATE TABLEGROUP tblgroup2
      SHARDING = 'NONE';
  

• Create a table group named tblgroup3 with SCOPE = 'CLUSTER'.

  obclient> CREATE TABLEGROUP tblgroup3
      SCOPE = 'CLUSTER';
  

• Creating a table group by specifying only SCOPE = 'ZONE' will result in an error. This is because the default value of SHARDING is ADAPTIVE, which does not support the combination SHARDING = 'ADAPTIVE' and SCOPE = 'ZONE'.

  obclient> CREATE TABLEGROUP tblgroup4
      SCOPE = 'ZONE';
  

  The return result is as follows:

  ERROR 4179 (HY000): create tablegroup with sharding is ADAPTIVE and scope is ZONE not allowed
  

• Create a table group named tblgroup5 with SHARDING = 'SUBPARTITION' and SCOPE = 'CLUSTER'.

  obclient> CREATE TABLEGROUP tgroup5
      SHARDING = 'SUBPARTITION',
      SCOPE = 'CLUSTER';
  

What to do next

After creating a table group, you can add existing tables that meet the criteria to the table group. For more information, see Add a table to a table group.

References

• About table groups

• Add a table to a table group

• Modify the SHARDING attribute of a table group

• Manage tables within a table group

• Delete a table group

• Set the weight of a table group