Create a table group

This topic describes how to create a table group by using an SQL statement with some examples.

Overview

In OceanBase Database, 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.

For more information about table groups, see Table groups.

Prerequisites

Before you create a table group, make sure that:

• You have deployed an OceanBase cluster and created an Oracle tenant. For more information about how to deploy an OceanBase cluster, see Deployment overview.

• You have connected to the Oracle tenant of OceanBase Database. For more information about how to connect to OceanBase Database, see Overview of connection methods.

Limitations

• In OceanBase Database, the name of each table group must be unique within the same tenant.
• The table group name cannot exceed 64 characters.
• The table group name can only consist of uppercase and lowercase letters, numbers, and underscores, and must start with a letter or underscore.
• Avoid using reserved keywords as a table group name. For more information about the reserved keywords in OceanBase Database's Oracle mode, see Reserved keywords.

Considerations

• We recommend that you give the table group a meaningful and descriptive name that reflects its purpose and content as much as possible. 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

You can use the CREATE TABLEGROUP statement to create a table group.

The syntax for 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. Values of the SHARDING attribute are as follows:

  • NONE: No restrictions are imposed on the tables added to the table group.

  • PARTITION: The partition corresponding to the tables added to the table group must be consistent with the partitioning method of that for all tables in the table group, including the partition type, number of partitions, and partition values.

  • ADAPTIVE: If SHARDING is not specified, it is the default value. The partitions and sub-partitions corresponding to the tables added to the table group must be consistent with the partitioning method of those for all tables in the table group, including the partition type, number of partitions, and partition values.

  For more information about partitions, see Overview.

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. Then, an error occurs.

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

   The result is as follows:

   OBE-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. Then, an error occurs.

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

   The result is as follows:

   OBE-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

   In the Oracle mode of OceanBase Database, when you query table groups using the sys.DBA_OB_TABLEGROUP_TABLES view, write the English letters in the table group name in uppercase.

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

   The 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. Then, an error occurs.

   CREATE TABLE test_tbl3 (col NUMBER) TABLEGROUP = test_tbl_tg3;
   

   The result is as follows:

   OBE-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. For more information, see Add tables to a table group.

References

• For more information about how to view table groups, see Query information about a table group.
• For more information about how to modify tables in a table group, see Manage tables in a table group.
• For more information about how to drop a table group, see Drop a table group.