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 with a global index to a table group whose SHARDING attribute is set to `NONE`, the system automatically binds the global index with 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 is described as follows:

  • 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 is described as follows:

  • 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];

The parameters in the syntax are described as follows:

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

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

• partition_option: 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 NUMBER) TABLEGROUP = tblgroup1;
     

  3. Create a partitioned table named tbl2 and add it to the tblgroup1 table group.

     CREATE TABLE tbl2(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup1
     PARTITION BY LIST(col1)
     (PARTITION p0 VALUES('01'),
     PARTITION p1 VALUES('02')
     );
     

  4. View tables in the table group.

     SELECT * FROM SYS.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'TBLGROUP1';
     

     The return result is as follows:

     +-----------------+-------+------------+----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
     +-----------------+-------+------------+----------+
     | TBLGROUP1       | SYS   | TBL1       | NONE     |
     | TBLGROUP1       | SYS   | 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 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup2
     PARTITION BY LIST(col1)
     (PARTITION p0 VALUES('01'),
     PARTITION p1 VALUES('02')
     );
     

  3. Create a table named tbl4 that has the same partitioning type, partition count, and partition value as the tbl3 table, and add it to the tblgroup2 table group.

     CREATE TABLE tbl4(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup2
     PARTITION BY LIST(col1)
     (PARTITION p0 VALUES('01'),
     PARTITION p1 VALUES('02')
     );
     

  4. View tables in the table group.

     SELECT * FROM SYS.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'TBLGROUP2';
     

     The return result is as follows:

     +-----------------+-------+------------+-----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING  |
     +-----------------+-------+------------+-----------+
     | TBLGROUP2       | SYS   | TBL3       | PARTITION |
     | TBLGROUP2       | SYS   | 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 by using a template and add it to the tblgroup3 table group.

     CREATE TABLE tbl5(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup3
     PARTITION BY LIST(col1)
     SUBPARTITION BY LIST(col2)
     SUBPARTITION TEMPLATE
     (SUBPARTITION mp0 VALUES('01'),
     SUBPARTITION mp1 VALUES('02'),
     SUBPARTITION mp2 VALUES('03')
     )
     (PARTITION p0 VALUES('01'),
     PARTITION p1 VALUES('02')
     );
     

  3. Create a table named tbl6 that has the same partitioning type, partition count, and partition value as the tbl5 table, and add it to the tblgroup3 table group.

     CREATE TABLE tbl6(col1 NUMBER,col2 VARCHAR2(50)) TABLEGROUP = tblgroup3
     PARTITION BY LIST(col1)
     SUBPARTITION BY LIST(col2)
     SUBPARTITION TEMPLATE
     (SUBPARTITION mp0 VALUES('01'),
     SUBPARTITION mp1 VALUES('02'),
     SUBPARTITION mp2 VALUES('03')
     )
     (PARTITION p0 VALUES('01'),
     PARTITION p1 VALUES('02')
     );
     

  4. View tables in the table group.

     SELECT * FROM SYS.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'TBLGROUP3';
     

     The return result is as follows:

     +-----------------+-------+------------+----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
     +-----------------+-------+------------+----------+
     | TBLGROUP3       | SYS   | TBL5       | ADAPTIVE |
     | TBLGROUP3       | SYS   | 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] [schema_name.]table_name [, [schema_name.]table_name ...];

The parameters in the syntax are described as follows:

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

• schema_name.: the schema to which the table belongs. This parameter is optional. If you do not specify schema_name, the system takes by default that the table to be added belongs to the current schema.

• table_name: 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 existing tables 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 template-based partitioned table named tbl8.

     CREATE TABLE tbl7 (col NUMBER);
     

     CREATE TABLE tbl8(col1 NUMBER,col2 VARCHAR2(50))
     PARTITION BY LIST(col1)
     SUBPARTITION BY LIST(col2)
     SUBPARTITION TEMPLATE
     (SUBPARTITION mp0 VALUES('01'),
     SUBPARTITION mp1 VALUES('02'),
     SUBPARTITION mp2 VALUES('03')
     )
     (PARTITION p0 VALUES('01'),
     PARTITION p1 VALUES('02')
     );
     

  3. Add the tbl7 and tbl8 tables to the tblgroup4 table group.

     ALTER TABLEGROUP tblgroup4 ADD tbl7,tbl8;
     

  4. View tables in the table group.

     SELECT * FROM SYS.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'TBLGROUP4';
     

     The return result is as follows:

     +-----------------+-------+------------+----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
     +-----------------+-------+------------+----------+
     | TBLGROUP4       | SYS   | TBL7       | NONE     |
     | TBLGROUP4       | SYS   | 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 NUMBER,col2 VARCHAR2(50))
     PARTITION BY LIST(col1)
     (PARTITION p0 VALUES('01'),
     PARTITION p1 VALUES('02')
     );
     

     CREATE TABLE tbl10(col1 NUMBER,col2 VARCHAR2(50))
     PARTITION BY LIST(col1)
     (PARTITION p0 VALUES('01'),
     PARTITION p1 VALUES('02')
     );
     

  3. Add the tbl9 and tbl10 tables to the tblgroup5 table group.

     ALTER TABLEGROUP tblgroup5 ADD tbl9,tbl10;
     

  4. View tables in the table group.

     SELECT * FROM SYS.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'TBLGROUP5';
     

     The return result is as follows:

     +-----------------+-------+------------+-----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING  |
     +-----------------+-------+------------+-----------+
     | TBLGROUP5       | SYS   | TBL9       | PARTITION |
     | TBLGROUP5       | SYS   | 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 NUMBER,col2 VARCHAR2(50))
     PARTITION BY LIST(col1)
     SUBPARTITION BY LIST(col2)
     SUBPARTITION TEMPLATE
     (SUBPARTITION mp0 VALUES('01'),
     SUBPARTITION mp1 VALUES('02'),
     SUBPARTITION mp2 VALUES('03')
     )
     (PARTITION p0 VALUES('01'),
     PARTITION p1 VALUES('02')
     );
     

     CREATE TABLE tbl12(col1 NUMBER,col2 VARCHAR2(50))
     PARTITION BY LIST(col1)
     SUBPARTITION BY LIST(col2)
     SUBPARTITION TEMPLATE
     (SUBPARTITION mp0 VALUES('01'),
     SUBPARTITION mp1 VALUES('02'),
     SUBPARTITION mp2 VALUES('03')
     )
     (PARTITION p0 VALUES('01'),
     PARTITION p1 VALUES('02')
     );
     

  3. Add the tbl11 and tbl12 tables to the tblgroup6 table group.

     ALTER TABLEGROUP tblgroup6 ADD tbl11,tbl12;
     

  4. View tables in the table group.

     SELECT * FROM SYS.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'TBLGROUP6';
     

     The return result is as follows:

     +-----------------+-------+------------+----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
     +-----------------+-------+------------+----------+
     | TBLGROUP6       | SYS   | TBL11      | ADAPTIVE |
     | TBLGROUP6       | SYS   | TBL12      | ADAPTIVE |
     +-----------------+-------+------------+----------+
     2 rows in set
     

References

• Overview

• 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