Create a partitioned table|V4.0.0|OceanBase Database| docs|Distributed Database

Create a partitioned table

Last Updated：2023-07-25 05:42:17 Updated

This topic describes the method of creating a partitioned table and other related items.

Overview

The RANGE, LIST, and HASH partitioning methods are supported for partitioned tables.

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 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.

Create a partitioned table named tbl1_log_r.

obclient> CREATE TABLE tbl1_log_r(log_id NUMBER,log_date DATE NOT NULL DEFAULT SYSDATE)
       PARTITION BY RANGE(log_date)
        (PARTITION M197001 VALUES LESS THAN(TO_DATE('1970/02/01','YYYY/MM/DD'))
         , PARTITION M197002 VALUES LESS THAN(TO_DATE('1970/03/01','YYYY/MM/DD'))
         , PARTITION M197003 VALUES LESS THAN(TO_DATE('1970/04/01','YYYY/MM/DD'))
         , PARTITION M197004 VALUES LESS THAN(TO_DATE('1970/05/01','YYYY/MM/DD'))
         , PARTITION M197005 VALUES LESS THAN(TO_DATE('1970/06/01','YYYY/MM/DD'))
         , PARTITION M197006 VALUES LESS THAN(TO_DATE('1970/07/01','YYYY/MM/DD'))
         , PARTITION M197007 VALUES LESS THAN(TO_DATE('1970/08/01','YYYY/MM/DD'))
         , PARTITION M197008 VALUES LESS THAN(TO_DATE('1970/09/01','YYYY/MM/DD'))
         , PARTITION M197009 VALUES LESS THAN(TO_DATE('1970/10/01','YYYY/MM/DD'))
         , PARTITION M197010 VALUES LESS THAN(TO_DATE('1970/11/01','YYYY/MM/DD'))
         , PARTITION M197011 VALUES LESS THAN(TO_DATE('1970/12/01','YYYY/MM/DD'))
         , PARTITION M197012 VALUES LESS THAN(TO_DATE('1971/01/01','YYYY/MM/DD'))
         , PARTITION MMAX VALUES LESS THAN (MAXVALUE)
         );
Query OK, 0 rows affected

View the tbl1_log_r partitioned table.

obclient> SHOW CREATE TABLE tbl1_log_r\G
*************************** 1. row ***************************
       TABLE: TBL1_LOG_R
CREATE TABLE: CREATE TABLE "TBL1_LOG_R" (
  "LOG_ID" NUMBER(38),
  "LOG_DATE" DATE DEFAULT sysdate NOT NULL ENABLE
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by range(log_date)
(partition M197001 values less than (TO_DATE('1970-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197002 values less than (TO_DATE('1970-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197003 values less than (TO_DATE('1970-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197004 values less than (TO_DATE('1970-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197005 values less than (TO_DATE('1970-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197006 values less than (TO_DATE('1970-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197007 values less than (TO_DATE('1970-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197008 values less than (TO_DATE('1970-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197009 values less than (TO_DATE('1970-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197010 values less than (TO_DATE('1970-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197011 values less than (TO_DATE('1970-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition M197012 values less than (TO_DATE('1971-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition MMAX values less than (MAXVALUE))
1 row in set

Insert data into the tbl1_log_r table.

obclient> INSERT INTO tbl1_log_r VALUES (1,date'1970-11-11');
Query OK, 1 row affected

View the partition into which data is inserted.

obclient> SELECT * FROM tbl1_log_r partition(M197011);
+--------+-----------+
| LOG_ID | LOG_DATE  |
+--------+-----------+
|      1 | 11-NOV-70 |
+--------+-----------+
1 row in set

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 DEFAULT is 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.
DEFAULTv	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.

Create a 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

View the tbl1_log_l partitioned table.

obclient> SHOW CREATE TABLE tbl1_log_l\G
*************************** 1. row ***************************
       TABLE: TBL1_LOG_L
CREATE TABLE: CREATE TABLE "TBL1_LOG_L" (
  "LOG_ID" NUMBER(38),
  "LOG_VALUE" VARCHAR2(20)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by list(log_value)
(partition P01 values  ('A'),
partition P02 values  ('B'),
partition P03 values  ('C'))
1 row in set

Insert data into the tbl1_log_l table.

obclient> INSERT INTO tbl1_log_l VALUES (1,'A');
Query OK, 1 row affected

View the partition into which data is inserted.

obclient> SELECT * FROM tbl1_log_l partition(P01);
+--------+-----------+
| LOG_ID | LOG_VALUE |
+--------+-----------+
|      1 | A         |
+--------+-----------+
1 row in set

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.

Examples

Create a HASH-partitioned table.

Create a 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

View the tbl1_h partitioned table.

obclient> SHOW CREATE TABLE tbl1_h\G
*************************** 1. row ***************************
       TABLE: TBL1_H
CREATE TABLE: CREATE TABLE "TBL1_H" (
  "COL1" NUMBER(38),
  "COL2" VARCHAR2(50)
) COMPRESS FOR ARCHIVE REPLICA_NUM = 1 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0
 partition by hash(col1)
(partition P0,
partition P1,
partition P2,
partition P3,
partition P4,
partition P5,
partition P6,
partition P7,
partition P8,
partition P9,
partition P10,
partition P11,
partition P12,
partition P13,
partition P14,
partition P15,
partition P16,
partition P17,
partition P18,
partition P19,
partition P20,
partition P21,
partition P22,
partition P23,
partition P24,
partition P25,
partition P26,
partition P27,
partition P28,
partition P29,
partition P30,
partition P31,
partition P32,
partition P33,
partition P34,
partition P35,
partition P36,
partition P37,
partition P38,
partition P39,
partition P40,
partition P41,
partition P42,
partition P43,
partition P44,
partition P45,
partition P46,
partition P47,
partition P48,
partition P49,
partition P50,
partition P51,
partition P52,
partition P53,
partition P54,
partition P55,
partition P56,
partition P57,
partition P58,
partition P59)
1 row in set

Insert data into the tbl1_h table.

obclient> INSERT INTO tbl1_h VALUES (1,'AA');
Query OK, 1 row affected

View the partition into which data is inserted.

obclient> SELECT * FROM tbl1_h partition(P1);
+------+------+
| COL1 | COL2 |
+------+------+
|    1 | AA   |
+------+------+
1 row in set

Subsequent operations

After creating a partition, you can add, drop, or truncate the partition. For information about partition management, see Manage a partitioned table.