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 connected to an Oracle tenant of OceanBase Database. For more information about how to connect to OceanBase 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 in.

Create a table group

Limitations and considerations on table group names

When you create a table group, you must specify a name for it. The limitations and considerations on table group names are as follows:

• 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 Database.

• We recommend that you use a meaningful table group name to avoid confusion in future use.

Syntax

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.

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. An error is 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 is 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

   In 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 return 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

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.

Two tables are considered using the same partitioning method when they meet the following conditions:

• Have the same partitioning type such as HASH-RANGE partitioning.
• Have the same number of referenced columns and the same number of partitions (for HASH-partitioned tables).
• Have the same number of referenced columns, the same number of partitions, and the same range definitions (for RANGE-partitioned tables).

For more information about partitions, see Overview.

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. An error is 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;
   

References

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