Add a subpartitioned table to a table group

After you create a subpartitioned table group, you can add subpartitioned tables to the table group.

OceanBase Database in MySQL mode allows you to add a subpartitioned table to a subpartitioned table group in the following two ways:

• Specify a table group when you create a subpartitioned table.

• Create a subpartitioned table and then add it to a table group.

Considerations

Comply with the following rules when you add a partitioned table to a partitioned table group:

• To add a partitioned table to an existing partitioned table group, ensure that this partitioned table is of the same partitioning type and has the same number of partitions as this table group.

  If the locality and primary zone information is specified for the partitioned table and table group, this information must be consistent between the table and table group.

• Ensure that you have the CREATE TABLE privilege on the target table and table group.

Specify a table group when you create a subpartitioned table

You can execute the CREATE TABLE statement to create a subpartitioned table and specify a table group for the table during the creation process.

Example:

1. Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table group tg2_f_rr without using a template.

   obclient> CREATE TABLEGROUP tg2_f_rr
          PARTITION BY RANGE COLUMNS 1
          SUBPARTITION BY RANGE COLUMNS 1
          (PARTITION p0 VALUES LESS THAN (100)
           (SUBPARTITION sp1 VALUES LESS THAN (2019),
            SUBPARTITION sp2 VALUES LESS THAN (2020),
            SUBPARTITION sp3 VALUES LESS THAN (2021)
            ),
           PARTITION p1 VALUES LESS THAN (200)
           (SUBPARTITION sp5 VALUES LESS THAN (2019),
            SUBPARTITION sp6 VALUES LESS THAN (2020),
            SUBPARTITION sp7 VALUES LESS THAN (2021)
           )
          );
   Query OK, 0 rows affected
   

2. Create a subpartitioned table t2_f_rr and specify the table group tg2_f_rr.

   obclient> CREATE TABLE t2_f_rr(col1 int, col2 int)
          TABLEGROUP = tg2_f_rr
          PARTITION BY RANGE COLUMNS(col1)
          SUBPARTITION BY RANGE COLUMNS(col2)
          (PARTITION p0 VALUES LESS THAN(100)
           (SUBPARTITION p0_r1 VALUES LESS THAN(2019),
            SUBPARTITION p0_r2 VALUES LESS THAN(2020),
            SUBPARTITION p0_r3 VALUES LESS THAN(2021)
           ),
           PARTITION p1 VALUES LESS THAN(200)
          (SUBPARTITION p1_r1 VALUES LESS THAN(2019),
           SUBPARTITION p1_r2 VALUES LESS THAN(2020),
           SUBPARTITION p1_r3 VALUES LESS THAN(2021)
           )
          );
   Query OK, 0 rows affected
   

Create a subpartitioned table and then add it to a table group

After you create a subpartitioned table, you can execute the ALTER TABLEGROUP or ALTER TABLE statement to add the table to an existing table group.

Example:

1. Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table group tg2_m_rcrc by using a template.

   obclient> CREATE TABLEGROUP tg2_m_rcrc
          PARTITION BY RANGE COLUMNS 1
          SUBPARTITION BY RANGE COLUMNS 1
          SUBPARTITION TEMPLATE
          (SUBPARTITION mp0 VALUES LESS THAN(2019),
           SUBPARTITION mp1 VALUES LESS THAN(2020),
           SUBPARTITION mp2 VALUES LESS THAN(2021)
          )
          (PARTITION p0 VALUES LESS THAN(100),
           PARTITION p1 VALUES LESS THAN(200),
           PARTITION p2 VALUES LESS THAN(300)
          );
   Query OK, 0 rows affected
   

2. Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table t2_m_rcrc by using a template. Ensure that this subpartitioned table has the same number of partitions as the table group tg2_m_rcrc.

   obclient> CREATE TABLE t2_m_rcrc (col1 INT,col2 INT)
          PARTITION BY RANGE COLUMNS(col1)
          SUBPARTITION BY RANGE COLUMNS(col2)
          SUBPARTITION TEMPLATE
          (SUBPARTITION mp0 VALUES LESS THAN(2019),
           SUBPARTITION mp1 VALUES LESS THAN(2020),
           SUBPARTITION mp2 VALUES LESS THAN(2021)
          )
          (PARTITION p0 VALUES LESS THAN(100),
           PARTITION p1 VALUES LESS THAN(200),
           PARTITION p2 VALUES LESS THAN(300)
          );
   Query OK, 0 rows affected
   

3. Add the table to the table group.

   obclient> ALTER TABLEGROUP tg2_m_rcrc ADD TABLE t2_m_rcrc;
   Query OK, 0 rows affected
   

   or

   obclient> ALTER TABLE t2_m_rcrc SET TABLEGROUP tg2_m_rcrc;
   Query OK, 0 rows affected
   

After you add a subpartitioned table to a subpartitioned table group, you can remove the table from the group, move the table to another table group, or drop the table group. The operations are the same as those for a common table group. For more information, see Statements for table group management.