Create a partitioned table|V4.4.2|OceanBase Database|docs|OceanBase Documentations

You can create a partitioned or subpartitioned table as needed.

Partitioning types

OceanBase Database supports the RANGE, RANGE COLUMNS, LIST, LIST COLUMNS, HASH, and KEY partitioning types.

In OceanBase Database, a combination of any two partitioning types is supported as the subpartitioning type. The following table describes the supported subpartitioning types.

Subpartitioning type	Create a subpartitioned table by using a template	Create a subpartitioned table without using a template
RANGE-RANGE, RANGE-RANGE COLUMNS, RANGE-LIST, RANGE-LIST COLUMNS, RANGE-HASH, and RANGE-KEY	Supported	Supported
RANGE COLUMNS-RANGE, RANGE COLUMNS-RANGE COLUMNS, RANGE COLUMNS-LIST, RANGE COLUMNS-LIST COLUMNS, RANGE COLUMNS-HASH, and RANGE COLUMNS-KEY	Supported	Supported
LIST-RANGE, LIST-RANGE COLUMNS, LIST-LIST, LIST-LIST COLUMNS, LIST-HASH, and LIST-KEY	Supported	Supported
LIST COLUMNS-RANGE, LIST COLUMNS-RANGE COLUMNS, LIST COLUMNS-LIST, LIST COLUMNS-LIST COLUMNS, LIST COLUMNS-HASH, and LIST COLUMNS-KEY	Supported	Supported
HASH-RANGE, HASH-RANGE COLUMNS, HASH-LIST, HASH-LIST COLUMNS, HASH-HASH, and HASH-KEY	Supported	Supported
KEY-RANGE, KEY-RANGE COLUMNS, KEY-LIST, KEY-LIST COLUMNS, KEY-HASH, and KEY-KEY	Supported	Supported

Create a RANGE- or RANGE COLUMNS-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[, column_name column_type])
  PARTITION BY { RANGE (expr(column_name) | column_name) 
                | RANGE COLUMNS(column_name [,column_name])
                }
    (
     PARTITION partition_name VALUES LESS THAN(expr)
     [, PARTITION partition_name VALUES LESS THAN (expr )...]
     [, PARTITION partition_name VALUES LESS THAN (MAXVALUE)]
     );

When you create a RANGE-partitioned table, comply with the following rules:

In MySQL-compatible mode, the result of the expr expression in the PARTITION BY RANGE ( expr ) clause must be an integer. To use a timestamp column as the partitioning key for RANGE partitioning, you must convert the values of the timestamp type column into numeric values.
A VALUES LESS THAN clause must be specified for each partition. This clause specifies a non-inclusive upper bound for the partition. Values of the partitioning key equal to or higher than this upper bound are added to the next higher partition.
All partitions, except the first one, have an implicit lower bound, which is the upper bound of the previous partition.
A MAXVALUE literal can be defined only for the last partition. MAXVALUE represents a virtual infinite value that is always greater than other possible values for the partitioning key, including the NULL value. If MAXVALUE is specified for the last RANGE partition, you cannot add a new partition.

Parameters

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
partition_name	The partition name.

Examples

Create a RANGE-partitioned table named tbl1_log_r.

obclient> CREATE TABLE tbl1_r (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date TIMESTAMP NOT NULL)
       PARTITION BY RANGE(UNIX_TIMESTAMP(log_date)) 
        (PARTITION M202001 VALUES LESS THAN(UNIX_TIMESTAMP('2020/02/01'))
       , PARTITION M202002 VALUES LESS THAN(UNIX_TIMESTAMP('2020/03/01'))
       , PARTITION M202003 VALUES LESS THAN(UNIX_TIMESTAMP('2020/04/01'))
       , PARTITION M202004 VALUES LESS THAN(UNIX_TIMESTAMP('2020/05/01'))
       , PARTITION M202005 VALUES LESS THAN(UNIX_TIMESTAMP('2020/06/01'))
       , PARTITION M202006 VALUES LESS THAN(UNIX_TIMESTAMP('2020/07/01'))
       , PARTITION M202007 VALUES LESS THAN(UNIX_TIMESTAMP('2020/08/01'))
       , PARTITION M202008 VALUES LESS THAN(UNIX_TIMESTAMP('2020/09/01'))
       , PARTITION M202009 VALUES LESS THAN(UNIX_TIMESTAMP('2020/10/01'))
       , PARTITION M202010 VALUES LESS THAN(UNIX_TIMESTAMP('2020/11/01'))
       , PARTITION M202011 VALUES LESS THAN(UNIX_TIMESTAMP('2020/12/01'))
       , PARTITION M202012 VALUES LESS THAN(UNIX_TIMESTAMP('2021/01/01'))
        );
Query OK, 0 rows affected

Create a RANGE COLUMNS-partitioned table tbl1_log_rc.

obclient> CREATE TABLE tbl1_log_rc (log_id BIGINT NOT NULL,log_value VARCHAR(50),log_date DATE NOT NULL)
       PARTITION BY RANGE COLUMNS(log_date) 
        (PARTITION M202001 VALUES LESS THAN('2020/02/01')
       , PARTITION M202002 VALUES LESS THAN('2020/03/01')
       , PARTITION M202003 VALUES LESS THAN('2020/04/01')
       , PARTITION M202004 VALUES LESS THAN('2020/05/01')
       , PARTITION M202005 VALUES LESS THAN('2020/06/01')
       , PARTITION M202006 VALUES LESS THAN('2020/07/01')
       , PARTITION M202007 VALUES LESS THAN('2020/08/01')
       , PARTITION M202008 VALUES LESS THAN('2020/09/01')
       , PARTITION M202009 VALUES LESS THAN('2020/10/01')
       , PARTITION M202010 VALUES LESS THAN('2020/11/01')
       , PARTITION M202011 VALUES LESS THAN('2020/12/01')
       , PARTITION M202012 VALUES LESS THAN('2021/01/01')
       , PARTITION MMAX VALUES LESS THAN MAXVALUE
        );
Query OK, 0 rows affected

Create a LIST- or LIST COLUMNS-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[,column_name column_type])
  PARTITION BY { LIST ( expr(column_name) | column_name )
                | LIST COLUMNS ( column_name [,column_name])
                }
    (PARTITION partition_name VALUES IN ( v01 [, v0N])
     [,PARTITION partition_name VALUES IN ( vN1 [, vNN])]
     [,PARTITION partition_name VALUES IN (DEFAULT)]
    );

Notice

When you create a LIST-partitioned table, comply with the following rules:

The result of the partitioning expression must be an integer.
The partitioning expression can reference only one column, instead of a list of multiple columns (column vectors).

Parameters

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
partition_name	The partition name.
DEFAULT	Only the last partition can have this parameter, which is an indefinite value that is greater than the upper bound of any other partitions, including NULLs.

Examples

Create a LIST-partitioned table named tbl1_l.

obclient> CREATE TABLE tbl1_l (col1 BIGINT PRIMARY KEY,col2 VARCHAR(50))
       PARTITION BY LIST(col1) 
        (PARTITION p0 VALUES IN (1, 2, 3),
         PARTITION p1 VALUES IN (5, 6),
         PARTITION p2 VALUES IN (DEFAULT)
        );
Query OK, 0 rows affected

Create a LIST COLUMNS-partitioned table.

obclient> CREATE TABLE tbl1_lc (id INT,partition_id VARCHAR(2))
       PARTITION BY LIST COLUMNS(partition_id)
        (PARTITION p0 VALUES IN ('00','01'),
         PARTITION p1 VALUES IN ('02','03'),
         PARTITION p2 VALUES IN (DEFAULT)
        );
Query OK, 0 rows affected

Create a HASH- or KEY-partitioned table

Syntax

CREATE TABLE table_name (column_name column_type[,column_name column_type])
  PARTITION BY { HASH(expr) | KEY([column_name_list]) } 
    PARTITIONS partition_count;

Parameters

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
expr	The expression for HASH partitioning.
column_name_list	The column name list for KEY partitioning.
partition_count	The number of partitions.

Note

For a HASH- or KEY-partitioned table, if no partition names are specified when the table is created, the system generates the names based on the following naming conventions: p0, p1, ..., and pn.

Examples

Create a HASH-partitioned table named tbl1_h.

obclient> CREATE TABLE tbl1_h(col1 INT,col2 VARCHAR(50))
       PARTITION BY HASH(col1) PARTITIONS 60;
Query OK, 0 rows affected

Create a KEY-partitioned table named tbl1_k.

obclient> CREATE TABLE tbl1_k(id INT,gmt_create DATETIME,info VARCHAR(20))
       PARTITION BY KEY(id,gmt_create) PARTITIONS 10;
Query OK, 0 rows affected

Create a subpartitioned table by using a template

The MySQL-compatible mode of OceanBase Database supports the HASH, RANGE, LIST, KEY, RANGE COLUMNS, and LIST COLUMNS partitioning types, and a combination of any two partitioning types as the subpartitioning type.

The SQL syntax for creating a subpartitioned table by using a template is as follows:

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

column_option_list:
  column_name column_type [, column_name column_type]

partition_option_list:
PARTITION BY 
  RANGE {(expression) | COLUMNS (column_name_list)}{subpartition_option} (range_partition_list)
 | LIST {(expression) | COLUMNS (column_name_list)}{subpartition_option} (list_partition_list)
 | HASH(expression) {subpartition_option} { (hash_partition_list)
                                           | PARTITIONS partition_count }
 | KEY(column_name_list) {subpartition_option} { (key_partition_list)
                                                | PARTITIONS partition_count }

subpartition_option:
  SUBPARTITION BY 
     RANGE {(expression) | COLUMNS (column_name_list)} SUBPARTITION TEMPLATE (range_subpartition_list)
    | LIST {(expression) | COLUMNS (column_name_list)} SUBPARTITION TEMPLATE (list_subpartition_list)
    | HASH(expression) { SUBPARTITION TEMPLATE (hash_subpartition_list)
                       | SUBPARTITIONS subpartition_count}
    | KEY(column_name_list) { SUBPARTITION TEMPLATE (key_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 IN {(expression_list) | DEFAULT}

list_subpartition_list:
    list_subpartition [, list_subpartition ...]

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

hash_partition_list:
    hash_partition [, hash_partition ...]

key_partition_list:
    key_partition [, key_partition ...]

hash_partition | key_partition:
    PARTITION partition_name

hash_subpartition_list:
    hash_subpartition [, hash_subpartition ...]

key_subpartition_list:
    key_subpartition [, key_subpartition ...]

hash_subpartition | key_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.
- V4.4.2 supports defining a subpartitioning strategy for a specific partition that differs from the template when creating a template-based subpartitioned table. This allows you to use both template-based and custom subpartitioning. This feature is available starting with V4.4.2 BP2.
For a template-based subpartitioned table, after subpartitions are defined, the subpartitions are named in the ($part_name)s($subpart_name) format. For the tbl_rr table in the following example, the p0 partition has three subpartitions: p0smp1, p0smp2, and p0smp3.

obclient> CREATE TABLE tbl_rr(col1 INT,col2 INT,col3 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(100),
         PARTITION p1 VALUES LESS THAN(200),
         PARTITION p2 VALUES LESS THAN(300)
        );
Query OK, 0 rows affected

obclient> SELECT table_name,partition_name,subpartition_name FROM information_schema.partitions;
+------------+----------------+-------------------+
| table_name | partition_name | subpartition_name |
+------------+----------------+-------------------+
| tbl_rr     | p0             | p0smp1            |
| tbl_rr     | p0             | p0smp2            |
| tbl_rr     | p0             | p0smp3            |
| tbl_rr     | p1             | p1smp1            |
| tbl_rr     | p1             | p1smp2            |
| tbl_rr     | p1             | p1smp3            |
| tbl_rr     | p2             | p2smp1            |
| tbl_rr     | p2             | p2smp2            |
| tbl_rr     | p2             | p2smp3            |
+------------+----------------+-------------------+
9 rows in set

The following table describes the parameters.

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
partition_name	The partition name.
subpartition_name	The subpartition name.
INT_VALUE	The number of HASH or KEY subpartitions.

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

Here is an example:

obclient> CREATE TABLE t2_m_rcr (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY RANGE COLUMNS(col1)
SUBPARTITION BY RANGE(col3)
SUBPARTITION TEMPLATE 
(SUBPARTITION mp0 VALUES LESS THAN(1000),
 SUBPARTITION mp1 VALUES LESS THAN(2000),
 SUBPARTITION mp2 VALUES LESS THAN(3000)
)
(PARTITION p0 VALUES LESS THAN(100),
 PARTITION p1 VALUES LESS THAN(200),
 PARTITION p2 VALUES LESS THAN(300)
);

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

Here is an example:

obclient> CREATE TABLE t_m_rcrc(col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
       SUBPARTITION TEMPLATE 
        (SUBPARTITION mp0 VALUES LESS THAN(1000),
         SUBPARTITION mp1 VALUES LESS THAN(2000),
         SUBPARTITION mp2 VALUES LESS THAN(3000)
        )
        (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 by using a template

Here is an example:

obclient> CREATE TABLE t_m_rclc(col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE 
        (SUBPARTITION mp0 VALUES IN(1,3),
         SUBPARTITION mp1 VALUES IN(4,6),
         SUBPARTITION mp2 VALUES IN(7)
        )
        (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-HASH-subpartitioned table by using a template

Here is an example:

obclient> CREATE TABLE t2_m_rch(col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
        (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-KEY-subpartitioned table by using a template

Here is an example:

obclient> CREATE TABLE t2_m_rck(col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY KEY(col2) SUBPARTITIONS 3
        (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 LIST-RANGE-subpartitioned table by using a template

Here is an example:

obclient> CREATE TABLE t2_m_lr(col1 INT,col2 INT) 
       PARTITION BY LIST (col1)
       SUBPARTITION BY RANGE(col2)
       SUBPARTITION TEMPLATE 
        (SUBPARTITION mp0 VALUES LESS THAN(100),
         SUBPARTITION mp1 VALUES LESS THAN(200),
         SUBPARTITION mp2 VALUES LESS THAN(300)
        )
        (PARTITION p0 VALUES IN(1,3),
         PARTITION p1 VALUES IN(4,6),
         PARTITION p2 VALUES IN(7,9)
        );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_m_lh (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY LIST (col1)
SUBPARTITION BY HASH(col3) SUBPARTITIONS 3
(PARTITION p0 VALUES IN(100),
 PARTITION p1 VALUES IN(200),
 PARTITION p2 VALUES IN(300)
); 
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_m_lk (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY LIST(col1)
SUBPARTITION BY KEY(col3) SUBPARTITIONS 3
(PARTITION p0 VALUES IN(100),
 PARTITION p1 VALUES IN(200),
 PARTITION p2 VALUES IN(300)
);

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

Here is an example:

obclient> CREATE TABLE t2_m_lcrc(col1 INT,col2 INT) 
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
       SUBPARTITION TEMPLATE 
        (SUBPARTITION mp0 VALUES LESS THAN(100),
         SUBPARTITION mp1 VALUES LESS THAN(200),
         SUBPARTITION mp2 VALUES LESS THAN(300)
        )
        (PARTITION p0 VALUES IN(1,3),
         PARTITION p1 VALUES IN(4,6),
         PARTITION p2 VALUES IN(7,9)
        );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_m_lclc(col1 INT,col2 INT) 
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE 
        (SUBPARTITION mp0 VALUES IN(2),
         SUBPARTITION mp1 VALUES IN(5),
         SUBPARTITION mp2 VALUES IN(8)
        )
        (PARTITION p0 VALUES IN(1,3),
         PARTITION p1 VALUES IN(4,6),
         PARTITION p2 VALUES IN(7,9)
        );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_m_lch(col1 INT,col2 INT) 
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY HASH(col2) SUBPARTITIONS 5
        (PARTITION p0 VALUES IN(100),
         PARTITION p1 VALUES IN(200),
         PARTITION p2 VALUES IN(300)
        ); 
Query OK, 0 rows affected

Create a LIST COLUMNS-KEY-subpartitioned table by using a template

Here is an example:

obclient> CREATE TABLE t2_m_lck(col1 INT,col2 INT) 
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY KEY(col2)
        SUBPARTITIONS 3
        (PARTITION p0 VALUES IN(100),
         PARTITION p1 VALUES IN(200),
         PARTITION p2 VALUES IN(300)
        ); 
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE tbl2_m_hrc(col1 INT,col2 INT,col3 INT) 
PARTITION BY HASH(col1)
SUBPARTITION BY RANGE COLUMNS(col2)
SUBPARTITION TEMPLATE
(SUBPARTITION p0 VALUES LESS THAN(100),
 SUBPARTITION p1 VALUES LESS THAN(200),
 SUBPARTITION p2 VALUES LESS THAN(300)
)
PARTITIONS 5;

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

Here is an example:

obclient> CREATE TABLE tbl2_m_hlc(col1 INT,col2 INT,col3 INT) 
PARTITION BY HASH(col1)
SUBPARTITION BY LIST COLUMNS(col2)
SUBPARTITION TEMPLATE
(SUBPARTITION p0 VALUES IN(1,3),
 SUBPARTITION p1 VALUES IN(4,6),
 SUBPARTITION p2 VALUES IN(7,9)
)
PARTITIONS 5;

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

Here is an example:

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

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

Here is an example:

obclient> CREATE TABLE tbl2_m_hh(col1 INT,col2 INT,col3 INT) 
       PARTITION BY HASH(col1)
       SUBPARTITION BY KEY(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0,
         SUBPARTITION sp1,
         SUBPARTITION sp2
         )
        PARTITIONS 5;
Query OK, 0 rows affected

Create a KEY-RANGE COLUMNS-subpartitioned table by using a template

Here is an example:

obclient> CREATE TABLE tbl2_m_krc(col1 INT,col2 INT,col3 INT) 
       PARTITION BY KEY(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0 VALUES LESS THAN(100),
         SUBPARTITION sp1 VALUES LESS THAN(200),
         SUBPARTITION sp2 VALUES LESS THAN(300)
         )
        (PARTITION p0,
         PARTITION p1,
         PARTITION p2
         );
Query OK, 0 rows affected

Create a KEY-LIST COLUMNS-subpartitioned table by using a template

Here is an example:

obclient> CREATE TABLE tbl2_m_klc(col1 INT,col2 INT,col3 INT) 
       PARTITION BY KEY(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION p0 VALUES IN(1,3),
         SUBPARTITION p1 VALUES IN(4,6),
         SUBPARTITION p2 VALUES IN(7,9)
        )
       PARTITIONS 5;
Query OK, 0 rows affected

Create a KEY-KEY-subpartitioned table by using a template

Here is an example:

obclient> CREATE TABLE tbl2_m_kk(col1 INT,col2 INT,col3 INT) 
       PARTITION BY KEY(col1)
       SUBPARTITION BY KEY(col2)
       SUBPARTITION TEMPLATE
        (SUBPARTITION sp0,
         SUBPARTITION sp1,
         SUBPARTITION sp2
         )
        PARTITIONS 5;
Query OK, 0 rows affected

Create a template-based or user-defined subpartitioned table with RANGE+RANGE partitioning

Example:

obclient> CREATE TABLE st_f_rr (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)
            (SUBPARTITION p0sp0 VALUES LESS THAN(150),
             SUBPARTITION p0sp1 VALUES LESS THAN(300)),
        PARTITION p1 VALUES LESS THAN(2021),
        PARTITION p2 VALUES LESS THAN(2022)
            (SUBPARTITION p2sp0 VALUES LESS THAN(200),
             SUBPARTITION p2sp1 VALUES LESS THAN(400)),
        PARTITION p3 VALUES LESS THAN(2023)
        );

Create a subpartitioned table without using a template

The SQL syntax for creating a subpartitioned table without using a template is as follows:

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

column_option_list:
  column_name column_type [, column_name column_type]

partition_option_list:
PARTITION BY 
  RANGE {(expression) | COLUMNS (column_name_list)}{subpartition_option}
    { range_partition_option (subpartition_option_list)
       [, range_partition_option (subpartition_option_list)]...
     }
 | LIST {(expression) | COLUMNS (column_name_list)}{subpartition_option}
     { list_partition_option (subpartition_option_list)
       [, list_partition_option (subpartition_option_list)]...
      }
 | HASH(expression) {subpartition_option}
    { hash_partition_option (subpartition_option_list)
     [, hash_partition_option (subpartition_option_list)]...
     }
 | KEY(column_name_list) {subpartition_option}
     { key_partition_option (subpartition_option_list)
       [, key_partition_option (subpartition_option_list)]
      }

subpartition_option:
SUBPARTITION BY 
  RANGE {(expression) | COLUMNS (column_name_list)}
 | LIST {(expression) | COLUMNS (column_name_list)}
 | HASH (expression)
 | KEY(column_name_list)

subpartition_option_list:
    range_partition_option_list
  | list_partition_option_list
  | hash_partition_option_list
  | key_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 ]...

key_partition_option_list:
  key_partition_option [, key_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 IN list_partition_expr
    [, SUBPARTITION subpartition_name VALUES IN list_partition_expr]...

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

key_partition_option_list:
  SUBPARTITION subpartition_name
    [, SUBPARTITION subpartition_name]...

The following table describes the parameters.

Parameter	Description
table_name	The table name.
column_name	The column name.
column_type	The data type of the column.
partition_name	The partition name.
subpartition_name	The subpartition name.
INT_VALUE	The number of HASH or KEY subpartitions.

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

Here is an example:

obclient> CREATE TABLE t2_f_rr(col1 INT,col2 TIMESTAMP) 
       PARTITION BY RANGE(col1)
       SUBPARTITION BY RANGE(UNIX_TIMESTAMP(col2))
        (PARTITION p0 VALUES LESS THAN(100)
           (SUBPARTITION sp0 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01')),
            SUBPARTITION sp1 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01')),
            SUBPARTITION sp2 VALUES LESS THAN(UNIX_TIMESTAMP('2021/10/01')),
            SUBPARTITION sp3 VALUES LESS THAN(UNIX_TIMESTAMP('2022/01/01'))
           ),
         PARTITION p1 VALUES LESS THAN(200)
           (SUBPARTITION sp4 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01')),
            SUBPARTITION sp5 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01')),
            SUBPARTITION sp6 VALUES LESS THAN(UNIX_TIMESTAMP('2021/10/01')),
            SUBPARTITION sp7 VALUES LESS THAN(UNIX_TIMESTAMP('2022/01/01'))
            )
         );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_rlc (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY RANGE(col1)
SUBPARTITION BY LIST COLUMNS(col3)
(PARTITION p0 VALUES LESS THAN(100)
  (SUBPARTITION sp0 VALUES IN(1,3),
   SUBPARTITION sp1 VALUES IN(4,6),
   SUBPARTITION sp2 VALUES IN(7,9)),
 PARTITION p1 VALUES LESS THAN(200)
  (SUBPARTITION sp3 VALUES IN(1,3),
   SUBPARTITION sp4 VALUES IN(4,6),
   SUBPARTITION sp5 VALUES IN(7,9))
); 
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_rcrc (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY RANGE COLUMNS(col1)
SUBPARTITION BY RANGE COLUMNS(col3)
(PARTITION p0 VALUES LESS THAN(100)
  (SUBPARTITION sp0 VALUES LESS THAN(1000),
   SUBPARTITION sp1 VALUES LESS THAN(2000),
   SUBPARTITION sp2 VALUES LESS THAN(3000)),
 PARTITION p1 VALUES LESS THAN(200)
  (SUBPARTITION sp3 VALUES LESS THAN(1000),
   SUBPARTITION sp4 VALUES LESS THAN(2000),
   SUBPARTITION sp5 VALUES LESS THAN(3000)),
 PARTITION p2 VALUES LESS THAN(300)
  (SUBPARTITION sp6 VALUES LESS THAN(1000),
   SUBPARTITION sp7 VALUES LESS THAN(2000),
   SUBPARTITION sp8 VALUES LESS THAN(3000))
);
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_rclc (col1 INT,col2 INT) 
       PARTITION BY RANGE COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
       (PARTITION p0 VALUES LESS THAN(100)
         (SUBPARTITION sp0 VALUES IN(1,3),
          SUBPARTITION sp1 VALUES IN(4,6),
          SUBPARTITION sp2 VALUES IN(7,9)),
        PARTITION p1 VALUES LESS THAN(200)
         (SUBPARTITION sp3 VALUES IN(1,3),
          SUBPARTITION sp4 VALUES IN(4,6),
          SUBPARTITION sp5 VALUES IN(7,9))
       ); 
Query OK, 0 rows affected

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

Here is an example:

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

Create a RANGE COLUMNS-KEY-subpartitioned table without using a template

Here is an example:

obclient> CREATE TABLE t2_f_rck (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY RANGE COLUMNS(col1)
SUBPARTITION BY KEY(col3)
(PARTITION p0 VALUES LESS THAN(100)
  (SUBPARTITION sp0,
   SUBPARTITION sp1,
   SUBPARTITION sp2),
 PARTITION p1 VALUES LESS THAN(200)
  (SUBPARTITION sp3,
   SUBPARTITION sp4,
   SUBPARTITION sp5)
); 
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_lrc(col1 INT,col2 TIMESTAMP) 
       PARTITION BY LIST(col1)
       SUBPARTITION BY RANGE COLUMNS(UNIX_TIMESTAMP(col2))
        (PARTITION p0 VALUES IN(100)
           (SUBPARTITION sp0 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01')),
            SUBPARTITION sp1 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01')),
            SUBPARTITION sp2 VALUES LESS THAN(UNIX_TIMESTAMP('2021/10/01')),
            SUBPARTITION sp3 VALUES LESS THAN(UNIX_TIMESTAMP('2022/01/01'))
           ),
         PARTITION p1 VALUES IN(200)
           (SUBPARTITION sp4 VALUES LESS THAN(UNIX_TIMESTAMP('2021/04/01')),
            SUBPARTITION sp5 VALUES LESS THAN(UNIX_TIMESTAMP('2021/07/01')),
            SUBPARTITION sp6 VALUES LESS THAN(UNIX_TIMESTAMP('2021/10/01')),
            SUBPARTITION sp7 VALUES LESS THAN(UNIX_TIMESTAMP('2022/01/01'))
            )
         );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_llc(col1 INT,col2 TIMESTAMP) 
       PARTITION BY LIST(col1)
       SUBPARTITION BY LIST COLUMNS(UNIX_TIMESTAMP(col2))
        (PARTITION p0 VALUES IN(100)
           (SUBPARTITION sp0 VALUES IN(UNIX_TIMESTAMP('2021/04/01')),
            SUBPARTITION sp1 VALUES IN(UNIX_TIMESTAMP('2021/07/01')),
            SUBPARTITION sp2 VALUES IN(UNIX_TIMESTAMP('2021/10/01')),
            SUBPARTITION sp3 VALUES IN(UNIX_TIMESTAMP('2022/01/01'))
           ),
         PARTITION p1 VALUES IN(200)
           (SUBPARTITION sp4 VALUES IN(UNIX_TIMESTAMP('2021/04/01')),
            SUBPARTITION sp5 VALUES IN(UNIX_TIMESTAMP('2021/07/01')),
            SUBPARTITION sp6 VALUES IN(UNIX_TIMESTAMP('2021/10/01')),
            SUBPARTITION sp7 VALUES IN(UNIX_TIMESTAMP('2022/01/01'))
            )
         );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_lcrc(col1 INT,col2 INT) 
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY RANGE COLUMNS(col2)
        (PARTITION p0 VALUES IN(100)
           (SUBPARTITION sp0 VALUES LESS THAN(1000),
            SUBPARTITION sp1 VALUES LESS THAN(2000),
            SUBPARTITION sp2 VALUES LESS THAN(3000),
            SUBPARTITION sp3 VALUES LESS THAN(4000)
           ),
         PARTITION p1 VALUES IN(200)
           (SUBPARTITION sp4 VALUES LESS THAN(100),
            SUBPARTITION sp5 VALUES LESS THAN(200),
            SUBPARTITION sp6 VALUES LESS THAN(300),
            SUBPARTITION sp7 VALUES LESS THAN(400)
            )
         );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_lclc(col1 INT,col2 INT) 
       PARTITION BY LIST COLUMNS(col1)
       SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p0 VALUES IN(100)
           (SUBPARTITION sp0 VALUES IN(1000),
            SUBPARTITION sp1 VALUES IN(2000),
            SUBPARTITION sp2 VALUES IN(3000),
            SUBPARTITION sp3 VALUES IN(4000)
           ),
         PARTITION p1 VALUES IN(200)
           (SUBPARTITION sp4 VALUES IN(100),
            SUBPARTITION sp5 VALUES IN(200),
            SUBPARTITION sp6 VALUES IN(300),
            SUBPARTITION sp7 VALUES IN(400)
            )
         );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_lch (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY LIST COLUMNS(col1)
SUBPARTITION BY HASH(col3)
(PARTITION p0 VALUES IN(100)
  (SUBPARTITION sp0,
   SUBPARTITION sp1,
   SUBPARTITION sp2),
 PARTITION p1 VALUES IN(200)
  (SUBPARTITION sp3,
   SUBPARTITION sp4,
   SUBPARTITION sp5)
); 
Query OK, 0 rows affected

Create a LIST COLUMNS-KEY-subpartitioned table without using a template

Here is an example:

obclient> CREATE TABLE t2_f_lck (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY LIST COLUMNS(col1)
SUBPARTITION BY KEY(col3)
(PARTITION p0 VALUES IN(100)
  (SUBPARTITION sp0,
   SUBPARTITION sp1,
   SUBPARTITION sp2),
 PARTITION p1 VALUES IN(200)
  (SUBPARTITION sp3,
   SUBPARTITION sp4,
   SUBPARTITION sp5)
); 
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_hrc (col1 INT,col2 INT) 
       PARTITION BY HASH(col1) 
       SUBPARTITION BY RANGE COLUMNS(col2)
         (PARTITION p1
          (SUBPARTITION sp0 VALUES LESS THAN (2020)
          ,SUBPARTITION sp1 VALUES LESS THAN (2021)
          ,SUBPARTITION sp2 VALUES LESS THAN (2022)
          ,SUBPARTITION sp3 VALUES LESS THAN (2023)
          ),
          PARTITION p2
          (SUBPARTITION sp4 VALUES LESS THAN (2020)
          ,SUBPARTITION sp5 VALUES LESS THAN (2021)
          ,SUBPARTITION sp6 VALUES LESS THAN (2022)
          ,SUBPARTITION sp7 VALUES LESS THAN (2023)
          )
         );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_hlc (col1 INT,col2 INT) 
       PARTITION BY HASH(col1) 
       SUBPARTITION BY LIST COLUMNS(col2)
        (PARTITION p1
          (SUBPARTITION sp0 VALUES IN (2020)
          ,SUBPARTITION sp1 VALUES IN (2021)
          ,SUBPARTITION sp2 VALUES IN (2022)
          ,SUBPARTITION sp3 VALUES IN (2023)
           ),
         PARTITION p2
          (SUBPARTITION sp4 VALUES IN (2020)
          ,SUBPARTITION sp5 VALUES IN (2021)
          ,SUBPARTITION sp6 VALUES IN (2022)
          ,SUBPARTITION sp7 VALUES IN (2023)
           )
        );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_hh (col1 INT NOT NULL,col2 varchar(50),col3 INT NOT NULL) 
PARTITION BY HASH(col3) 
SUBPARTITION BY HASH(col1)
( PARTITION p1
     ( SUBPARTITION sp0
       , SUBPARTITION sp1
       , SUBPARTITION sp2
       , SUBPARTITION sp3
        ),
    PARTITION p2
     ( SUBPARTITION sp4
       , SUBPARTITION sp5
       , SUBPARTITION sp6
       , SUBPARTITION sp7
        )
  );
Query OK, 0 rows affected

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

Here is an example:

obclient> CREATE TABLE t2_f_hk (col1 INT,col2 INT) 
       PARTITION BY HASH(col1) 
       SUBPARTITION BY KEY(col2)
        (PARTITION p1
         (SUBPARTITION sp0
         ,SUBPARTITION sp1
         ,SUBPARTITION sp2
         ,SUBPARTITION sp3
         ),
         PARTITION p2
         (SUBPARTITION sp4
         ,SUBPARTITION sp5
         ,SUBPARTITION sp6
         ,SUBPARTITION sp7
         )
        );
Query OK, 0 rows affected