CREATE TABLEGROUP

Purpose

You can use this statement to create a table group.

In the current version of OceanBase Database, you can use non-template-based subpartitions when you create an unbound table group. That is, you can add multiple non-template-based subpartitions to the table group.

Syntax

CREATE TABLEGROUP tablegroup_name
                  [opt_tablegroup_option_list] [opt_tg_partition_option]

opt_tablegroup_option_list:
tablegroup_option [tablegroup_option]

tablegroup_option:
LOCALITY [=] locality_name

opt_tg_partition_option:
PARTITION BY
 HASH column_num [tg_subpartition_option] PARTITIONS int_num
| RANGE [COLUMNS] column_num [tg_subpartition_option] {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| LIST [COLUMNS] column_num [tg_subpartition_option] {PARTITION partition_name VALUES  list_partition_expr, ...}

tg_subpartition_option:
SUBPARTITION BY
RANGE [COLUMNS] column_num SUBPARTITION TEMPLATE {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
| HASH column_num [SUBPARTITIONS INTNUM]
| LIST [COLUMNS] column_num SUBPARTITION TEMPLATE {SUBPARTITION partition_name VALUES  list_partition_expr, ...}

Syntax for creating non-template-based subpartitions for a table group:

CREATE TABLEGROUP  <tablegroup_name> [opt_tablegroup_option_list] [opt_tg_partition_option]

opt_tablegroup_option_list:
 tablegroup_option [tablegroup_option]

tablegroup_option:
 LOCALITY [=] locality_name
 ...
opt_tg_partition_option:
 PARTITION BY
 | HASH column_num [tg_subpartition_option] PARTITIONS int_num
 | RANGE [COLUMNS] column_num [tg_subpartition_option] {PARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
 | LIST [COLUMNS] column_num [tg_subpartition_option] {PARTITION partition_name VALUES list_partition_expr, ...}

tg_subpartition_option:
 SUBPARTITION BY
 | RANGE [COLUMNS] column_num SUBPARTITION {SUBPARTITION partition_name VALUES LESS THAN range_partition_expr, ...}
 | LIST [COLUMNS] column_num SUBPARTITION  {SUBPARTITION partition_name VALUES  list_partition_expr, ...}
 | HASH [SUBPARTITIONS int_num]

Parameters

Examples

• Create a table group named tblgroup1, and two tables respectively named tbl1 and tbl2 for which tblgroup1 is specified for the TABLEGROUP attribute.

  obclient> CREATE TABLEGROUP tblgroup1;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1 (col1 int, col2 int ) tablegroup = tblgroup1;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2 (col1 int, col2 int ) tablegroup = tblgroup1;
  Query OK, 0 rows affected
  

• Create a HASH-partitioned table group named tblgroup2, and two HASH-partitioned tables respectively named tbl1 and tbl2 that have the same number of partitions.

  obclient> CREATE TABLEGROUP tblgroup2 LOCALITY = 'F,R{ALL_SERVER}@zone1' partition by hash partitions 10;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl1(col1 int, col2 int) TABLEGROUP = tblgroup2 LOCALITY = 'F,R{ALL_SERVER}@zone1' partition by hash(col1) partitions 10;
  Query OK, 0 rows affected
  
  obclient> CREATE TABLE tbl2(col1 int, col2 int) TABLEGROUP = tblgroup2 LOCALITY = 'F,R{ALL_SERVER}@zone1' partition by hash(col2) partitions 10;
  Query OK, 0 rows affected
  

Create a subpartitioned table group by using a template

• Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table group named tblgroup3.

  obclient> CREATE TABLEGROUP tblgroup3 PARTITION BY RANGE COLUMNS 1
  SUBPARTITION BY RANGE  COLUMNS 1
  SUBPARTITION TEMPLATE
  (
   SUBPARTITION rp1 VALUES LESS THAN (2019),
   SUBPARTITION rp2 VALUES LESS THAN (2020),
   SUBPARTITION rp3 VALUES LESS THAN (2021)
  )
  (
   PARTITION p0 VALUES LESS THAN (100),
   PARTITION p1 VALUES LESS THAN (200),
   PARTITION p2 VALUES LESS THAN (300)
  )
  ;
  Query OK, 0 rows affected
  

• Create a RANGE COLUMNS-LIST COLUMNS-subpartitioned table group named tblgroup4.

  obclient> CREATE TABLEGROUP tblgroup4 PARTITION BY RANGE COLUMNS 1
  SUBPARTITION BY LIST COLUMNS 1
  SUBPARTITION TEMPLATE
  (
   SUBPARTITION rp0 VALUES  ('00','01'),
   SUBPARTITION rp1 VALUES  ('02','03'),
   SUBPARTITION rp2 VALUES  ('04','05')
  )
  (
   PARTITION p0 VALUES LESS THAN (2019),
   PARTITION p1 VALUES LESS THAN (2020),
   PARTITION p2 VALUES LESS THAN (2021)
  );
  Query OK, 0 rows affected
  

Create a subpartitioned table group without using a template

• Create a RANGE COLUMNS-RANGE COLUMNS-subpartitioned table group named tblgroup6.

  obclient> CREATE TABLEGROUP tblgroup6 PARTITION BY RANGE COLUMNS 1
    SUBPARTITION BY RANGE COLUMNS 1
  (
  PARTITION p0 VALUES LESS THAN (100)
     (
     SUBPARTITION sp0 VALUES LESS THAN (2018),
     SUBPARTITION sp1 VALUES LESS THAN (2019),
     SUBPARTITION sp2 VALUES LESS THAN (2020),
     SUBPARTITION sp3 VALUES LESS THAN (2021)
     ),
  PARTITION p1 VALUES LESS THAN (200)
    (
    SUBPARTITION sp4 VALUES LESS THAN (2018),
    SUBPARTITION sp5 VALUES LESS THAN (2019),
    SUBPARTITION sp6 VALUES LESS THAN (2020),
    SUBPARTITION sp7 VALUES LESS THAN (2021)
    )
  );
  Query OK, 0 rows affected
  

• Create a RANGE COLUMNS-LIST COLUMNS-subpartitioned table group named tblgroup7.

  obclient> CREATE TABLEGROUP tb1 PARTITION BY RANGE COLUMNS 1
   SUBPARTITION BY LIST COLUMNS 1
  (
  PARTITION p0 VALUES LESS THAN (2019)
    (SUBPARTITION p0sp0 VALUES ('00', '03'),
     SUBPARTITION p0sp1 VALUES ('04', '06'),
     SUBPARTITION p0sp2 VALUES ('07', '09')
    ),
  PARTITION p1 VALUES LESS THAN ( 2020)
    (SUBPARTITION p1sp0 VALUES ('00', '03'),
     SUBPARTITION p1sp1 VALUES ('04', '06'),
     SUBPARTITION p1sp2 VALUES ('07', '09')
    ),
  PARTITION p2 VALUES LESS THAN (2021)
    (SUBPARTITION p2sp0 VALUES ('00', '03'),
     SUBPARTITION p2sp1 VALUES ('04', '06'),
     SUBPARTITION p2sp2 VALUES ('07', '09'))
   );
  Query OK, 0 rows affected
  

• Create a RANGE COLUMNS-HASH-subpartitioned table group named tblgroup8.

  obclient> CREATE TABLEGROUP tblgroup8  PARTITION BY RANGE COLUMNS 1
  SUBPARTITION BY HASH SUBPARTITIONS 1
  (
  PARTITION p0 VALUES LESS THAN (2020)
    (
     SUBPARTITION p0sp1,
     SUBPARTITION p0sp2
     ),
  PARTITION p1 VALUES LESS THAN (2021)
     (
      SUBPARTITION p1sp1,
      SUBPARTITION p1sp2,
      SUBPARTITION p1sp3
      )
  );
  Query OK, 0 rows affected