Create a subpartitioned table|V3.2.4|OceanBase Database| docs|Distributed Database

Create a subpartitioned table

Last Updated：2023-10-24 09:23:03 Updated

Subpartitioning is a technique that partitions a table in two dimensions. For example, it is frequently used in scenarios that involve bills.

Subpartitioning types

OceanBase Database in Oracle mode supports the HASH, RANGE, and LIST partitioning methods, and a combination of any two of them can be used for subpartitioning. The following table describes the support for subpartitioned table creation.

Subpartitioning type	Create a subpartitioned table by using a template	Create a subpartitioned table without using a template
RANGE-RANGE	Supported	Supported
RANGE-LIST	Supported	Supported
RANGE-HASH	Supported	Supported
LIST-RANGE	Supported	Supported
LIST-LIST	Supported	Supported
LIST-HASH	Supported	Supported
HASH-RANGE	Supported	Supported
HASH-LIST	Supported	Supported
HASH-HASH	Supported	Supported

Create a subpartitioned table

When you create a subpartitioned table, you can choose whether to use a template.

SQL syntax to create a subpartitioned table by using a template

CREATE TABLE [IF NOT EXISTS] table_name(column_option_list)
  [table_option_list] [partition_option_list];

column_option_list:
  column_name column_type [, column_name column_type]

table_option_list:
  table_option [table_option]

table_option:
   LOCALITY [=] locality_name
  | PRIMARY_ZONE [=] primary_zone_name

partition_option:
  PARTITION BY
    RANGE(column_name){subpartition_option} (range_partition_list)
   | LIST(expression){subpartition_option} (list_partition_list)
   | HASH(expression){subpartition_option} { (hash_partition_list)
                                             | PARTITIONS partition_count }

subpartition_option:
  SUBPARTITION BY
    RANGE(column_name) SUBPARTITION TEMPLATE(range_subpartition_list)
   | LIST(expression) SUBPARTITION TEMPLATE(list_subpartition_list)
   | HASH(expression) { SUBPARTITION TEMPLATE (hash_subpartition_list)
                       | SUBPARTITIONS subpartition_count }

range_partition_list:
  range_partition [, range_partition ...]

range_partition:
  PARTITION partition_name VALUES LESS THAN {(expression_list) | MAXVALUE}

range_subpartition_list:
  range_subpartition [, range_subpartition ...]

range_subpartition:
  SUBPARTITION subpartition_name VALUES LESS THAN {(expression_list) | MAXVALUE}

list_partition_list:
  list_partition [, list_partition ...]

list_partition:
  PARTITION partition_name VALUES {(expression_list) | DEFAULT}

list_subpartition_list:
  list_subpartition [, list_subpartition ...]

list_subpartition:
  SUBPARTITION subpartition_name VALUES {(expression_list) | DEFAULT}

hash_partition_list:
    hash_partition [, hash_partition ...]

hash_partition:
    PARTITION partition_name

hash_subpartition_list:
    hash_subpartition [, hash_subpartition ...]

hash_subpartition:
    SUBPARTITION subpartition_name

expression_list:
  expression [, expression ...]

column_name_list:
  column_name [, column_name ...]

partition_count | subpartition_count:
  INT_VALUE

Note

When you use a template to create a subpartitioned table, the template defines subpartitions under each partition. In this case, subpartitions under each partition share the same definition.

For a template-based subpartitioned table, the subpartitions are named in the ($part_name)s($subpart_name) format. For example, in the t_range_range table created by using the following statement, the partition p0 has three subpartitions: p0smp1, p0smp2, and p0smp3.

obclient> CREATE TABLE t_range_range(col1 INT,col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
       (SUBPARTITION mp1 VALUES LESS THAN(100),
        SUBPARTITION mp2 VALUES LESS THAN(200),
        SUBPARTITION mp3 VALUES LESS THAN(300)
        )
       (PARTITION p0 VALUES LESS THAN(2020),
        PARTITION p1 VALUES LESS THAN(2021),
        PARTITION p2 VALUES LESS THAN(2022)
        );
Query OK, 0 rows affected

SQL syntax to create a subpartitioned table without using a template

CREATE TABLE [IF NOT EXISTS] table_name(column_option_list)
  [table_option_list] [partition_option_list];

column_option_list:
  column_name column_type [, column_name column_type]

table_option_list:
  table_option [table_option]

table_option:
  LOCALITY [=] locality_name
  | PRIMARY_ZONE [=] primary_zone_name

partition_option_list:
  PARTITION BY
    RANGE(column_name){subpartition_option}
      { range_partition_option (subpartition_option_list)
        [, range_partition_option (subpartition_option_list) ...]
       }
   | LIST(expression){subpartition_option}
       { list_partition_option (subpartition_option_list)
        [, list_partition_option (subpartition_option_list) ...]
        }
   | HASH(expression) {subpartition_option}
      { hash_partition_option (subsubpartition_option_list)
       [, hash_partition_option (subsubpartition_option_list) ...]
       }

subpartition_option:
  SUBPARTITION BY { RANGE(column_name) | LIST(expression) | HASH(expression) }

subpartition_option_list:
  range_partition_option_list | list_partition_option_list | hash_partition_option_list

range_partition_option_list:
  range_partition_option [, range_partition_option ...]

list_partition_option_list:
  list_partition_option [, list_partition_option ...]

hash_partition_option_list:
  hash_partition_option [, hash_partition_option ...]

range_partition_option:
  SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr
  [,SUBPARTITION subpartition_name VALUES LESS THAN range_partition_expr ...]

list_partition_option:
  SUBPARTITION subpartition_name VALUES list_partition_expr
  [, SUBPARTITION subpartition_name VALUES list_partition_expr ...]

hash_partition_option_list:
  SUBPARTITION subpartition_name
  [, SUBPARTITION subpartition_name ...]

Note

For a non-template-based subpartitioned table, you can define the subpartitioning method for each partition. You can choose whether to use the same subpartitioning method for different partitions.

Parameters

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
locality_name	The distribution of replicas across the zones. For example, `F@zone1,F@zone2,F@zone3,R@zone4` specifies that full-featured replicas reside in `zone1`, `zone2`, and `zone3`, and a read-only replica resides in `zone4`.
primary_zone_name	The primary zone where the leader resides.
partition_name	The partition name.
subpartition_name	The subpartition name.
INT_VALUE	The number of HASH or KEY subpartitions.

Examples

Create a subpartitioned table by using a template

Create a RANGE-RANGE-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_rr(col1 INT,col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES LESS THAN(2020),
          SUBPARTITION mp1 VALUES LESS THAN(2021),
          SUBPARTITION mp2 VALUES LESS THAN(2022)
          )
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200)
          );
Query OK, 0 rows affected

Create a RANGE-LIST-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_rl(col1 INT,col2 VARCHAR2(50))
       PARTITION BY RANGE(col1)
       SUBPARTITION BY LIST(col2)
       SUBPARTITION TEMPLATE
         (SUBPARTITION mp0 VALUES('01'),
          SUBPARTITION mp1 VALUES('02'),
          SUBPARTITION mp2 VALUES('03')
          )
         (PARTITION p0 VALUES LESS THAN(100),
          PARTITION p1 VALUES LESS THAN(200)
          );
Query OK, 0 rows affected

Create a RANGE-HASH-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_rh(col1 INT,col2 VARCHAR2(50))
       PARTITION BY RANGE(col1)
       SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
        (PARTITION p0 VALUES LESS THAN(100),
         PARTITION p1 VALUES LESS THAN(200)
         );
Query OK, 0 rows affected

Create a LIST-RANGE-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_lr(col1 INT,col2 varchar2(50))
       PARTITION BY LIST(col2)
       SUBPARTITION BY RANGE(col1)
       SUBPARTITION TEMPLATE
        (SUBPARTITION mp0 VALUES LESS THAN(100),
         SUBPARTITION mp1 VALUES LESS THAN(200),
         SUBPARTITION mp2 VALUES LESS THAN(300)
         )
        (PARTITION p0 VALUES('01'),
         PARTITION p1 VALUES('02')
         );
Query OK, 0 rows affected

Create a LIST-LIST-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_ll(col1 INT,col2 varchar2(50))
       PARTITION BY LIST(col1)
       SUBPARTITION BY LIST(col2)
       SUBPARTITION TEMPLATE
       (SUBPARTITION mp0 VALUES('A'),
        SUBPARTITION mp1 VALUES('B'),
        SUBPARTITION mp2 VALUES('C')
        )
       (PARTITION p0 VALUES('01'),
        PARTITION p1 VALUES('02')
        );
Query OK, 0 rows affected

Create a LIST-HASH-subpartitioned table by using a template.

obclient> CREATE TABLE t2_m_lh(col1 INT,col2 VARCHAR2(50))
       PARTITION BY LIST(col1)
       SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
        (PARTITION p0 VALUES('01'),
         PARTITION p1 VALUES('02')
         );
Query OK, 0 rows affected

Create a HASH-RANGE-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_hr(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0 VALUES LESS THAN(100),
         SUBPARTITION sp1 VALUES LESS THAN(200),
         SUBPARTITION sp2 VALUES LESS THAN(300)
        )
        PARTITIONS 5;
Query OK, 0 rows affected

Create a HASH-LIST-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_hl(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY LIST(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0 VALUES(100),
         SUBPARTITION sp1 VALUES(200),
         SUBPARTITION sp2 VALUES(300)
         )
        PARTITIONS 5;
Query OK, 0 rows affected

Create a HASH-HASH-subpartitioned table by using a template.

obclient> CREATE TABLE tbl2_m_hh(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY HASH(col2)
        SUBPARTITIONS 3
        PARTITIONS 5;
Query OK, 0 rows affected

Create a subpartitioned table without using a template

Create a RANGE-RANGE-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_rr(col1 INT,col2 INT)
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(col2)
        (PARTITION p0 VALUES LESS THAN(100)
          (SUBPARTITION sp0 VALUES LESS THAN(2020),
           SUBPARTITION sp1 VALUES LESS THAN(2021)
          ),
         PARTITION p1 VALUES LESS THAN(200)
          (SUBPARTITION sp2 VALUES LESS THAN(2020),
           SUBPARTITION sp3 VALUES LESS THAN(2021),
           SUBPARTITION sp4 VALUES LESS THAN(2022)
           )
         );
Query OK, 0 rows affected

Create a RANGE-LIST-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_rl(col1 INT,col2 VARCHAR2(50))
       PARTITION BY RANGE(col1)
       SUBPARTITION BY LIST(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES('01'),
          SUBPARTITION sp1 VALUES('02')
          ),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp2 VALUES('01'),
          SUBPARTITION sp3 VALUES('02'),
          SUBPARTITION sp4 VALUES('03')
         )
        );
Query OK, 0 rows affected

Create a RANGE-HASH-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_rh(col1 INT,col2 VARCHAR2(50))
       PARTITION BY RANGE(col1)
       SUBPARTITION BY HASH(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0,
          SUBPARTITION sp1
          ),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp2,
          SUBPARTITION sp3,
          SUBPARTITION sp4
          )
        );
Query OK, 0 rows affected

Create a LIST-RANGE-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_lr(col1 INT,col2 VARCHAR2(50))
       PARTITION BY LIST(col2)
       SUBPARTITION BY RANGE(col1)
       (PARTITION p0 VALUES('01')
         (SUBPARTITION sp0 VALUES LESS THAN(100),
          SUBPARTITION sp1 VALUES LESS THAN(200)
          ),
        PARTITION p1 VALUES('02')
         (SUBPARTITION sp2 VALUES LESS THAN(100),
          SUBPARTITION sp3 VALUES LESS THAN(200),
          SUBPARTITION sp4 VALUES LESS THAN(300)
          )
        );
Query OK, 0 rows affected

Create a LIST-LIST-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_ll(col1 INT,col2 varchar2(50))
       PARTITION BY LIST(col1)
       SUBPARTITION BY LIST(col2)
       (PARTITION p0 VALUES ('01', '02')
         (SUBPARTITION sp0 VALUES ('A'),
          SUBPARTITION sp1 VALUES ('B'),
          SUBPARTITION sp2 VALUES ('C')
          )
,
        PARTITION p1 VALUES ('03', '04')
         (SUBPARTITION sp3 VALUES ('A'),
          SUBPARTITION sp4 VALUES ('B'),
          SUBPARTITION sp5 VALUES ('C')
          )
        );
Query OK, 0 rows affected

Create a LIST-HASH-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_lh(col1 INT,col2 VARCHAR2(50))
       PARTITION BY LIST(col1)
       SUBPARTITION BY HASH(col2)
       (PARTITION p0 VALUES('01')
         (SUBPARTITION sp0,
          SUBPARTITION sp1
          ),
        PARTITION p1 VALUES('02')
         (SUBPARTITION sp2,
          SUBPARTITION sp3,
          SUBPARTITION sp4
          )
        );
Query OK, 0 rows affected

Create a HASH-RANGE-subpartitioned table without using a template.

obclient> CREATE TABLE tbl2_f_hr(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY RANGE(col2)
        (PARTITION p0
         (SUBPARTITION sp0 VALUES LESS THAN(100),
          SUBPARTITION sp1 VALUES LESS THAN(200)),
        PARTITION p1
         (SUBPARTITION sp2 VALUES LESS THAN(100),
          SUBPARTITION sp3 VALUES LESS THAN(200)
         )
        );
Query OK, 0 rows affected

Create a HASH-LIST-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_hl(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY LIST(col2)
        (PARTITION p0
          (SUBPARTITION sp0 VALUES(1,3),
           SUBPARTITION sp1 VALUES(4,7)
          ),
         PARTITION p1
          (SUBPARTITION sp2 VALUES(1,3),
           SUBPARTITION sp3 VALUES(4,7)
           )
        );
Query OK, 0 rows affected

Create a HASH-HASH-subpartitioned table without using a template.

obclient> CREATE TABLE t2_f_hh(col1 INT,col2 INT,col3 INT)
       PARTITION BY HASH(col1)
       SUBPARTITION BY HASH(col2)
        (PARTITION p0
          (SUBPARTITION sp0,
           SUBPARTITION sp1
           ),
         PARTITION p1
          (SUBPARTITION sp2,
           SUBPARTITION sp3
           )
        );
Query OK, 0 rows affected