Add a subpartitioned table to a table group

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

OceanBase Database in Oracle 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-RANGE-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 tbl1 and specify the table group tg2_f_rr.

   obclient> CREATE TABLE tbl1(col1 int, col2 int) TABLEGROUP = tg2_f_rr
          PARTITION BY RANGE(col1)
          SUBPARTITION BY RANGE(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-RANGE-subpartitioned table group tg2_m_rr by using a template.

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

2. Create a RANGE-RANGE-subpartitioned table t2_m_rr by using a template. Ensure that the table has the same number of partitions as the table group tg2_m_rr.

   obclient> CREATE TABLE t2_m_rr (col1 INT, col2 INT)
          PARTITION BY RANGE(col1)
          SUBPARTITION BY RANGE(col2)
          SUBPARTITION TEMPLATE
           (SUBPARTITION sp1 VALUES LESS THAN (2019),
            SUBPARTITION sp2 VALUES LESS THAN (2020),
            SUBPARTITION sp3 VALUES LESS THAN (2021)
           )
           (PARTITION p0 VALUES LESS THAN (100),
            PARTITION p1 VALUES LESS THAN (200)
           );
   Query OK, 0 rows affected
   

3. Add the t2_m_rr table to the table group tg2_m_rr.

   obclient> ALTER TABLEGROUP tg2_m_rr ADD TABLE t2_m_rr;
   

   or

   obclient> ALTER TABLE t2_m_rr SET TABLEGROUP tg2_m_rr;
   

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.