You can create a partitioned table or subpartitioned table of the appropriate type based on your business requirements.
Supported partitioning types
OceanBase AI Database supports the following partitioning types for a partition: RANGE, RANGE COLUMNS, LIST, LIST COLUMNS, HASH, and KEY.
For a subpartition in OceanBase AI Database, any two of the preceding partitioning types can be combined. The specific supported combinations are as follows:
Subpartition type |
Template-based subpartitioned table |
Non-template-based subpartitioned table |
|---|---|---|
| Range + Range / Range + Range Columns / Range + List / Range + List Columns / Range + Hash / Range + Key | Supported | Supported |
| Range Columns + Range / Range Columns + Range Columns / Range Columns + List / Range Columns + List Columns / Range Columns + Hash / Range Columns + Key | Supported | Supported |
| List + Range / List + Range Columns / List + List / List + List Columns / List + Hash / List + Key | Supported | Supported |
| List Columns + Range / List Columns + Range Columns / List Columns + List / List Columns + List Columns / List Columns + Hash / List Columns + Key | Supported | Supported |
| Hash + Range / Hash + Range Columns / Hash + List / Hash + List Columns / Hash + Hash / Hash + Key | Supported | Supported |
| Key + Range / Key + Range Columns / Key + List / Key + List Columns / Key + Hash /Key + Key | Supported | Supported |
Create a partitioned table with RANGE or RANGE COLUMNS partitions
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 creating a RANGE partition, note the following rules:
The result of the
exprexpression inPARTITION BY RANGE ( expr )must be an integer. If you want to create a RANGE partition based on a time column, you must convert the time type to a numeric value.Each partition has a
VALUES LESS THANclause that specifies a non-inclusive upper limit for the partition. Any value of the partitioning key that is equal to or greater than this value is mapped to the next partition.Except for the first partition, all partitions implicitly have a lower limit, which is the upper limit of the previous partition.
Only the upper limit of the last partition can be defined as
MAXVALUE. This value has no specific numeric meaning, is greater than the upper limits of all other partitions, and includes null values. If the last RANGE partition specifiesMAXVALUE, no new partitions can be added.
Parameters
Parameter |
Description |
|---|---|
| table_name | Specify the table name. |
| column_name | Specifies the column name. |
| column_type | Specifies the data type of a column. |
| partition_name | Specifies 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 affectedCreate a RANGE COLUMNS-partitioned table named
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 partitioned table with LIST or LIST COLUMNS partitions
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
- The result of the partitioning expression must be an integer.
- The partitioning expression can reference only one column, not multiple columns (i.e., a column vector).
When using LIST partitioning, follow these rules:
Parameters
Parameter |
Description |
|---|---|
| table_name | Specify the table name. |
| column_name | Specifies the column name. |
| column_type | Specifies the data type of a column. |
| partition_name | Specifies the partition name. |
| DEFAULT | Only the last partition is allowed to specify this value. This value has no specific numeric meaning, must be greater than the upper limit of all other partitions, and can also be null. |
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 affectedCreate 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 partitioned table with HASH or KEY partitions
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 | Specify the table name. |
| column_name | Specify the column name. |
| column_type | Specifies the data type of a column. |
| expr | Specify the hash partitioning expression. |
| column_name_list | Specifies the list of partitioning key column names. |
| partition_count | Specifies the number of partitions. |
Note
For a hash/key-partitioned table, if no partition name is specified during creation, the system assigns a name based on the naming rules. For a primary partitioned table, each partition is named p0, p1, ..., 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 affectedCreate 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 template-based subpartitioned table
Currently, six partitioning types are supported: HASH, RANGE, LIST, KEY, RANGE COLUMNS, and LIST COLUMNS. Subpartitions can be any combination of two of these partitioning types.
The SQL syntax for creating a template-based subpartitioned table 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
- The subpartitions under each partition of a template-based subpartitioned table follow the subpartition definition in the template, meaning the subpartition definition is identical across all partitions.
- For a template-based subpartitioned table, after defining the subpartitions, each subpartition is named according to the rule
($part_name)s($subpart_name). For example, for thetbl_rrtable below, the three subpartitions under partitionp0are namedp0smp1,p0smp2, andp0smp3.
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 some of the parameters:
Parameter |
Description |
|---|---|
| table_name | Specify the table name. |
| column_name | Specify the column name. |
| column_type | Specifies the column data type. |
| partition_name | Specifies the name of the partition. |
| subpartition_name | Specifies the name of the subpartition. |
| INT_VALUE | Specifies the number of subpartitions of the HASH or KEY type. |
Create a template-based subpartitioned table with RANGE COLUMNS + RANGE partitions
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 template-based subpartitioned table with RANGE COLUMNS + RANGE COLUMNS partitions
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 template-based subpartitioned table with RANGE COLUMNS + LIST COLUMNS partitions
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 template-based subpartitioned table with RANGE COLUMNS + HASH partitions
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 template-based subpartitioned table with RANGE COLUMNS + KEY partitions
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 template-based subpartitioned table with LIST + RANGE partitions
Examples:
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 template-based subpartitioned table with LIST + HASH partitions
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 template-based subpartitioned table with LIST + KEY partitions
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 template-based subpartitioned table with LIST COLUMNS + RANGE COLUMNS partitions
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 template-based subpartitioned table with LIST COLUMNS + LIST COLUMNS partitions
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 template-based subpartitioned table with LIST COLUMNS + HASH partitions
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 template-based subpartitioned table with LIST COLUMNS + KEY partitions
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 template-based subpartitioned table with HASH + RANGE COLUMNS partitions
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 template-based subpartitioned table with HASH + LIST COLUMNS partitions
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 template-based subpartitioned table with HASH + HASH partitions
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 template-based subpartitioned table with HASH + KEY partitions
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 template-based subpartitioned table with KEY + RANGE COLUMNS partitions
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 template-based subpartitioned table with KEY + LIST COLUMNS partitions
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 template-based subpartitioned table with KEY + KEY partitions
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 non-template-based subpartitioned table
Currently, six partitioning types are supported: HASH, RANGE, LIST, KEY, RANGE COLUMNS, and LIST COLUMNS. Subpartitions can be any combination of two partitioning types.
The SQL syntax for creating a non-template-based subpartitioned table 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 some of the parameters.
Parameter |
Description |
|---|---|
| table_name | Specify the table name. |
| column_name | Specify the column name. |
| column_type | Specifies the column data type. |
| partition_name | Specifies the name of the partition. |
| subpartition_name | Specifies the name of the subpartition. |
| INT_VALUE | Specifies the number of subpartitions of the HASH or KEY type. |
Create a non-template-based subpartitioned table with RANGE + RANGE partitions
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 non-template-based subpartitioned table with RANGE + LIST COLUMNS partitions
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 non-template-based subpartitioned table with RANGE COLUMNS + RANGE COLUMNS partitions
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 non-template-based subpartitioned table with RANGE COLUMNS + LIST COLUMNS partitions
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 non-template-based subpartitioned table with RANGE COLUMNS + HASH partitions
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 non-template-based subpartitioned table with RANGE COLUMNS + KEY partitions
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 non-template-based subpartitioned table with LIST + RANGE COLUMNS partitions
Examples:
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 non-template-based subpartitioned table with LIST + LIST COLUMNS partitions
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 non-template-based subpartitioned table with LIST COLUMNS + RANGE COLUMNS partitions
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 non-template-based subpartitioned table with LIST COLUMNS + LIST COLUMNS partitions
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 non-template-based subpartitioned table with LIST COLUMNS + HASH partitions
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 non-template-based subpartitioned table with LIST COLUMNS + KEY partitions
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 non-template-based subpartitioned table with HASH + RANGE COLUMNS partitions
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 non-template-based subpartitioned table with HASH + LIST COLUMNS partitions
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 non-template-based subpartitioned table with HASH + HASH partitions
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 non-template-based subpartitioned table with HASH + KEY partitions
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