Create a table group

This topic describes how to create a table group by using an SQL statement in the Oracle mode of OceanBase Database.

Overview

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 logged on to an Oracle tenant of OceanBase Database. For more information about how to connect to a database, see Connection methods.

  Note

  You can query the oceanbase.DBA_OB_TENANTS view in the sys tenant to confirm the mode of the tenant to which you have logged on.

• You have the CREATE TABLEGROUP privilege. For more information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator. For more information, see Modify user privileges.

Create a table group

Limitations and suggestions on table group names

When you create a table group, you must name it. Observe the following limitations and suggestions on table group names:

• Each table group name must be unique within the same tenant.

• The table group name cannot exceed 64 characters in length.

• The table group name can contain only uppercase and lowercase letters, digits, and underscores (_), and must start with a letter or an underscore.

• The table group name cannot be a keyword of OceanBase Database.

• A meaningful name is suggested for a table group to facilitate future use.

Syntax

The syntax of CREATE TABLEGROUP 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.

Example 1

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:

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

   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

   In the Oracle mode of OceanBase Database, when you query a table group from the sys.DBA_OB_TABLEGROUP_TABLES view, the table group name specified in the query statement must be 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
   

Specify the SHARDING attribute for the table group

The SHARDING attribute of a table group can have the following values:

• NONE: There are no limitations on the tables added to a table group.
• PARTITION: The tables added to the table group must have the same first-level partitioning scheme as all the tables in the table group, including the partition type, number of partitions, and partition values.
• ADAPTIVE: It is the default value if the SHARDING attribute is not specified. The tables added to the table group must have the same first-level and second-level partitioning schemes as all the tables in the table group, including the partition type, number of partitions, and partition values.

The same partitioning scheme includes:

• The partitioning types are the same (for example, both are Hash+Range partitioning).
• If it is Hash partitioning, the number of referenced columns must be the same, and the number of partitions must be the same.
• If it is Range partitioning, the number of referenced columns must be the same, and the number of partitions and Range split points must be the same.

For more information about partitions, see Overview of partitions

Example 2

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;
   

Example 3

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:

   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;
   

References

• Overview of table groups
• CREATE TABLEGROUP
• Overview of partitions