Specify a table group when creating a table

This topic describes how to specify a table group when you create a data table in OceanBase Database in Oracle mode.

Syntax

You can specify a table group when creating a table. Syntax for the SQL statement:

CREATE TABLE table_name (column_name data_type [, column_name data_type] ) TABLEGROUP = tablegroup_name;

Parameters

Examples

The following example creates an order table and an order statement table. The two tables are generally joined for queries. Therefore, we recommend that you put the two tables into the same table group.

1. Create an order table named ordr.

   create table ordr (
     o_w_id int,
     o_d_id int,
     o_id int,
     o_c_id int,
     o_carrier_id int,
     o_ol_cnt int,
     o_all_local int,
     o_entry_d date,
     index iordr(o_w_id, o_d_id, o_c_id, o_id) local,
     primary key (o_w_id, o_d_id, o_id)
   ) tablegroup tpcc_group partition by hash(o_w_id) partitions 6;
   

2. Create an order details table named ordl.

   create table ordl (
       ol_w_id int, 
       ol_d_id int, 
       ol_o_id int, 
       ol_number int, 
       ol_delivery_d date,
       ol_amount decimal(6, 2) ,
       ol_i_id int ,
       ol_supply_w_id int ,
       ol_quantity int,
       ol_dist_info char(24),
       primary key (ol_w_id, ol_d_id, ol_o_id, ol_number)
   )tablegroup tpcc_group partition by hash(ol_w_id) partitions 6;
   

3. View the order table and order details table added to the tpcc_group table group.

   obclient> SHOW TABLEGROUPS;
   +-----------------+------------+---------------+
   | TABLEGROUP_NAME | TABLE_NAME | DATABASE_NAME |
   +-----------------+------------+---------------+
   | TPCC_GROUP      | ORDL       | SYS           |
   | TPCC_GROUP      | ORDR       | SYS           |
   +-----------------+------------+---------------+
   2 rows in set