Add a partitioned table to a table group

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

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

• Specify a table group when you create a table.

• Create a partitioned 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 table

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

Example:

1. Create a RANGE COLUMNS-partitioned table group tg1_rc.

   obclient> CREATE TABLEGROUP tg1_rc PARTITION BY RANGE COLUMNS 1
               ( 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-partitioned table and add it to the tg1_rc table group.

   obclient> CREATE TABLE t1_rc (id INT NOT NULL,name varchar(50),t_date date NOT NULL)
               TABLEGROUP =tg1_rc
               PARTITION BY RANGE COLUMNS(id)
               ( PARTITION p0 VALUES LESS THAN(100),
                 PARTITION p1 VALUES LESS THAN(200),
                 PARTITION p2 VALUES LESS THAN(300)
               );
   Query OK, 0 rows affected
   

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

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

SQL syntax:

• ALTER TABLEGROUP statement

  obclient> ALTER TABLEGROUP tablegroup_name ADD [TABLE] table_name;
  

  In the preceding syntax, table_name indicates the name of the table to be added to the table group. To add multiple tables, separate the table names with commas (,).

• ALTER TABLE statement

  obclient> ALTER TABLE table_name [SET] TABLEGROUP tablegroup_name;
  

Example:

1. Create a LIST COLUMNS-partitioned table group tg1_rc.

   obclient> CREATE TABLEGROUP tg1_lc PARTITION BY LIST COLUMNS 1
               ( PARTITION p0 VALUES IN(1,2),
                 PARTITION p1 VALUES IN(3,4),
                 PARTITION p2 VALUES IN(5,6)
               );
   Query OK, 0 rows affected
   

2. Create a LIST COLUMNS-partitioned table t1_lc.

   obclient> CREATE TABLE t1_lc (id INT NOT NULL,name varchar(50),t_date date NOT NULL)
               PARTITION BY LIST COLUMNS(id)
               ( PARTITION p0 VALUES IN(1,2),
                 PARTITION p1 VALUES IN(3,4),
                 PARTITION p2 VALUES IN(5,6)
               );
   Query OK, 0 rows affected
   

3. Add the table to the table group.

   obclient> ALTER TABLEGROUP tg1_lc ADD TABLE t1_lc;
   Query OK, 0 rows affected
   

   or

   obclient> ALTER TABLE t1_lc SET TABLEGROUP tg1_lc;
   Query OK, 0 rows affected
   

After adding a partitioned table to a partitioned table group, you can remove the table from the group, move the table to another 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.