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

Prerequisites and 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 HASH-partitioned table group tblgroup1.

   obclient> CREATE TABLEGROUP tblgroup1 LOCALITY = 'F,R{ALL_SERVER}@zone1' PARTITION BY HASH 1 PARTITIONS 10;
   Query OK, 0 rows affected
   

2. Create a HASH-partitioned table tbl1 and add it to the table group tblgroup1. The partitioned table has the same number of partitions as the table group.

   obclient> CREATE TABLE tbl1(col1 int, col2 int) TABLEGROUP = tblgroup1 LOCALITY = 'F,R{ALL_SERVER}@zone1' PARTITION BY HASH(col1) PARTITIONS10;
   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.

Example:

1. Create a HASH-partitioned table group tblgroup1.

   obclient> CREATE TABLEGROUP tblgroup1 LOCALITY = 'F,R{ALL_SERVER}@zone1' PARTITION BY HASH 1 PARTITIONS 10;
   Query OK, cted0 rows affected
   

2. Create a HASH-partitioned table tbl1.

   obclient> CREATE TABLE tbl1 (col1 INT PRIMARY KEY, col2 INT) LOCALITY = 'F,R{ALL_SERVER}@zone1' PARTITION BY HASH(col1) PARTITIONS 10;
   Query OK, 0 rows affected
   

3. Add the table to the table group.

   obclient> ALTER TABLEGROUP tblgroup1 ADD TABLE tbl1;
   

   or

   obclient>ALTER TABLE tbl1 SET TABLEGROUP tblgroup1;
   

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.