Add tables to a table group

After creating a table group, you can add tables that meet the criteria to the table group.

Limitations

During the upgrade of OceanBase Database, you are not allowed to use the ALTER TABLEGROUP statement to add tables to a table group.

Considerations

Tables with partition weights can be added to a table group. However, after being added, they may affect the overall distribution of the table group. The specific impact on the original table group when a table with partition weights is added is shown in the following table.

Prerequisites

Before adding a table to a table group, you need to check the attributes of the table group and the partition information of the tables within the table group to confirm whether the current table meets the conditions for joining the table group. For detailed operations on checking table group information, see View table group information.

When specifying the table group to which it belongs, the table to be created must meet the conditions for joining that table group.

• For a table group with SHARDING attribute set to NONE, there are no restrictions on the table to be created. A table group can contain non-partitioned tables, partitioned tables, and subpartitioned tables.

  Note

  When adding a table to a table group with the SHARDING = 'NONE' attribute, if the table has global indexes, the system automatically binds these global indexes to the table group to improve performance. For more information about global indexes, see Global indexes.

• For a table group with SHARDING attribute set to PARTITION, the partition of the table to be created must have the same partition definition as all partitions in the table group, including the partition type, number of partitions, and partition values. A table group can contain partitioned tables and subpartitioned tables. It is also supported for the table group to consist entirely of non-partitioned tables.

  Identical partition definitions specifically refer to:

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

• For a table group with SHARDING attribute set to ADAPTIVE, the partition and subpartition of the table to be created must have the same partition definition as all partitions and subpartitions in the table group, including the partition type, number of partitions, and partition values. The table group must consist entirely of partitioned tables or subpartitioned tables. It is also supported for the table group to consist entirely of non-partitioned tables.

  Identical partition definitions specifically refer to:

  • 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, based on their partition type, the requirements are the same as those for partitions.

• For a table group with SHARDING attribute set to SUBPARTITION, the table to be added to the table group must be a subpartitioned table. The partitioning methods and subpartitioning methods for different tables must be the same, 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.

Assign a table to a table group when creating the table

After creating a table group, if the table has not yet been created, you can assign it to the table group by specifying the table group when creating the table. The statement is as follows.

CREATE TABLE table_name column_definition TABLEGROUP = tablegroup_name [partition_option];

Instructions for using the statement:

• To execute this statement, ensure the current user has the ALTER privilege on the table.

• column_definition: Defines the column names and data types of the table.

• TABLEGROUP: Specifies the table group to which the table belongs.

• partition_option: Defines the partitioning information for the table.

The following example shows how to create a table and add it to a table group:

• Create a table and add it to a table group with a SHARDING attribute of NONE.

  1. Create a table group named tblgroup1 with a SHARDING attribute of NONE.

     CREATE TABLEGROUP tblgroup1 SHARDING = 'NONE'; 
     

  2. Create a non-partitioned table named tbl1 and specify to add it to the tblgroup1 table group.

     CREATE TABLE tbl1 (col int) TABLEGROUP = tblgroup1;
     

  3. Create a partitioned table named tbl2 and specify to add it to the tblgroup1 table group.

     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)
      );
     

  4. View the tables in the table group.

     SELECT * FROM oceanbase.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'tblgroup1';
     

     The result is as follows:

     +-----------------+-------+------------+----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
     +-----------------+-------+------------+----------+
     | tblgroup1       | test  | tbl1       | NONE     |
     | tblgroup1       | test  | tbl2       | NONE     |
     +-----------------+-------+------------+----------+
     2 rows in set
     

• Create a table and add it to a table group with a SHARDING attribute of PARTITION.

  1. Create a table group named tblgroup2 with a SHARDING attribute of PARTITION.

     CREATE TABLEGROUP tblgroup2 SHARDING = 'PARTITION'; 
     

  2. Create a partitioned table named tbl3 and specify to add it to the table group named tblgroup2.

     CREATE TABLE tbl3 (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)
     );
     

  3. Create a table named tbl4 that has the same partition type, number of partitions, and partition values as the tbl3 table, and specify to add it to the tblgroup2 table group.

     CREATE TABLE 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. View the tables in the table group.

     SELECT * FROM oceanbase.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'tblgroup2';
     

     The result is as follows:

     +-----------------+-------+------------+-----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING  |
     +-----------------+-------+------------+-----------+
     | tblgroup2       | test  | tbl3       | PARTITION |
     | tblgroup2       | test  | tbl4       | PARTITION |
     +-----------------+-------+------------+-----------+
     2 rows in set
     

• Create tables and add them to a table group with the SHARDING attribute set to ADAPTIVE.

  1. Create a table group named tblgroup3 with the SHARDING attribute set to ADAPTIVE.

     CREATE TABLEGROUP tblgroup3 SHARDING = 'ADAPTIVE'; 
     

  2. Create a subpartitioned table named tbl5 and specify to add it to the tblgroup3 table group.

     CREATE TABLE tbl5(col1 INT,col2 varchar(50)) TABLEGROUP = tblgroup3
     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'))
     );
     

  3. Create a table named tbl6 that has the same partition type, number of partitions, and partition values as the tbl5 table, and specify to add it to the tblgroup3 table group.

     CREATE TABLE tbl6(col1 INT,col2 varchar(50)) TABLEGROUP = tblgroup3
     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'))
     );
     

  4. View the tables in the table group.

     SELECT * FROM oceanbase.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'tblgroup3';
     

     The result is as follows:

     +-----------------+-------+------------+----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
     +-----------------+-------+------------+----------+
     | tblgroup3       | test  | tbl5       | ADAPTIVE |
     | tblgroup3       | test  | tbl6       | ADAPTIVE |
     +-----------------+-------+------------+----------+
     2 rows in set
     

Add an existing table to a table group

After creating a table group, if a table already exists and meets the conditions for joining the table group, you can use the ALTER TABLEGROUP statement to add it to the table group. The statement is as follows.

ALTER TABLEGROUP tablegroup_name ADD [TABLE] table_name [, table_name...];

Instructions for using the statement:

• To execute this statement, ensure the current user has the global ALTER privilege.

• tablegroup_name: The table group to which the table is to be added.

• table_name: The table to be added. When adding multiple tables, separate the table names with commas (,).

  When adding multiple tables, table names can be repeated. If a table to be added is already in the current table group, the system does not report an error.

Examples of adding existing tables to a table group are as follows:

• Add an existing table to a table group whose SHARDING attribute is NONE.

  1. Create a table group named tblgroup4 with its SHARDING attribute set to NONE.

     CREATE TABLEGROUP tblgroup4 SHARDING = 'NONE'; 
     

  2. Create a non-partitioned table named tbl7 and a partitioned table named tbl8.

     CREATE TABLE tbl7 (col int);
     

     CREATE TABLE tbl8 (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)
     );
     

  3. Add both tables tbl7 and tbl8 to the table group tblgroup4 at the same time.

     ALTER TABLEGROUP tblgroup4 ADD tbl7,tbl8;
     

  4. View the tables in the table group.

     SELECT * FROM oceanbase.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'tblgroup4';
     

     The result is as follows:

     +-----------------+-------+------------+----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
     +-----------------+-------+------------+----------+
     | tblgroup4       | test  | tbl7       | NONE     |
     | tblgroup4       | test  | tbl8       | NONE     |
     +-----------------+-------+------------+----------+
     2 rows in set
     

• Add an existing table to a table group whose SHARDING attribute is PARTITION.

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

     CREATE TABLEGROUP tblgroup5 SHARDING = 'PARTITION'; 
     

  2. Create two partitioned tables named tbl9 and tbl10 with the same partition definition.

     CREATE TABLE tbl9 (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)
     );
     

     CREATE TABLE tbl10 (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)
     );
     

  3. Add tables tbl9 and tbl10 to the table group tblgroup5.

     ALTER TABLEGROUP tblgroup5 ADD tbl9,tbl10;
     

  4. View the tables in the table group.

     SELECT * FROM oceanbase.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'tblgroup5';
     

     The result is as follows:

     +-----------------+-------+------------+-----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING  |
     +-----------------+-------+------------+-----------+
     | tblgroup5       | test  | tbl9       | PARTITION |
     | tblgroup5       | test  | tbl10      | PARTITION |
     +-----------------+-------+------------+-----------+
     2 rows in set
     

• Add existing tables to a table group with the SHARDING attribute set to ADAPTIVE.

  1. Create a table group named tblgroup6 with its SHARDING attribute set to ADAPTIVE.

     CREATE TABLEGROUP tblgroup6 SHARDING = 'ADAPTIVE'; 
     

  2. Create two template-based partitioned tables, tbl11 and tbl12, with identical partition definitions.

     CREATE TABLE tbl11(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));
     

     CREATE TABLE tbl12(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));
     

  3. Add tables tbl11 and tbl12 to the table group tblgroup6.

     ALTER TABLEGROUP tblgroup6 ADD tbl11,tbl12;
     

  4. View the tables in the table group.

     SELECT * FROM oceanbase.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'tblgroup6';
     

     The result is as follows:

     +-----------------+-------+------------+----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
     +-----------------+-------+------------+----------+
     | tblgroup6       | test  | tbl11      | ADAPTIVE |
     | tblgroup6       | test  | tbl12      | ADAPTIVE |
     +-----------------+-------+------------+----------+
     2 rows in set
     

References

• About table groups

• Create a table group

• View table group information

• Modify the SHARDING attribute of a table group

• Manage tables within a table group

• Delete a table group