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

The SQL statement for creating a table group is as follows:

CREATE TABLEGROUP tablegroup_name
    [sharding_option,]
    [scope_option];

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 created.

• sharding_option: Specifies the SHARDING attribute of the 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 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 across 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 scattered. This semantics has been removed in newer versions; whether scattering 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 act 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 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 the partition and subpartition definitions of all tables added to the table group match those of all tables in the 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. Subpartitions indexed by the same subpartition serve within this Balance Group are aggregated into the same Partition Group.

    Identical partition definitions specifically mean:

    • The partition types must be the same. For example, both must be 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 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, and partition boundary values.

    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 a single node.
  • ZONE: Indicates that the Leaders of all Partition Groups are distributed within the same Zone, 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', SCOPE is set to SERVER.
  • When SHARDING = 'PARTITION', SCOPE is set to CLUSTER.
  • When SHARDING = 'ADAPTIVE', SCOPE is set to 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 for SHARDING is ADAPTIVE, which does not support the combination SHARDING = 'ADAPTIVE' + SCOPE = 'ZONE'.

  obclient> CREATE TABLEGROUP tblgroup4
      SCOPE = 'ZONE';
  

  The return result is as follows:

  OBE-00600: internal error code, arguments: -4179, 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