This topic describes the method of creating a partitioned table and other related items.
Partitioning types
OceanBase Database in Oracle mode supports the following partitioning types:
RANGE partitioning
LIST partitioning
HASH partitioning
Create a RANGE-partitioned table
Syntax
CREATE TABLE table_name (column_name column_type[, column_name column_type])
PARTITION BY { RANGE (column_name) }
(PARTITION partition_name VALUES LESS THAN(expr)
[, PARTITION partition_name VALUES LESS THAN (expr )...]
[, PARTITION partition_name VALUES LESS THAN (MAXVALUE)]
);
Note
When you create a RANGE-partitioned table, comply with the following rules:
A
VALUES LESS THANclause 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
MAXVALUEliteral 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. IfMAXVALUEis 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 tbl1_log_r.
obclient> CREATE TABLE tbl1_log_r(log_id INT,log_date DATE NOT NULL DEFAULT SYSDATE)
PARTITION BY RANGE(log_date)
(PARTITION M202001 VALUES LESS THAN(TO_DATE('2020/02/01','YYYY/MM/DD'))
, PARTITION M202002 VALUES LESS THAN(TO_DATE('2020/03/01','YYYY/MM/DD'))
, PARTITION M202003 VALUES LESS THAN(TO_DATE('2020/04/01','YYYY/MM/DD'))
, PARTITION M202004 VALUES LESS THAN(TO_DATE('2020/05/01','YYYY/MM/DD'))
, PARTITION M202005 VALUES LESS THAN(TO_DATE('2020/06/01','YYYY/MM/DD'))
, PARTITION M202006 VALUES LESS THAN(TO_DATE('2020/07/01','YYYY/MM/DD'))
, PARTITION M202007 VALUES LESS THAN(TO_DATE('2020/08/01','YYYY/MM/DD'))
, PARTITION M202008 VALUES LESS THAN(TO_DATE('2020/09/01','YYYY/MM/DD'))
, PARTITION M202009 VALUES LESS THAN(TO_DATE('2020/10/01','YYYY/MM/DD'))
, PARTITION M202010 VALUES LESS THAN(TO_DATE('2020/11/01','YYYY/MM/DD'))
, PARTITION M202011 VALUES LESS THAN(TO_DATE('2020/12/01','YYYY/MM/DD'))
, PARTITION M202012 VALUES LESS THAN(TO_DATE('2021/01/01','YYYY/MM/DD'))
, PARTITION MMAX VALUES LESS THAN (MAXVALUE)
);
Query OK, 0 rows affected
Create a LIST-partitioned table
Syntax
CREATE TABLE table_name (column_name column_type[,column_name column_type])
PARTITION BY { LIST ( expr(column_name) | column_name )}
(PARTITION partition_name VALUES ( v01 [, v0N])
[,PARTITION partition_name VALUES ( vN1 [, vNN])]
[,PARTITION partition_name VALUES (DEFAULT)]
);
Note
In LIST partitioning, the partitioning expression can reference only one column instead of multiple columns (column vectors).
If
DEFAULTis specified for the last LIST partition, you cannot add more partitions.
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_log_l.
obclient> CREATE TABLE tbl1_log_l(log_id INT,log_value VARCHAR2(20))
PARTITION BY LIST(log_value)
(PARTITION P01 VALUES ('A'),
PARTITION P02 VALUES ( 'B' ),
PARTITION P03 VALUES ( 'C' )
);
Query OK, 0 rows affected
Create a HASH-partitioned table
Syntax
CREATE TABLE table_name (column_name column_type[,column_name column_type])
PARTITION BY HASH(expr) 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. |
| partition_count | The number of partitions. |
Note
For a HASH-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
What to do next
After creating a partition, you can add, drop, or truncate the partition. For information about partition management, see Manage a partitioned table.