Create a table group

This topic describes the definition of table groups, as well as the prerequisites, limitations, considerations, and examples for creating a table group by using an SQL statement.

About table groups

In OceanBase Cloud, a table group is a logical concept that represents a collection of tables. By defining a table group, you can control the physical storage adjacency among a group of tables.

Prerequisites

Before you create a table group, make sure that:

• You have deployed an OceanBase cluster and created an Oracle-compatible tenant.

• You have connected to the Oracle-compatible tenant of OceanBase Cloud.

Limitations

• In OceanBase Cloud, the name of each table group must be unique within the same tenant.
• A table group name cannot exceed 64 characters in length.
• A table group name can contain only letters, digits, and underscores (_), and must start with a letter or an underscore.
• A table group name cannot contain reserved keywords of OceanBase Cloud.

Considerations

• We recommend that you give a table group a meaningful and descriptive name that reflects its purpose and content as much as possible to facilitate subsequent management and maintenance. For example, when naming a table group, consider factors such as business requirements, functional modules, or data types, and name the order table group, user table group, and product table group as order_tablegroup, user_tablegroup, and product_tablegroup.
• We recommend that you plan and design comprehensively before creating a table group, considering factors such as business requirements, data relationships, and query patterns to determine whether table grouping is necessary.
• We recommend that you place related tables in the same table group based on their association and query frequency, in order to improve query efficiency and data availability.
• We recommend that you appropriately authorize and manage privileges for table groups, ensuring that only authorized users can operate on table groups to maintain data security and integrity.

Create a table group by using a statement

Use the CREATE TABLEGROUP statement to create a table group.

The syntax of the CREATE TABLEGROUP statement is as follows:

CREATE TABLEGROUP tablegroup_name [tablegroup_option];

where

• tablegroup_name specifies the name of the table group.

• tablegroup_option specifies the SHARDING attribute of the table group, which is optional. If you do not specify the SHARDING attribute, an ADAPTIVE table group is created by default. Valid values of the SHARDING attribute are as follows:

  • NONE: No restrictions are imposed on the tables added to the table group.
  • PARTITION: The partitioning method of tables added to the table group must be the same as that of all tables in the table group in terms of the partitioning type, number of partitions, and partitioning key value.
  • ADAPTIVE: If SHARDING is not specified, it is the default value. The partitioning and subpartitioning methods of tables added to the table group must be the same as those of all tables in the table group in terms of the partitioning type, number of partitions, and partitioning key value.

Examples

Example 1: Create a table group without the SHARDING attribute

1. Create a table group named test_tbl_tg1 without the SHARDING attribute.

   CREATE TABLEGROUP test_tbl_tg1;
   

2. Create a HASH-RANGE-subpartitioned table named test_tbl1_hr and set TABLEGROUP to test_tbl_tg1.

   CREATE TABLE test_tbl1_hr(col1 NUMBER,col2 NUMBER) TABLEGROUP = test_tbl_tg1
   PARTITION BY HASH(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
       (SUBPARTITION p0 VALUES LESS THAN(100),
       SUBPARTITION p1 VALUES LESS THAN(200),
       SUBPARTITION p2 VALUES LESS THAN(300)
       )
   PARTITIONS 10;
   

3. Create a non-partitioned table named test_tbl1 and set TABLEGROUP to test_tbl_tg1. An error will be returned.

   CREATE TABLE test_tbl1 (col NUMBER,col2 NUMBER) TABLEGROUP = test_tbl_tg1;
   

   The return result is as follows:

   ORA-00600: internal error code, arguments: -4179, not all tables are non-partitioned or partitioned, add table to tablegroup not allowed
   

4. Create a HASH-partitioned table named test_tbl1_h and set TABLEGROUP to test_tbl_tg1. An error will be returned.

   CREATE TABLE test_tbl1_h(col1 NUMBER,col2 NUMBER) TABLEGROUP = test_tbl_tg1
     PARTITION BY HASH(col1) PARTITIONS 10;
   

   The return result is as follows:

   ORA-00600: internal error code, arguments: -4179, part level is not equal, add table to tablegroup not allowed
   

5. Query tables in the test_tbl_tg1 table group.

   Notice

   When you query the sys.DBA_OB_TABLEGROUP_TABLES view for tables in a table group in the Oracle compatible mode of OceanBase Cloud, the name of the table group must be in upper case.

   SELECT * FROM sys.DBA_OB_TABLEGROUP_TABLES WHERE tablegroup_name = 'TEST_TBL_TG1';
   

   The return result is as follows:

   +-----------------+-------+--------------+----------+
   | TABLEGROUP_NAME | OWNER | TABLE_NAME   | SHARDING |
   +-----------------+-------+--------------+----------+
   | TEST_TBL_TG1    | SYS   | TEST_TBL1_HR | ADAPTIVE |
   +-----------------+-------+--------------+----------+
   1 row in set
   

Example 2: Create a table group with the SHARDING attribute specified

Create a table group with the SHARDING attribute set to NONE

1. Create a table group named test_tbl_tg2 and set the SHARDING attribute to NONE.

   CREATE TABLEGROUP test_tbl_tg2 SHARDING = 'NONE';
   

2. Create a non-partitioned table named test_tbl2 and set TABLEGROUP to test_tbl_tg2.

   CREATE TABLE test_tbl2 (col NUMBER) TABLEGROUP = test_tbl_tg2;
   

3. Create a partitioned table named test_tbl2_h and set TABLEGROUP to test_tbl_tg2.

   CREATE TABLE test_tbl2_h(col1 NUMBER,col2 VARCHAR(50)) TABLEGROUP = test_tbl_tg2
     PARTITION BY HASH(col1) PARTITIONS 10;
   

Create a table group with the SHARDING attribute set to PARTITION

1. Create a table group named test_tbl_tg3 and set the SHARDING attribute to PARTITION.

   CREATE TABLEGROUP test_tbl_tg3 SHARDING = 'PARTITION';
   

2. Create a HASH-partitioned table named test_tbl3_h and set TABLEGROUP to test_tbl_tg3.

   CREATE TABLE test_tbl3_h(col1 NUMBER) TABLEGROUP = test_tbl_tg3
     PARTITION BY HASH(col1) PARTITIONS 10;
   

3. Create a non-partitioned table named test_tbl3 and set TABLEGROUP to test_tbl_tg3. An error will be returned.

   CREATE TABLE test_tbl3 (col NUMBER) TABLEGROUP = test_tbl_tg3;
   

   The return result is as follows:

   ORA-00600: internal error code, arguments: -4179, not all tables are non-partitioned or partitioned, add table to tablegroup not allowed
   

4. Create a HASH-RANGE-subpartitioned table named test_tbl3_hr and set TABLEGROUP to test_tbl_tg3.

   CREATE TABLE test_tbl3_hr(col1 NUMBER,col2 NUMBER) TABLEGROUP = test_tbl_tg3
   PARTITION BY HASH(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
       (SUBPARTITION p0 VALUES LESS THAN(100),
       SUBPARTITION p1 VALUES LESS THAN(200),
       SUBPARTITION p2 VALUES LESS THAN(300)
       )
   PARTITIONS 10;
   

What to do next

After you create a table group, you can add existing tables that meet specific conditions to the table group.