Add tables to a table group

After you create a table group, you can add tables meeting specific conditions to the table group.

Limitations

During the upgrade of OceanBase Database, it is prohibited to use the ALTER TABLEGROUP statement to add tables to a table group.

Prerequisites

Before you add a table to a table group, view the attributes and the partition information about existing tables in the table group to verify whether the current table meets the conditions for joining the table group. For more information about how to view the information about a table group, see Query information about a table group.

If you want to specify a table group when you create a table, make sure that the table meets the conditions for joining the table group.

• A table group with the SHARDING attribute set to NONE has no limitations on the table to be added to the table group. Such a table group can contain non-partitioned tables, partitioned tables, and subpartitioned tables.

  Note

  If you add a table to a table group with the SHARDING = 'NONE' attribute, and the table has a global index, the system automatically binds the global index to the table group to improve performance. For more information about global indexes, see Global indexes.

• A table group with the SHARDING attribute set to PARTITION requires that the table to be added have the same partition definition as existing tables in the table group, including the partitioning type, partition count, and partition value. Such a table group can contain both partitioned tables and subpartitioned tables. The table group can also contain only non-partitioned tables.

  Specifically, the same partition definition means that:

  • Tables in the table group have the same partitioning type, such as RANGE partitioning.
  • HASH-partitioned tables have the same number of referenced columns and the same number of partitions.
  • RANGE-partitioned tables have the same number of referenced columns, the same number of partitions, and the same range definition.

• A table group with the SHARDING attribute set to ADAPTIVE requires that the table to be added have the same partition definition and subpartition definition as existing tables in the table group, including the partitioning type, partition count, and partition value. The tables in the table group must be all partitioned tables or all subpartitioned tables. The table group can also contain only non-partitioned tables.

  Specifically, the same partition definition means that:

  • Tables in the table group have the same partitioning type such as HASH-RANGE partitioning.
  • HASH-partitioned tables have the same number of referenced columns and the same number of partitions.
  • RANGE-partitioned tables have the same number of referenced columns, the same number of partitions, and the same range definition.
  • The requirements on subpartitions are the same as those on partitions, depending on the partitioning type.

Specify a table group while creating a table

After you create a table group, you can specify this table group when you create a table. The syntax is as follows:

CREATE TABLE table_name column_definition TABLEGROUP = tablegroup_name [partition_option];

Some notes about this:

• To execute this statement, you must have the ALTER privilege on tables.

• column_definition specifies the name and data type of the column in the table.

• TABLEGROUP specifies the table group to which the table belongs.

• partition_option specifies the partition information of the table.

The following examples show how to create a table and add it to a table group:

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

  1. Create a table group named tblgroup1 and set the SHARDING attribute to NONE.

     CREATE TABLEGROUP tblgroup1 SHARDING = 'NONE';
     

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

     CREATE TABLE tbl1 (col int) TABLEGROUP = tblgroup1;
     

  3. Create a partitioned table named tbl2 and 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 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 the SHARDING attribute set to PARTITION.

  1. Create a table group named tblgroup2 and set the SHARDING attribute to PARTITION.

     CREATE TABLEGROUP tblgroup2 SHARDING = 'PARTITION';
     

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

     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 partitioning type, partition count, and partition value as the table tbl3, and add the created table 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 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 a table and add it to a table group with the SHARDING attribute set to ADAPTIVE.

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

     CREATE TABLEGROUP tblgroup3 SHARDING = 'ADAPTIVE';
     

  2. Create a subpartitioned table named tbl5 and 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 partitioning type, partition count, and partition value as the table tbl5, and add the created table 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 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 existing tables to a table group

After you create a table group, you can use the ALTER TABLEGROUP statement to add existing tables that meet specific conditions to the table group. The syntax is as follows:

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

Some notes about this:

• To execute this statement, you must have the global ALTER privilege.

• tablegroup_name specifies the name of the table group to which the table is to be added.

• table_name specifies the name of the table to be added to the table group. Separate multiple tables with commas (,).

  When multiple tables are added, the table names can be duplicate. If a table to be added already exists in the table group, the system does not return an error.

The following examples show how to add existing tables to a table group:

• Add an existing table to a table group with the SHARDING attribute set to NONE.

  1. Create a table group named tblgroup4 and set the SHARDING attribute 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 the tables tbl7 and tbl8 to the table group tblgroup4.

     ALTER TABLEGROUP tblgroup4 ADD tbl7,tbl8;
     

  4. View 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 existing tables to a table group with the SHARDING attribute set to PARTITION.

  1. Create a table group named tblgroup5 and set the SHARDING attribute to PARTITION.

     CREATE TABLEGROUP tblgroup5 SHARDING = 'PARTITION';
     

  2. Create two partitioned tables named tbl9 and tbl10 that have 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 the tables tbl9 and tbl10 to the table group tblgroup5.

     ALTER TABLEGROUP tblgroup5 ADD tbl9,tbl10;
     

  4. View 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 and set the SHARDING attribute to ADAPTIVE.

     CREATE TABLEGROUP tblgroup6 SHARDING = 'ADAPTIVE';
     

  2. Create two template-based subpartitioned tables named tbl11 and tbl12 that have the same partition definition.

     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 the tables tbl11 and tbl12 to the table group tblgroup6.

     ALTER TABLEGROUP tblgroup6 ADD tbl11,tbl12;
     

  4. View 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

• Table groups

• Create a table group

• Query information about a table group

• Modify the SHARDING attribute of a table group

• Manage tables in a table group

• Drop a table group