Add tables to a table group

After creating a table group, you can add tables that meet the criteria to the table group.

Limitations

During the upgrade of OceanBase Database, you are not allowed to use the ALTER TABLEGROUP statement to add tables to a table group.

Considerations

Tables with partition weights can be added to a table group. However, after being added, they may affect the overall distribution of the table group. The specific impact on the original table group after adding a table with partition weights is shown in the following table.

Prerequisites

Before adding a table to a table group, you need to check the attributes of the table group and the partition information of the tables within the table group to confirm whether the current table meets the conditions for joining the table group. For detailed operations on checking table group information, see View table group information.

When specifying the table group to which it belongs, the table to be created must meet the conditions for joining that table group.

• For a table group with SHARDING attribute set to NONE, there are no restrictions on the table to be created. A table group can contain non-partitioned tables, partitioned tables, and subpartitioned tables.

  Note

  When adding a table to a table group with the SHARDING = 'NONE' attribute, if the table has global indexes, the system automatically binds these global indexes to this table group to improve performance. For more information about global indexes, see Global indexes.

• For a table group with SHARDING attribute set to PARTITION, the partition of the table to be created must have the same partition definition as the partitions of all tables in the table group, including the partition type, number of partitions, and partition values. A table group can contain partitioned tables and subpartitioned tables. It is also supported for the table group to consist entirely of non-partitioned tables.

  Identical partition definitions specifically refer to:

  • The same partition type, for example, both are RANGE partitions.
  • If it is a HASH partition, the number of referenced columns and the number of partitions must be the same.
  • If it is a RANGE partition, the number of referenced columns, the number of partitions, and the RANGE split points must be the same.

• For a table group with SHARDING attribute set to ADAPTIVE, the partition and subpartition of the table to be created must have the same partition definition as the partition and subpartition of all tables in the table group, including the partition type, number of partitions, and partition values. The table group must consist entirely of partitioned tables or subpartitioned tables. It is also supported for the table group to consist entirely of non-partitioned tables.

  Identical partition definitions specifically refer to:

  • The same partition type (for example, both are HASH + RANGE partitions).
  • If it is a HASH partition, the number of referenced columns and the number of partitions must be the same.
  • If it is a RANGE partition, the number of referenced columns, the number of partitions, and the RANGE split points must be the same.
  • For subpartitions, the requirements are the same as those for partitions based on their partition type.

• For a table group with SHARDING attribute set to SUBPARTITION, the table to be added to the table group must be a subpartitioned table. The partitioning methods (key types, partition functions, numbers of partitions, partition boundary values, etc.) must be the same for different tables. Additionally, the subpartitioning methods must be identical.

  Note

  For V5.x versions, support for the SHARDING = 'SUBPARTITION' attribute of table groups started from V5.0.1.

Assign a table to a table group when creating the table

After creating a table group, if the table has not yet been created, you can assign it to the table group by specifying the table group when creating the table. The statement is as follows.

CREATE TABLE table_name column_definition TABLEGROUP = tablegroup_name [partition_option];

Statement usage:

• column_definition: Defines the column names and data types of a table.

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

• partition_option: Defines the partition information for the table.

The following example shows how to create tables and add them to a table group:

• Create a table and add it to a table group with SHARDING attribute set to NONE.

  1. Create a table group named tblgroup1 with its SHARDING attribute set to NONE.

     CREATE TABLEGROUP tblgroup1 SHARDING = 'NONE'; 
     

  2. Create a non-partitioned table named tbl1 and specify to add it to the tblgroup1 table group.

     CREATE TABLE tbl1 (col NUMBER) TABLEGROUP = tblgroup1;
     

  3. Create a partitioned table named tbl2 and specify to 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 the tables in the table group.

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

     The 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 SHARDING attribute set to PARTITION.

  1. Create a table group named tblgroup2 with its SHARDING attribute set to PARTITION.

     CREATE TABLEGROUP tblgroup2 SHARDING = 'PARTITION'; 
     

  2. Create a partitioned table named tbl3 and specify to 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 partition type, number of partitions, and partition values as table tbl3, and specify to 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 the tables in the table group.

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

     The result is as follows:

     +-----------------+-------+------------+-----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING  |
     +-----------------+-------+------------+-----------+
     | TBLGROUP2       | SYS   | TBL3       | PARTITION |
     | TBLGROUP2       | SYS   | TBL4       | PARTITION |
     +-----------------+-------+------------+-----------+
     2 rows in set
     

• Create tables and add them to a table group with the SHARDING attribute set to ADAPTIVE.

  1. Create a table group named tblgroup3 with the SHARDING attribute set to ADAPTIVE.

     CREATE TABLEGROUP tblgroup3 SHARDING = 'ADAPTIVE'; 
     

  2. Create a template-based secondary partitioned table named tbl5 and specify to 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 partition type, number of partitions, and partition values as the tbl5 table, and specify to 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 the tables in the table group.

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

     The result is as follows:

     +-----------------+-------+------------+----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
     +-----------------+-------+------------+----------+
     | TBLGROUP3       | SYS   | TBL5       | ADAPTIVE |
     | TBLGROUP3       | SYS   | TBL6       | ADAPTIVE |
     +-----------------+-------+------------+----------+
     2 rows in set
     

Add an existing table to a table group

After creating a table group, if a table already exists and meets the conditions for joining the table group, you can add the table to the table group using the ALTER TABLEGROUP statement. The statement is as follows.

ALTER TABLEGROUP tablegroup_name ADD [TABLE] table_name [, table_name...];

Instructions for using the statement:

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

• table_name: The table to be added. When adding multiple tables, separate the table names with commas (,).

  When adding multiple tables, table names can be repeated. If the table to be added is already in the current table group, the system does not report an error.

The following example shows how to add an existing table to a table group:

• Add an existing table to a table group whose SHARDING attribute is NONE.

  1. Create a table group named tblgroup4 with its SHARDING attribute set to NONE.

     CREATE TABLEGROUP tblgroup4 SHARDING = 'NONE'; 
     

  2. Create a non-partitioned table named tbl7 and a template 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 both tables tbl7 and tbl8 to the table group tblgroup4.

     ALTER TABLEGROUP tblgroup4 ADD tbl7,tbl8;
     

  4. View the tables in the table group.

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

     The result is as follows:

     +-----------------+-------+------------+----------+
     | TABLEGROUP_NAME | OWNER | TABLE_NAME | SHARDING |
     +-----------------+-------+------------+----------+
     | TBLGROUP4       | SYS   | TBL7       | NONE     |
     | TBLGROUP4       | SYS   | TBL8       | NONE     |
     +-----------------+-------+------------+----------+
     2 rows in set
     

• Add an existing table to a table group whose SHARDING attribute is PARTITION.

  1. Create a table group named tblgroup5 with its SHARDING attribute set to PARTITION.

     CREATE TABLEGROUP tblgroup5 SHARDING = 'PARTITION'; 
     

  2. Create two partitioned tables named tbl9 and tbl10 with 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 tables tbl9 and tbl10 to the table group tblgroup5.

     ALTER TABLEGROUP tblgroup5 ADD tbl9,tbl10;
     

  4. View the tables in the table group.

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

     The 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 SHARDING attribute set to ADAPTIVE.

  1. Create a table group named tblgroup6 with its SHARDING attribute set to ADAPTIVE.

     CREATE TABLEGROUP tblgroup6 SHARDING = 'ADAPTIVE'; 
     

  2. Create two template-based partitioned tables, tbl11 and tbl12, with identical partition definitions.

     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 tables tbl11 and tbl12 to the table group tblgroup6.

     ALTER TABLEGROUP tblgroup6 ADD tbl11,tbl12;
     

  4. View the tables in the table group.

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

     The result is as follows:

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

References

• About table groups

• Create a table group

• View table group information

• Modify the SHARDING attribute of a table group

• Manage tables within a table group

• Delete a table group