几个开源数据库对比 Oracle 分区表--分区表的创建和简单查询

原创张玉龙 2022-04-07

1938

分区表支持情况

分区类型	PGSQL 13.3	OG 2.1	OG 3.0	mysql 8.0	IvorySQL 1.2
Range	Y	Y	Y	Y	Y
List	Y	Y	Y	Y	Y
Hash	Y	Y	Y	Y	Y
Interval	N	仅时间	仅时间	-	N
RANGE-RANGE	Y	N	Y	-	Y
RANGE-LIST	Y	N	Y	-	Y
RANGE-HASH	Y	N	Y	-	Y
LIST-RANGE	Y	N	Y	-	Y
LIST-LIST	Y	N	Y	-	Y
LIST-HASH	Y	N	Y	-	Y
HASH-RANGE	Y	N	Y	-	Y
HASH-LIST	Y	N	Y	-	Y
HASH-HASH	Y	N	Y	-	Y

备注：

openGauss 2.1 不支持 LIST 的 DEFAULT 分区，openGauss 3.0 支持 DEFAULT 分区。
openGauss 的分区创建方式与 Oracle 相同，PostgreSQL 和 IvorySQL 均需要先创建主(父)表，再创建子表关联主(父)表。
openGauss 不支持 HASH 的 PARTITIONS 创建方式。
openGauss 2.1 不支持子分区 SUBPARTITION，openGauss 3.0 支持子分区 SUBPARTITION。
openGauss 3.0 对 Hash-Hash 表插入数据报错。
openGauss 只支持时间数据类型的间隔分区表，分区键支持的数据类型为：TIMESTAMP[§] [WITHOUT TIME ZONE]、TIMESTAMP[§] [WITH TIME ZONE]、DATE。

以下图形流程是在上面<分区表支持情况>表格的基础上对备注做进一步说明

分区表创建示例

范围分区

创建范围分区表

-- Oracle and openGauss
CREATE TABLE emp_range
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
  (PARTITION hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY')),
   PARTITION hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY')),
   PARTITION hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY')),
   PARTITION hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY')),
   PARTITION hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY')),
   PARTITION hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY')),
   PARTITION hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY')),
   PARTITION hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY')),
   PARTITION hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY')),
   PARTITION hiredate_max VALUES LESS THAN (MAXVALUE))
;

-- MySQL
CREATE TABLE emp_range
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     DATETIME
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY RANGE COLUMNS(hiredate)
  (PARTITION hiredate_1979 VALUES LESS THAN ('1980-01-01 00:00:00'),
   PARTITION hiredate_1980 VALUES LESS THAN ('1981-01-01 00:00:00'),
   PARTITION hiredate_1981 VALUES LESS THAN ('1982-01-01 00:00:00'),
   PARTITION hiredate_1982 VALUES LESS THAN ('1983-01-01 00:00:00'),
   PARTITION hiredate_1983 VALUES LESS THAN ('1984-01-01 00:00:00'),
   PARTITION hiredate_1984 VALUES LESS THAN ('1985-01-01 00:00:00'),
   PARTITION hiredate_1985 VALUES LESS THAN ('1986-01-01 00:00:00'),
   PARTITION hiredate_1986 VALUES LESS THAN ('1987-01-01 00:00:00'),
   PARTITION hiredate_1987 VALUES LESS THAN ('1988-01-01 00:00:00'),
   PARTITION hiredate_max VALUES LESS THAN (MAXVALUE))
;

-- PostgreSQL 创建主表
CREATE TABLE emp_range
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY RANGE (hiredate);

-- orafce 创建主表，利用 VARCHAR2 and DATE
CREATE TABLE emp_range
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMERIC(4,0)
    , hiredate     DATE
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY RANGE (hiredate);

-- IvorySQL 创建主表，利用 NUMBER and VARCHAR2 and DATE
CREATE TABLE emp_range
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate);

-- PostgreSQL and orafce and IvorySQL 创建子表
create table emp_range_hiredate_1979 PARTITION of emp_range FOR VALUES FROM ('1979-01-01 00:00:00+08') TO ('1980-01-01 00:00:00+08');
create table emp_range_hiredate_1980 PARTITION of emp_range FOR VALUES FROM ('1980-01-01 00:00:00+08') TO ('1981-01-01 00:00:00+08');
create table emp_range_hiredate_1981 PARTITION of emp_range FOR VALUES FROM ('1981-01-01 00:00:00+08') TO ('1982-01-01 00:00:00+08');
create table emp_range_hiredate_1982 PARTITION of emp_range FOR VALUES FROM ('1982-01-01 00:00:00+08') TO ('1983-01-01 00:00:00+08');
create table emp_range_hiredate_1983 PARTITION of emp_range FOR VALUES FROM ('1983-01-01 00:00:00+08') TO ('1984-01-01 00:00:00+08');
create table emp_range_hiredate_1974 PARTITION of emp_range FOR VALUES FROM ('1984-01-01 00:00:00+08') TO ('1985-01-01 00:00:00+08');
create table emp_range_hiredate_1985 PARTITION of emp_range FOR VALUES FROM ('1985-01-01 00:00:00+08') TO ('1986-01-01 00:00:00+08');
create table emp_range_hiredate_1986 PARTITION of emp_range FOR VALUES FROM ('1986-01-01 00:00:00+08') TO ('1987-01-01 00:00:00+08');
create table emp_range_hiredate_1987 PARTITION of emp_range FOR VALUES FROM ('1987-01-01 00:00:00+08') TO ('1988-01-01 00:00:00+08');
create table emp_range_hiredate_1988 PARTITION of  emp_range FOR VALUES FROM ('1988-01-01 00:00:00+08') TO ('1989-01-01 00:00:00+08');
create table emp_range_hiredate_default partition of emp_range default;

插入测试数据

insert into emp_range select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss and MySQL
SQL> select * from emp_range PARTITION (hiredate_1980);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

openGauss=# select * from emp_range PARTITION (hiredate_1980);
 empno | ename |  job  | mgr  |      hiredate       |  sal   | comm | deptno
-------+-------+-------+------+---------------------+--------+------+--------
  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 |      |     20
(1 row)

mysql> select * from emp_range PARTITION (hiredate_1980);
+-------+-------+-------+------+---------------------+--------+------+--------+
| empno | ename | job   | mgr  | hiredate            | sal    | comm | deptno |
+-------+-------+-------+------+---------------------+--------+------+--------+
|  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 | NULL |     20 |
+-------+-------+-------+------+---------------------+--------+------+--------+
1 row in set (0.00 sec)

-- PostgreSQL and orafce and IvorySQL
postgres=# select * from emp_range PARTITION (emp_range_hiredate_1980);
 emp_range_hiredate_1980 | ename  |    job    | mgr  |      hiredate       |   sal   |  comm   | deptno
-------------------------+--------+-----------+------+---------------------+---------+---------+--------
                    7369 | SMITH  | CLERK     | 7902 | 1980-12-17 00:00:00 |  800.00 |         |     20
                    7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30
                    7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30
                    7566 | JONES  | MANAGER   | 7839 | 1981-04-02 00:00:00 | 2975.00 |         |     20
                    7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30
                    7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 00:00:00 | 2850.00 |         |     30
                    7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 00:00:00 | 2450.00 |         |     10
                    7839 | KING   | PRESIDENT |      | 1981-11-17 00:00:00 | 5000.00 |         |     10
                    7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30
                    7900 | JAMES  | CLERK     | 7698 | 1981-12-03 00:00:00 |  950.00 |         |     30
                    7902 | FORD   | ANALYST   | 7566 | 1981-12-03 00:00:00 | 3000.00 |         |     20
                    7934 | MILLER | CLERK     | 7782 | 1982-01-23 00:00:00 | 1300.00 |         |     10
                    7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 00:00:00 | 3000.00 |         |     20
                    7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 00:00:00 | 1100.00 |         |     20
(14 rows)

postgres=# select * from emp_range_hiredate_1980;
 empno | ename |  job  | mgr  |      hiredate       |  sal   | comm | deptno
-------+-------+-------+------+---------------------+--------+------+--------
  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 |      |     20
(1 row)

问题：
- PostgreSQL and IvorySQL 不支持使用 select … PARTITION 语句查询单个分区里的数据。

列表分区

创建列表分区表

-- Oracle and openGauss
CREATE TABLE emp_list
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY LIST (deptno) 
  (PARTITION deptno_10 VALUES (10),
   PARTITION deptno_20 VALUES (20),
   PARTITION deptno_30 VALUES (30),
   PARTITION deptno_default VALUES (DEFAULT))
;

-- openGauss 2.1 不支持 LIST 的 DEFAULT 分区，openGauss 3.0 支持 DEFAULT 分区
ERROR:  Un-support feature
DETAIL:  The default list's partition is not supported currently.

-- MySQL
... ...

-- PostgreSQL 创建主表
CREATE TABLE emp_list
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY LIST (deptno);

-- orafce 创建主表，利用 VARCHAR2 and DATE
CREATE TABLE emp_list
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMERIC(4,0)
    , hiredate     DATE
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY LIST (deptno);

-- IvorySQL 创建主表，利用 NUMBER and VARCHAR2 and DATE
CREATE TABLE emp_list
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY LIST (deptno);

-- PostgreSQL and orafce and IvorySQL 创建子表
create table emp_list_deptno_10 partition of emp_list for values in (10);
create table emp_list_deptno_20 partition of emp_list for values in (20);
create table emp_list_deptno_30 partition of emp_list for values in (30);
create table emp_list_deptno_default partition of emp_list default;

插入测试数据

insert into emp_list select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss
select * from emp_list PARTITION (deptno_10);

-- PostgreSQL and orafce and IvorySQL
postgres=# select * from emp_list_deptno_10;
 empno | ename  |    job    | mgr  |  hiredate  |   sal   | comm | deptno
-------+--------+-----------+------+------------+---------+------+--------
  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 | 2450.00 |      |     10
  7839 | KING   | PRESIDENT |      | 1981-11-17 | 5000.00 |      |     10
  7934 | MILLER | CLERK     | 7782 | 1982-01-23 | 1300.00 |      |     10
(3 rows)

问题：
- openGauss 2.1 不支持 LIST 的 DEFAULT 分区，openGauss 3.0 支持 DEFAULT 分区。

哈希(散列)分区

创建哈希分区表

-- Oracle and openGauss
CREATE TABLE emp_hash
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY HASH (sal)
PARTITIONS 4
;

-- openGauss 不支持 HASH 的 PARTITIONS 创建方式，只能使用以下方式创建 HASH 分区表
ERROR:  syntax error at or near "PARTITIONS"

CREATE TABLE emp_hash
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY HASH (sal)
  (PARTITION emp_hash_1,
   PARTITION emp_hash_2,
   PARTITION emp_hash_3,
   PARTITION emp_hash_4)
;

-- MySQL
... ...

-- PostgreSQL 创建主表
CREATE TABLE emp_hash
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY HASH (sal);

-- orafce 创建主表，利用 VARCHAR2 and DATE
CREATE TABLE emp_hash
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMERIC(4,0)
    , hiredate     DATE
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY HASH (sal);

-- IvorySQL 创建主表，利用 NUMBER and VARCHAR2 and DATE
CREATE TABLE emp_hash
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY HASH (sal);

-- PostgreSQL and orafce and IvorySQL 创建子表
CREATE TABLE emp_hash_1 PARTITION of emp_hash FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE emp_hash_2 PARTITION of emp_hash FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE emp_hash_3 PARTITION of emp_hash FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE emp_hash_4 PARTITION of emp_hash FOR VALUES WITH (MODULUS 4, REMAINDER 3);

插入测试数据

insert into emp_hash select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss
SQL> select * from emp_hash2 PARTITION (emp_hash_1);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30

openGauss=# select * from emp_hash PARTITION (emp_hash_1);
 empno | ename  |   job    | mgr  |      hiredate       |   sal   |  comm   | deptno
-------+--------+----------+------+---------------------+---------+---------+--------
  7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00 |  300.00 |     30
  7521 | WARD   | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250.00 |  500.00 |     30
  7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00 | 1400.00 |     30
  7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00 |    0.00 |     30
(4 rows)

-- PostgreSQL and orafce and IvorySQL
postgres=# select * from emp_hash_1;
 empno | ename |   job   | mgr  |  hiredate  |   sal   | comm | deptno
-------+-------+---------+------+------------+---------+------+--------
  7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975.00 |      |     20
  7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 |      |     10
  7876 | ADAMS | CLERK   | 7788 | 1987-07-13 | 1100.00 |      |     20
  7900 | JAMES | CLERK   | 7698 | 1981-12-03 |  950.00 |      |     30
(4 rows)

问题：
- openGauss 不支持 HASH 的 PARTITIONS 创建方式。

间隔(INTERVAL)分区

创建数值范围的间隔分区表

-- Oracle
CREATE TABLE emp_interval
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (sal)
INTERVAL (1000)
(PARTITION sal_p1 VALUES LESS THAN (1000));

-- PostgreSQL and openGauss and IvorySQL 均不支持创建数值范围的间隔分区表

-- 当前只有一个分区
SQL> @tabpart emp_interval

TABLE_OWNER   TABLE_NAME            POS COM PARTITION_NAME    NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW  HIGH_VALUE_LENGTH COMPRESS LAST_ANA COMPRESS_FOR
------------- -------------- ---------- --- --------------- ---------- ------------------ --------------- ----------------- -------- -------- ------------
SYS           EMP_INTERVAL            1 NO  SAL_P1                                      0 1000                            4 DISABLED

-- 插入测试数据
SQL> insert into emp_interval select * from emp;

14 rows created.

-- 自动创建分区
SQL> @tabpart emp_interval

TABLE_OWNER   TABLE_NAME            POS COM PARTITION_NAME    NUM_ROWS SUBPARTITION_COUNT HIGH_VALUE_RAW  HIGH_VALUE_LENGTH COMPRESS LAST_ANA COMPRESS_FOR
------------- -------------- ---------- --- --------------- ---------- ------------------ --------------- ----------------- -------- -------- ------------
SYS           EMP_INTERVAL            1 NO  SAL_P1                                      0 1000                            4 DISABLED
SYS           EMP_INTERVAL            2 NO  SYS_P49                                     0 2000                            4 DISABLED
SYS           EMP_INTERVAL            3 NO  SYS_P50                                     0 3000                            4 DISABLED
SYS           EMP_INTERVAL            4 NO  SYS_P51                                     0 4000                            4 DISABLED
SYS           EMP_INTERVAL            5 NO  SYS_P52                                     0 6000                            4 DISABLED

创建时间范围的间隔分区表

-- Oracle 按年分区（关键字：NUMTOYMINTERVAL）
CREATE TABLE emp_interval_date_year
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
INTERVAL (NUMTOYMINTERVAL(1,'year'))
(PARTITION hiredate_p1 VALUES LESS THAN (TO_DATE('01-01-1979','DD-MM-YYYY')));

-- Oracle 按月分区（关键字：NUMTOYMINTERVAL）
CREATE TABLE emp_interval_date_month
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
INTERVAL (NUMTOYMINTERVAL(1,'month'))
(PARTITION hiredate_p1 VALUES LESS THAN (TO_DATE('01-01-1979','DD-MM-YYYY')));

-- Oracle 按天分区（关键字：NUMTODSINTERVAL）
CREATE TABLE emp_interval_date_day
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
INTERVAL (NUMTODSINTERVAL(1,'day'))
(PARTITION hiredate_p1 VALUES LESS THAN (TO_DATE('01-01-1979','DD-MM-YYYY')));

-- openGauss 按年分区('1 year')
CREATE TABLE emp_interval_date_year
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
INTERVAL ('1 year')
(PARTITION hiredate_p1 VALUES LESS THAN (TO_DATE('01-01-1979','DD-MM-YYYY')));

-- openGauss 按月分区('1 month')
CREATE TABLE emp_interval_date_month
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
INTERVAL ('1 month')
(PARTITION hiredate_p1 VALUES LESS THAN (TO_DATE('01-01-1979','DD-MM-YYYY')));

-- openGauss 按天分区('1 day')
CREATE TABLE emp_interval_date_day
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
INTERVAL ('1 day')
(PARTITION hiredate_p1 VALUES LESS THAN (TO_DATE('01-01-1979','DD-MM-YYYY')));

问题：
- PostgreSQL and IvorySQL 不支持间隔分区表
- openGauss 只支持时间数据类型的间隔分区表，分区键支持的数据类型为：TIMESTAMP[§] [WITHOUT TIME ZONE]、TIMESTAMP[§] [WITH TIME ZONE]、DATE。

范围 + 范围(Range-Range)的复合分区

创建范围+范围的复合分区表

-- Oracle and openGauss 3.0
CREATE TABLE emp_range_range
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
SUBPARTITION BY RANGE (sal)
  (PARTITION hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1979_min  VALUES LESS THAN (1000),
     SUBPARTITION hiredate_1979_1000 VALUES LESS THAN (2000),
     SUBPARTITION hiredate_1979_2000 VALUES LESS THAN (3000),
     SUBPARTITION hiredate_1979_3000 VALUES LESS THAN (4000),
     SUBPARTITION hiredate_1979_4000 VALUES LESS THAN (5000),
     SUBPARTITION hiredate_1979_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1980_min  VALUES LESS THAN (1000),
     SUBPARTITION hiredate_1980_1000 VALUES LESS THAN (2000),
     SUBPARTITION hiredate_1980_2000 VALUES LESS THAN (3000),
     SUBPARTITION hiredate_1980_3000 VALUES LESS THAN (4000),
     SUBPARTITION hiredate_1980_4000 VALUES LESS THAN (5000),
     SUBPARTITION hiredate_1980_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1981_min  VALUES LESS THAN (1000),
     SUBPARTITION hiredate_1981_1000 VALUES LESS THAN (2000),
     SUBPARTITION hiredate_1981_2000 VALUES LESS THAN (3000),
     SUBPARTITION hiredate_1981_3000 VALUES LESS THAN (4000),
     SUBPARTITION hiredate_1981_4000 VALUES LESS THAN (5000),
     SUBPARTITION hiredate_1981_max  VALUES LESS THAN (MAXVALUE)),   
   PARTITION hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1982_min  VALUES LESS THAN (1000),
     SUBPARTITION hiredate_1982_1000 VALUES LESS THAN (2000),
     SUBPARTITION hiredate_1982_2000 VALUES LESS THAN (3000),
     SUBPARTITION hiredate_1982_3000 VALUES LESS THAN (4000),
     SUBPARTITION hiredate_1982_4000 VALUES LESS THAN (5000),
     SUBPARTITION hiredate_1982_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1983_min  VALUES LESS THAN (1000),
     SUBPARTITION hiredate_1983_1000 VALUES LESS THAN (2000),
     SUBPARTITION hiredate_1983_2000 VALUES LESS THAN (3000),
     SUBPARTITION hiredate_1983_3000 VALUES LESS THAN (4000),
     SUBPARTITION hiredate_1983_4000 VALUES LESS THAN (5000),
     SUBPARTITION hiredate_1983_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1984_min  VALUES LESS THAN (1000),
     SUBPARTITION hiredate_1984_1000 VALUES LESS THAN (2000),
     SUBPARTITION hiredate_1984_2000 VALUES LESS THAN (3000),
     SUBPARTITION hiredate_1984_3000 VALUES LESS THAN (4000),
     SUBPARTITION hiredate_1984_4000 VALUES LESS THAN (5000),
     SUBPARTITION hiredate_1984_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1985_min  VALUES LESS THAN (1000),
     SUBPARTITION hiredate_1985_1000 VALUES LESS THAN (2000),
     SUBPARTITION hiredate_1985_2000 VALUES LESS THAN (3000),
     SUBPARTITION hiredate_1985_3000 VALUES LESS THAN (4000),
     SUBPARTITION hiredate_1985_4000 VALUES LESS THAN (5000),
     SUBPARTITION hiredate_1985_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1986_min  VALUES LESS THAN (1000),
     SUBPARTITION hiredate_1986_1000 VALUES LESS THAN (2000),
     SUBPARTITION hiredate_1986_2000 VALUES LESS THAN (3000),
     SUBPARTITION hiredate_1986_3000 VALUES LESS THAN (4000),
     SUBPARTITION hiredate_1986_4000 VALUES LESS THAN (5000),
     SUBPARTITION hiredate_1986_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1987_min  VALUES LESS THAN (1000),
     SUBPARTITION hiredate_1987_1000 VALUES LESS THAN (2000),
     SUBPARTITION hiredate_1987_2000 VALUES LESS THAN (3000),
     SUBPARTITION hiredate_1987_3000 VALUES LESS THAN (4000),
     SUBPARTITION hiredate_1987_4000 VALUES LESS THAN (5000),
     SUBPARTITION hiredate_1987_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hiredate_max VALUES LESS THAN (MAXVALUE)
    (SUBPARTITION hiredate_max_min  VALUES LESS THAN (1000),
     SUBPARTITION hiredate_max_1000 VALUES LESS THAN (2000),
     SUBPARTITION hiredate_max_2000 VALUES LESS THAN (3000),
     SUBPARTITION hiredate_max_3000 VALUES LESS THAN (4000),
     SUBPARTITION hiredate_max_4000 VALUES LESS THAN (5000),
     SUBPARTITION hiredate_max_max  VALUES LESS THAN (MAXVALUE)))
;

-- openGauss 2.1 不支持子分区 SUBPARTITION，openGauss 3.0 支持子分区 SUBPARTITION
ERROR:  syntax error at or near "SUBPARTITION"
LINE 12: SUBPARTITION BY RANGE (sal)

-- MogDB 2.1 设置 enable_poc_feature=true 可以支持创建子分区 SUBPARTITION，但是这个参数默认是 off ，更改需要重启数据库生效。
ERROR:  do not support to create subpartition
DETAIL:  set 'enable_poc_feature=true' to enable create subpartition

openGauss=# set enable_poc_feature=true;
ERROR:  parameter "enable_poc_feature" cannot be changed without restarting the server

-- MySQL
... ...

-- PostgreSQL and orafce and IvorySQL 创建主表
-- IvorySQL 支持 NUMBER and VARCHAR2，orafce 支持 VARCHAR2
-- 如果需要相关字段请自行替换，本实验将 NUMBER --> NUMERIC，VARCHAR2 --> VARCHAR，DATE --> TIMESTAMP
CREATE TABLE emp_range_range
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY RANGE (hiredate);

-- PostgreSQL and orafce and IvorySQL 创建二级分区主表
CREATE TABLE emp_range_range_1979
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY RANGE (sal);

CREATE TABLE emp_range_range_1980    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (sal);
CREATE TABLE emp_range_range_1981    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (sal);
CREATE TABLE emp_range_range_1982    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (sal);
CREATE TABLE emp_range_range_1983    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (sal);
CREATE TABLE emp_range_range_1984    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (sal);
CREATE TABLE emp_range_range_1985    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (sal);
CREATE TABLE emp_range_range_1986    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (sal);
CREATE TABLE emp_range_range_1987    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (sal);
CREATE TABLE emp_range_range_1988    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (sal);
CREATE TABLE emp_range_range_default (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (sal);

-- 创建二级分区子表
create table emp_range_range_1979_min  PARTITION of emp_range_range_1979 FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_1979_1000 PARTITION of emp_range_range_1979 FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_1979_2000 PARTITION of emp_range_range_1979 FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_1979_3000 PARTITION of emp_range_range_1979 FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_1979_4000 PARTITION of emp_range_range_1979 FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_1979_max  PARTITION of emp_range_range_1979 FOR VALUES FROM (5000) TO (MAXVALUE);

create table emp_range_range_1980_min  PARTITION of emp_range_range_1980 FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_1980_1000 PARTITION of emp_range_range_1980 FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_1980_2000 PARTITION of emp_range_range_1980 FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_1980_3000 PARTITION of emp_range_range_1980 FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_1980_4000 PARTITION of emp_range_range_1980 FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_1980_max  PARTITION of emp_range_range_1980 FOR VALUES FROM (5000) TO (MAXVALUE);

create table emp_range_range_1981_min  PARTITION of emp_range_range_1981 FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_1981_1000 PARTITION of emp_range_range_1981 FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_1981_2000 PARTITION of emp_range_range_1981 FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_1981_3000 PARTITION of emp_range_range_1981 FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_1981_4000 PARTITION of emp_range_range_1981 FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_1981_max  PARTITION of emp_range_range_1981 FOR VALUES FROM (5000) TO (MAXVALUE);

create table emp_range_range_1982_min  PARTITION of emp_range_range_1982 FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_1982_1000 PARTITION of emp_range_range_1982 FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_1982_2000 PARTITION of emp_range_range_1982 FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_1982_3000 PARTITION of emp_range_range_1982 FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_1982_4000 PARTITION of emp_range_range_1982 FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_1982_max  PARTITION of emp_range_range_1982 FOR VALUES FROM (5000) TO (MAXVALUE);

create table emp_range_range_1983_min  PARTITION of emp_range_range_1983 FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_1983_1000 PARTITION of emp_range_range_1983 FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_1983_2000 PARTITION of emp_range_range_1983 FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_1983_3000 PARTITION of emp_range_range_1983 FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_1983_4000 PARTITION of emp_range_range_1983 FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_1983_max  PARTITION of emp_range_range_1983 FOR VALUES FROM (5000) TO (MAXVALUE);

create table emp_range_range_1984_min  PARTITION of emp_range_range_1984 FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_1984_1000 PARTITION of emp_range_range_1984 FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_1984_2000 PARTITION of emp_range_range_1984 FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_1984_3000 PARTITION of emp_range_range_1984 FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_1984_4000 PARTITION of emp_range_range_1984 FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_1984_max  PARTITION of emp_range_range_1984 FOR VALUES FROM (5000) TO (MAXVALUE);

create table emp_range_range_1985_min  PARTITION of emp_range_range_1985 FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_1985_1000 PARTITION of emp_range_range_1985 FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_1985_2000 PARTITION of emp_range_range_1985 FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_1985_3000 PARTITION of emp_range_range_1985 FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_1985_4000 PARTITION of emp_range_range_1985 FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_1985_max  PARTITION of emp_range_range_1985 FOR VALUES FROM (5000) TO (MAXVALUE);

create table emp_range_range_1986_min  PARTITION of emp_range_range_1986 FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_1986_1000 PARTITION of emp_range_range_1986 FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_1986_2000 PARTITION of emp_range_range_1986 FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_1986_3000 PARTITION of emp_range_range_1986 FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_1986_4000 PARTITION of emp_range_range_1986 FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_1986_max  PARTITION of emp_range_range_1986 FOR VALUES FROM (5000) TO (MAXVALUE);

create table emp_range_range_1987_min  PARTITION of emp_range_range_1987 FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_1987_1000 PARTITION of emp_range_range_1987 FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_1987_2000 PARTITION of emp_range_range_1987 FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_1987_3000 PARTITION of emp_range_range_1987 FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_1987_4000 PARTITION of emp_range_range_1987 FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_1987_max  PARTITION of emp_range_range_1987 FOR VALUES FROM (5000) TO (MAXVALUE);

create table emp_range_range_1988_min  PARTITION of emp_range_range_1988 FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_1988_1000 PARTITION of emp_range_range_1988 FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_1988_2000 PARTITION of emp_range_range_1988 FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_1988_3000 PARTITION of emp_range_range_1988 FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_1988_4000 PARTITION of emp_range_range_1988 FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_1988_max  PARTITION of emp_range_range_1988 FOR VALUES FROM (5000) TO (MAXVALUE);

create table emp_range_range_default_min  PARTITION of emp_range_range_default FOR VALUES FROM (MINVALUE) TO (1000);
create table emp_range_range_default_1000 PARTITION of emp_range_range_default FOR VALUES FROM (1000) TO (2000);
create table emp_range_range_default_2000 PARTITION of emp_range_range_default FOR VALUES FROM (2000) TO (3000);
create table emp_range_range_default_3000 PARTITION of emp_range_range_default FOR VALUES FROM (3000) TO (4000);
create table emp_range_range_default_4000 PARTITION of emp_range_range_default FOR VALUES FROM (4000) TO (5000);
create table emp_range_range_default_max  PARTITION of emp_range_range_default FOR VALUES FROM (5000) TO (MAXVALUE);

-- 将二级分区子表绑定到主表上，作为主表的一级分区子表
alter table emp_range_range attach partition emp_range_range_1979    FOR VALUES FROM ('1979-01-01 00:00:00+08') TO ('1980-01-01 00:00:00+08');
alter table emp_range_range attach partition emp_range_range_1980    FOR VALUES FROM ('1980-01-01 00:00:00+08') TO ('1981-01-01 00:00:00+08');
alter table emp_range_range attach partition emp_range_range_1981    FOR VALUES FROM ('1981-01-01 00:00:00+08') TO ('1982-01-01 00:00:00+08');
alter table emp_range_range attach partition emp_range_range_1982    FOR VALUES FROM ('1982-01-01 00:00:00+08') TO ('1983-01-01 00:00:00+08');
alter table emp_range_range attach partition emp_range_range_1983    FOR VALUES FROM ('1983-01-01 00:00:00+08') TO ('1984-01-01 00:00:00+08');
alter table emp_range_range attach partition emp_range_range_1984    FOR VALUES FROM ('1984-01-01 00:00:00+08') TO ('1985-01-01 00:00:00+08');
alter table emp_range_range attach partition emp_range_range_1985    FOR VALUES FROM ('1985-01-01 00:00:00+08') TO ('1986-01-01 00:00:00+08');
alter table emp_range_range attach partition emp_range_range_1986    FOR VALUES FROM ('1986-01-01 00:00:00+08') TO ('1987-01-01 00:00:00+08');
alter table emp_range_range attach partition emp_range_range_1987    FOR VALUES FROM ('1987-01-01 00:00:00+08') TO ('1988-01-01 00:00:00+08');
alter table emp_range_range attach partition emp_range_range_1988    FOR VALUES FROM ('1988-01-01 00:00:00+08') TO ('1989-01-01 00:00:00+08');
alter table emp_range_range attach partition emp_range_range_default default;

插入测试数据

insert into emp_range_range select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss
select * from emp_range_range PARTITION (hiredate_1980);

-- PostgreSQL and orafce and IvorySQL
select * from emp_range_range_1980;

查询分区表的单个子分区里的数据

-- Oracle and openGauss 3.0
SQL> select * from emp_range_range SUBPARTITION (hiredate_1980_min);

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20

openGauss=# select * from emp_range_range SUBPARTITION (hiredate_1980_min);
 empno | ename |  job  | mgr  |      hiredate       |  sal   | comm | deptno
-------+-------+-------+------+---------------------+--------+------+--------
  7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800.00 |      |     20
(1 row)

-- PostgreSQL and orafce and IvorySQL
-- PostgreSQL 的 DATE 数据类型只存储日期，orafce and IvorySQL都支持存储日期和时间
postgres=# select * from emp_range_range_1980_min;
 empno | ename |  job  | mgr  |  hiredate  |  sal   | comm | deptno
-------+-------+-------+------+------------+--------+------+--------
  7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 |      |     20
(1 row)

问题：
- openGauss 2.1 不支持子分区 SUBPARTITION，openGauss 3.0 支持子分区 SUBPARTITION。
- MogDB 2.1 设置 enable_poc_feature=true 可以支持创建子分区 SUBPARTITION，这个参数默认是 off ，更改需要重启数据库生效，enable_poc_feature 等同于 enable_beta_features，参考官方文档：enable_beta_features，关于 MogDB 2.1 创建子分区参考官方文档：CREATE-TABLE-SUBPARTITION。
- PostgreSQL and IvorySQL 不支持使用 select … SUBPARTITION 语句查询单个子分区里的数据。
- PostgreSQL 的 DATE 数据类型只存储日期，orafce and IvorySQL都支持存储日期和时间

范围 + 列表(Range-List)的复合分区

创建范围+列表的复合分区表

-- Oracle and openGauss 3.0
CREATE TABLE emp_range_list
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
SUBPARTITION BY LIST (deptno)
  (PARTITION hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1979_deptno_10 VALUES (10),
     SUBPARTITION hiredate_1979_deptno_20 VALUES (20),
     SUBPARTITION hiredate_1979_deptno_30 VALUES (30),
     SUBPARTITION hiredate_1979_deptno_default VALUES (DEFAULT)),
   PARTITION hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1980_deptno_10 VALUES (10),
     SUBPARTITION hiredate_1980_deptno_20 VALUES (20),
     SUBPARTITION hiredate_1980_deptno_30 VALUES (30),
     SUBPARTITION hiredate_1980_deptno_default VALUES (DEFAULT)),
   PARTITION hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1981_deptno_10 VALUES (10),
     SUBPARTITION hiredate_1981_deptno_20 VALUES (20),
     SUBPARTITION hiredate_1981_deptno_30 VALUES (30),
     SUBPARTITION hiredate_1981_deptno_default VALUES (DEFAULT)),  
   PARTITION hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1982_deptno_10 VALUES (10),
     SUBPARTITION hiredate_1982_deptno_20 VALUES (20),
     SUBPARTITION hiredate_1982_deptno_30 VALUES (30),
     SUBPARTITION hiredate_1982_deptno_default VALUES (DEFAULT)),
   PARTITION hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1983_deptno_10 VALUES (10),
     SUBPARTITION hiredate_1983_deptno_20 VALUES (20),
     SUBPARTITION hiredate_1983_deptno_30 VALUES (30),
     SUBPARTITION hiredate_1983_deptno_default VALUES (DEFAULT)),
   PARTITION hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1984_deptno_10 VALUES (10),
     SUBPARTITION hiredate_1984_deptno_20 VALUES (20),
     SUBPARTITION hiredate_1984_deptno_30 VALUES (30),
     SUBPARTITION hiredate_1984_deptno_default VALUES (DEFAULT)),
   PARTITION hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1985_deptno_10 VALUES (10),
     SUBPARTITION hiredate_1985_deptno_20 VALUES (20),
     SUBPARTITION hiredate_1985_deptno_30 VALUES (30),
     SUBPARTITION hiredate_1985_deptno_default VALUES (DEFAULT)),
   PARTITION hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1986_deptno_10 VALUES (10),
     SUBPARTITION hiredate_1986_deptno_20 VALUES (20),
     SUBPARTITION hiredate_1986_deptno_30 VALUES (30),
     SUBPARTITION hiredate_1986_deptno_default VALUES (DEFAULT)),
   PARTITION hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1987_deptno_10 VALUES (10),
     SUBPARTITION hiredate_1987_deptno_20 VALUES (20),
     SUBPARTITION hiredate_1987_deptno_30 VALUES (30),
     SUBPARTITION hiredate_1987_deptno_default VALUES (DEFAULT)),
   PARTITION hiredate_max VALUES LESS THAN (MAXVALUE)
    (SUBPARTITION hiredate_max_deptno_10 VALUES (10),
     SUBPARTITION hiredate_max_deptno_20 VALUES (20),
     SUBPARTITION hiredate_max_deptno_30 VALUES (30),
     SUBPARTITION hiredate_max_deptno_default VALUES (DEFAULT)))
;

-- MogDB 2.1 暂时也不支持 LIST 分区的 DEFAULT 分区，3.0版本应该会支持。
ERROR:  Un-support feature
DETAIL:  The default list's partition is not supported currently.

-- MySQL
... ...

-- PostgreSQL and orafce and IvorySQL 创建主表
-- IvorySQL 支持 NUMBER and VARCHAR2，orafce 支持 VARCHAR2
-- 如果需要相关字段请自行替换，本实验将 NUMBER --> NUMERIC，VARCHAR2 --> VARCHAR，DATE --> TIMESTAMP
CREATE TABLE emp_range_list
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY RANGE (hiredate);

-- PostgreSQL and orafce and IvorySQL 创建二级分区主表
CREATE TABLE emp_range_list_1979    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_range_list_1980    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_range_list_1981    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_range_list_1982    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_range_list_1983    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_range_list_1984    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_range_list_1985    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_range_list_1986    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_range_list_1987    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_range_list_1988    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_range_list_default (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);

-- 创建二级分区子表
create table emp_range_list_1979_deptno_10      partition of emp_range_list_1979 for values in (10);
create table emp_range_list_1979_deptno_20      partition of emp_range_list_1979 for values in (20);
create table emp_range_list_1979_deptno_30      partition of emp_range_list_1979 for values in (30);
create table emp_range_list_1979_deptno_default partition of emp_range_list_1979 default;

create table emp_range_list_1980_deptno_10      partition of emp_range_list_1980 for values in (10);
create table emp_range_list_1980_deptno_20      partition of emp_range_list_1980 for values in (20);
create table emp_range_list_1980_deptno_30      partition of emp_range_list_1980 for values in (30);
create table emp_range_list_1980_deptno_default partition of emp_range_list_1980 default;

create table emp_range_list_1981_deptno_10      partition of emp_range_list_1981 for values in (10);
create table emp_range_list_1981_deptno_20      partition of emp_range_list_1981 for values in (20);
create table emp_range_list_1981_deptno_30      partition of emp_range_list_1981 for values in (30);
create table emp_range_list_1981_deptno_default partition of emp_range_list_1981 default;

create table emp_range_list_1982_deptno_10      partition of emp_range_list_1982 for values in (10);
create table emp_range_list_1982_deptno_20      partition of emp_range_list_1982 for values in (20);
create table emp_range_list_1982_deptno_30      partition of emp_range_list_1982 for values in (30);
create table emp_range_list_1982_deptno_default partition of emp_range_list_1982 default;

create table emp_range_list_1983_deptno_10      partition of emp_range_list_1983 for values in (10);
create table emp_range_list_1983_deptno_20      partition of emp_range_list_1983 for values in (20);
create table emp_range_list_1983_deptno_30      partition of emp_range_list_1983 for values in (30);
create table emp_range_list_1983_deptno_default partition of emp_range_list_1983 default;

create table emp_range_list_1984_deptno_10      partition of emp_range_list_1984 for values in (10);
create table emp_range_list_1984_deptno_20      partition of emp_range_list_1984 for values in (20);
create table emp_range_list_1984_deptno_30      partition of emp_range_list_1984 for values in (30);
create table emp_range_list_1984_deptno_default partition of emp_range_list_1984 default;

create table emp_range_list_1985_deptno_10      partition of emp_range_list_1985 for values in (10);
create table emp_range_list_1985_deptno_20      partition of emp_range_list_1985 for values in (20);
create table emp_range_list_1985_deptno_30      partition of emp_range_list_1985 for values in (30);
create table emp_range_list_1985_deptno_default partition of emp_range_list_1985 default;

create table emp_range_list_1986_deptno_10      partition of emp_range_list_1986 for values in (10);
create table emp_range_list_1986_deptno_20      partition of emp_range_list_1986 for values in (20);
create table emp_range_list_1986_deptno_30      partition of emp_range_list_1986 for values in (30);
create table emp_range_list_1986_deptno_default partition of emp_range_list_1986 default;

create table emp_range_list_1987_deptno_10      partition of emp_range_list_1987 for values in (10);
create table emp_range_list_1987_deptno_20      partition of emp_range_list_1987 for values in (20);
create table emp_range_list_1987_deptno_30      partition of emp_range_list_1987 for values in (30);
create table emp_range_list_1987_deptno_default partition of emp_range_list_1987 default;

create table emp_range_list_1988_deptno_10      partition of emp_range_list_1988 for values in (10);
create table emp_range_list_1988_deptno_20      partition of emp_range_list_1988 for values in (20);
create table emp_range_list_1988_deptno_30      partition of emp_range_list_1988 for values in (30);
create table emp_range_list_1988_deptno_default partition of emp_range_list_1988 default;

create table emp_range_list_default_deptno_10      partition of emp_range_list_default for values in (10);
create table emp_range_list_default_deptno_20      partition of emp_range_list_default for values in (20);
create table emp_range_list_default_deptno_30      partition of emp_range_list_default for values in (30);
create table emp_range_list_default_deptno_default partition of emp_range_list_default default;

-- 将二级分区子表绑定到主表上，作为主表的一级分区子表
alter table emp_range_list attach partition emp_range_list_1979    FOR VALUES FROM ('1979-01-01 00:00:00+08') TO ('1980-01-01 00:00:00+08');
alter table emp_range_list attach partition emp_range_list_1980    FOR VALUES FROM ('1980-01-01 00:00:00+08') TO ('1981-01-01 00:00:00+08');
alter table emp_range_list attach partition emp_range_list_1981    FOR VALUES FROM ('1981-01-01 00:00:00+08') TO ('1982-01-01 00:00:00+08');
alter table emp_range_list attach partition emp_range_list_1982    FOR VALUES FROM ('1982-01-01 00:00:00+08') TO ('1983-01-01 00:00:00+08');
alter table emp_range_list attach partition emp_range_list_1983    FOR VALUES FROM ('1983-01-01 00:00:00+08') TO ('1984-01-01 00:00:00+08');
alter table emp_range_list attach partition emp_range_list_1984    FOR VALUES FROM ('1984-01-01 00:00:00+08') TO ('1985-01-01 00:00:00+08');
alter table emp_range_list attach partition emp_range_list_1985    FOR VALUES FROM ('1985-01-01 00:00:00+08') TO ('1986-01-01 00:00:00+08');
alter table emp_range_list attach partition emp_range_list_1986    FOR VALUES FROM ('1986-01-01 00:00:00+08') TO ('1987-01-01 00:00:00+08');
alter table emp_range_list attach partition emp_range_list_1987    FOR VALUES FROM ('1987-01-01 00:00:00+08') TO ('1988-01-01 00:00:00+08');
alter table emp_range_list attach partition emp_range_list_1988    FOR VALUES FROM ('1988-01-01 00:00:00+08') TO ('1989-01-01 00:00:00+08');
alter table emp_range_list attach partition emp_range_list_default default;

插入测试数据

insert into emp_range_list select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss
select * from emp_range_list PARTITION (hiredate_1980);
-- PostgreSQL and orafce and IvorySQL
select * from emp_range_list_1980;

查询分区表的单个子分区里的数据

-- Oracle and openGauss 3.0
select * from emp_range_list SUBPARTITION (hiredate_1980_deptno_20);
-- PostgreSQL and orafce and IvorySQL
select * from emp_range_list_1980_deptno_20;

范围 + 哈希(Range-Hash)的复合分区

创建范围+哈希的复合分区表

-- Oracle and openGauss 3.0
CREATE TABLE emp_range_hash
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY RANGE (hiredate)
SUBPARTITION BY HASH (sal)
  (PARTITION hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1979_hash_1,
     SUBPARTITION hiredate_1979_hash_2,
     SUBPARTITION hiredate_1979_hash_3,
     SUBPARTITION hiredate_1979_hash_4),
   PARTITION hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1980_hash_1,
     SUBPARTITION hiredate_1980_hash_2,
     SUBPARTITION hiredate_1980_hash_3,
     SUBPARTITION hiredate_1980_hash_4),
   PARTITION hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1981_hash_1,
     SUBPARTITION hiredate_1981_hash_2,
     SUBPARTITION hiredate_1981_hash_3,
     SUBPARTITION hiredate_1981_hash_4), 
   PARTITION hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1982_hash_1,
     SUBPARTITION hiredate_1982_hash_2,
     SUBPARTITION hiredate_1982_hash_3,
     SUBPARTITION hiredate_1982_hash_4),
   PARTITION hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1983_hash_1,
     SUBPARTITION hiredate_1983_hash_2,
     SUBPARTITION hiredate_1983_hash_3,
     SUBPARTITION hiredate_1983_hash_4),
   PARTITION hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1984_hash_1,
     SUBPARTITION hiredate_1984_hash_2,
     SUBPARTITION hiredate_1984_hash_3,
     SUBPARTITION hiredate_1984_hash_4),
   PARTITION hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1985_hash_1,
     SUBPARTITION hiredate_1985_hash_2,
     SUBPARTITION hiredate_1985_hash_3,
     SUBPARTITION hiredate_1985_hash_4),
   PARTITION hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1986_hash_1,
     SUBPARTITION hiredate_1986_hash_2,
     SUBPARTITION hiredate_1986_hash_3,
     SUBPARTITION hiredate_1986_hash_4),
   PARTITION hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY'))
    (SUBPARTITION hiredate_1987_hash_1,
     SUBPARTITION hiredate_1987_hash_2,
     SUBPARTITION hiredate_1987_hash_3,
     SUBPARTITION hiredate_1987_hash_4),
   PARTITION hiredate_max VALUES LESS THAN (MAXVALUE)
    (SUBPARTITION hiredate_max_hash_1,
     SUBPARTITION hiredate_max_hash_2,
     SUBPARTITION hiredate_max_hash_3,
     SUBPARTITION hiredate_max_hash_4))
;

-- MySQL
... ...

-- PostgreSQL and orafce and IvorySQL 创建主表
-- IvorySQL 支持 NUMBER and VARCHAR2，orafce 支持 VARCHAR2
-- 如果需要相关字段请自行替换，本实验将 NUMBER --> NUMERIC，VARCHAR2 --> VARCHAR，DATE --> TIMESTAMP
CREATE TABLE emp_range_hash
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY RANGE (hiredate);

-- PostgreSQL and orafce and IvorySQL 创建二级分区主表
CREATE TABLE emp_range_hash_1979    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_range_hash_1980    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_range_hash_1981    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_range_hash_1982    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_range_hash_1983    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_range_hash_1984    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_range_hash_1985    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_range_hash_1986    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_range_hash_1987    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_range_hash_1988    (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_range_hash_default (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);

-- 创建二级分区子表
create table emp_range_hash_1979_hash_1 partition of emp_range_hash_1979 for values with (modulus 4, remainder 0);
create table emp_range_hash_1979_hash_2 partition of emp_range_hash_1979 for values with (modulus 4, remainder 1);
create table emp_range_hash_1979_hash_3 partition of emp_range_hash_1979 for values with (modulus 4, remainder 2);
create table emp_range_hash_1979_hash_4 partition of emp_range_hash_1979 for values with (modulus 4, remainder 3);

create table emp_range_hash_1980_hash_1 partition of emp_range_hash_1980 for values with (modulus 4, remainder 0);
create table emp_range_hash_1980_hash_2 partition of emp_range_hash_1980 for values with (modulus 4, remainder 1);
create table emp_range_hash_1980_hash_3 partition of emp_range_hash_1980 for values with (modulus 4, remainder 2);
create table emp_range_hash_1980_hash_4 partition of emp_range_hash_1980 for values with (modulus 4, remainder 3);

create table emp_range_hash_1981_hash_1 partition of emp_range_hash_1981 for values with (modulus 4, remainder 0);
create table emp_range_hash_1981_hash_2 partition of emp_range_hash_1981 for values with (modulus 4, remainder 1);
create table emp_range_hash_1981_hash_3 partition of emp_range_hash_1981 for values with (modulus 4, remainder 2);
create table emp_range_hash_1981_hash_4 partition of emp_range_hash_1981 for values with (modulus 4, remainder 3);

create table emp_range_hash_1982_hash_1 partition of emp_range_hash_1982 for values with (modulus 4, remainder 0);
create table emp_range_hash_1982_hash_2 partition of emp_range_hash_1982 for values with (modulus 4, remainder 1);
create table emp_range_hash_1982_hash_3 partition of emp_range_hash_1982 for values with (modulus 4, remainder 2);
create table emp_range_hash_1982_hash_4 partition of emp_range_hash_1982 for values with (modulus 4, remainder 3);

create table emp_range_hash_1983_hash_1 partition of emp_range_hash_1983 for values with (modulus 4, remainder 0);
create table emp_range_hash_1983_hash_2 partition of emp_range_hash_1983 for values with (modulus 4, remainder 1);
create table emp_range_hash_1983_hash_3 partition of emp_range_hash_1983 for values with (modulus 4, remainder 2);
create table emp_range_hash_1983_hash_4 partition of emp_range_hash_1983 for values with (modulus 4, remainder 3);

create table emp_range_hash_1984_hash_1 partition of emp_range_hash_1984 for values with (modulus 4, remainder 0);
create table emp_range_hash_1984_hash_2 partition of emp_range_hash_1984 for values with (modulus 4, remainder 1);
create table emp_range_hash_1984_hash_3 partition of emp_range_hash_1984 for values with (modulus 4, remainder 2);
create table emp_range_hash_1984_hash_4 partition of emp_range_hash_1984 for values with (modulus 4, remainder 3);

create table emp_range_hash_1985_hash_1 partition of emp_range_hash_1985 for values with (modulus 4, remainder 0);
create table emp_range_hash_1985_hash_2 partition of emp_range_hash_1985 for values with (modulus 4, remainder 1);
create table emp_range_hash_1985_hash_3 partition of emp_range_hash_1985 for values with (modulus 4, remainder 2);
create table emp_range_hash_1985_hash_4 partition of emp_range_hash_1985 for values with (modulus 4, remainder 3);

create table emp_range_hash_1986_hash_1 partition of emp_range_hash_1986 for values with (modulus 4, remainder 0);
create table emp_range_hash_1986_hash_2 partition of emp_range_hash_1986 for values with (modulus 4, remainder 1);
create table emp_range_hash_1986_hash_3 partition of emp_range_hash_1986 for values with (modulus 4, remainder 2);
create table emp_range_hash_1986_hash_4 partition of emp_range_hash_1986 for values with (modulus 4, remainder 3);

create table emp_range_hash_1987_hash_1 partition of emp_range_hash_1987 for values with (modulus 4, remainder 0);
create table emp_range_hash_1987_hash_2 partition of emp_range_hash_1987 for values with (modulus 4, remainder 1);
create table emp_range_hash_1987_hash_3 partition of emp_range_hash_1987 for values with (modulus 4, remainder 2);
create table emp_range_hash_1987_hash_4 partition of emp_range_hash_1987 for values with (modulus 4, remainder 3);

create table emp_range_hash_1988_hash_1 partition of emp_range_hash_1988 for values with (modulus 4, remainder 0);
create table emp_range_hash_1988_hash_2 partition of emp_range_hash_1988 for values with (modulus 4, remainder 1);
create table emp_range_hash_1988_hash_3 partition of emp_range_hash_1988 for values with (modulus 4, remainder 2);
create table emp_range_hash_1988_hash_4 partition of emp_range_hash_1988 for values with (modulus 4, remainder 3);

create table emp_range_hash_default_hash_1 partition of emp_range_hash_default for values with (modulus 4, remainder 0);
create table emp_range_hash_default_hash_2 partition of emp_range_hash_default for values with (modulus 4, remainder 1);
create table emp_range_hash_default_hash_3 partition of emp_range_hash_default for values with (modulus 4, remainder 2);
create table emp_range_hash_default_hash_4 partition of emp_range_hash_default for values with (modulus 4, remainder 3);

-- 将二级分区子表绑定到主表上，作为主表的一级分区子表
alter table emp_range_hash attach partition emp_range_hash_1979    FOR VALUES FROM ('1979-01-01 00:00:00+08') TO ('1980-01-01 00:00:00+08');
alter table emp_range_hash attach partition emp_range_hash_1980    FOR VALUES FROM ('1980-01-01 00:00:00+08') TO ('1981-01-01 00:00:00+08');
alter table emp_range_hash attach partition emp_range_hash_1981    FOR VALUES FROM ('1981-01-01 00:00:00+08') TO ('1982-01-01 00:00:00+08');
alter table emp_range_hash attach partition emp_range_hash_1982    FOR VALUES FROM ('1982-01-01 00:00:00+08') TO ('1983-01-01 00:00:00+08');
alter table emp_range_hash attach partition emp_range_hash_1983    FOR VALUES FROM ('1983-01-01 00:00:00+08') TO ('1984-01-01 00:00:00+08');
alter table emp_range_hash attach partition emp_range_hash_1984    FOR VALUES FROM ('1984-01-01 00:00:00+08') TO ('1985-01-01 00:00:00+08');
alter table emp_range_hash attach partition emp_range_hash_1985    FOR VALUES FROM ('1985-01-01 00:00:00+08') TO ('1986-01-01 00:00:00+08');
alter table emp_range_hash attach partition emp_range_hash_1986    FOR VALUES FROM ('1986-01-01 00:00:00+08') TO ('1987-01-01 00:00:00+08');
alter table emp_range_hash attach partition emp_range_hash_1987    FOR VALUES FROM ('1987-01-01 00:00:00+08') TO ('1988-01-01 00:00:00+08');
alter table emp_range_hash attach partition emp_range_hash_1988    FOR VALUES FROM ('1988-01-01 00:00:00+08') TO ('1989-01-01 00:00:00+08');
alter table emp_range_hash attach partition emp_range_hash_default default;

插入测试数据

insert into emp_range_hash select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss
select * from emp_range_hash PARTITION (hiredate_1980);
-- PostgreSQL and orafce and IvorySQL
select * from emp_range_hash_1980;

查询分区表的单个子分区里的数据

-- Oracle and openGauss 3.0
select * from emp_range_hash SUBPARTITION (hiredate_1980_hash_3);
-- PostgreSQL and orafce and IvorySQL
select * from emp_range_hash_1980_hash_3;

列表 + 范围(List-Range)的复合分区

创建列表 + 范围的复合分区表

-- Oracle and openGauss 3.0
CREATE TABLE emp_list_range
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY LIST (deptno)
SUBPARTITION BY RANGE (hiredate)
  (PARTITION deptno_10 VALUES (10)
    (SUBPARTITION deptno_10_hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_1988 VALUES LESS THAN (TO_DATE('01-01-1989','DD-MM-YYYY')),
     SUBPARTITION deptno_10_hiredate_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION deptno_11 VALUES (20)
    (SUBPARTITION deptno_11_hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY')),
     SUBPARTITION deptno_11_hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY')),
     SUBPARTITION deptno_11_hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY')),
     SUBPARTITION deptno_11_hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY')),
     SUBPARTITION deptno_11_hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY')),
     SUBPARTITION deptno_11_hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY')),
     SUBPARTITION deptno_11_hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY')),
     SUBPARTITION deptno_11_hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY')),
     SUBPARTITION deptno_11_hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY')),
     SUBPARTITION deptno_11_hiredate_1988 VALUES LESS THAN (TO_DATE('01-01-1989','DD-MM-YYYY')),
     SUBPARTITION deptno_11_hiredate_max  VALUES LESS THAN (MAXVALUE)), 
   PARTITION deptno_12 VALUES (30)
    (SUBPARTITION deptno_12_hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY')),
     SUBPARTITION deptno_12_hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY')),
     SUBPARTITION deptno_12_hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY')),
     SUBPARTITION deptno_12_hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY')),
     SUBPARTITION deptno_12_hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY')),
     SUBPARTITION deptno_12_hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY')),
     SUBPARTITION deptno_12_hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY')),
     SUBPARTITION deptno_12_hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY')),
     SUBPARTITION deptno_12_hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY')),
     SUBPARTITION deptno_12_hiredate_1988 VALUES LESS THAN (TO_DATE('01-01-1989','DD-MM-YYYY')),
     SUBPARTITION deptno_12_hiredate_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION deptno_default VALUES (DEFAULT)
    (SUBPARTITION deptno_default_hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_1988 VALUES LESS THAN (TO_DATE('01-01-1989','DD-MM-YYYY')),
     SUBPARTITION deptno_default_hiredate_max  VALUES LESS THAN (MAXVALUE)));

-- MySQL
... ...

-- PostgreSQL and orafce and IvorySQL 创建主表
-- IvorySQL 支持 NUMBER and VARCHAR2，orafce 支持 VARCHAR2
-- 如果需要相关字段请自行替换，本实验将 NUMBER --> NUMERIC，VARCHAR2 --> VARCHAR，DATE --> TIMESTAMP
CREATE TABLE emp_list_range
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY LIST (deptno);

-- PostgreSQL and orafce and IvorySQL 创建二级分区主表
CREATE TABLE emp_list_range_deptno_10      (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (hiredate);
CREATE TABLE emp_list_range_deptno_20      (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (hiredate);
CREATE TABLE emp_list_range_deptno_30      (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (hiredate);
CREATE TABLE emp_list_range_deptno_default (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (hiredate);

-- 创建二级分区子表
create table emp_list_range_deptno_10_1979    partition of emp_list_range_deptno_10 for values from ('1979-01-01 00:00:00+08') to ('1980-01-01 00:00:00+08');
create table emp_list_range_deptno_10_1980    partition of emp_list_range_deptno_10 for values from ('1980-01-01 00:00:00+08') to ('1981-01-01 00:00:00+08');
create table emp_list_range_deptno_10_1981    partition of emp_list_range_deptno_10 for values from ('1981-01-01 00:00:00+08') to ('1982-01-01 00:00:00+08');
create table emp_list_range_deptno_10_1982    partition of emp_list_range_deptno_10 for values from ('1982-01-01 00:00:00+08') to ('1983-01-01 00:00:00+08');
create table emp_list_range_deptno_10_1983    partition of emp_list_range_deptno_10 for values from ('1983-01-01 00:00:00+08') to ('1984-01-01 00:00:00+08');
create table emp_list_range_deptno_10_1984    partition of emp_list_range_deptno_10 for values from ('1984-01-01 00:00:00+08') to ('1985-01-01 00:00:00+08');
create table emp_list_range_deptno_10_1985    partition of emp_list_range_deptno_10 for values from ('1985-01-01 00:00:00+08') to ('1986-01-01 00:00:00+08');
create table emp_list_range_deptno_10_1986    partition of emp_list_range_deptno_10 for values from ('1986-01-01 00:00:00+08') to ('1987-01-01 00:00:00+08');
create table emp_list_range_deptno_10_1987    partition of emp_list_range_deptno_10 for values from ('1987-01-01 00:00:00+08') to ('1988-01-01 00:00:00+08');
create table emp_list_range_deptno_10_1988    partition of emp_list_range_deptno_10 for values from ('1988-01-01 00:00:00+08') to ('1989-01-01 00:00:00+08');
create table emp_list_range_deptno_10_default partition of emp_list_range_deptno_10 default;

create table emp_list_range_deptno_20_1979    partition of emp_list_range_deptno_20 for values from ('1979-01-01 00:00:00+08') to ('1980-01-01 00:00:00+08');
create table emp_list_range_deptno_20_1980    partition of emp_list_range_deptno_20 for values from ('1980-01-01 00:00:00+08') to ('1981-01-01 00:00:00+08');
create table emp_list_range_deptno_20_1981    partition of emp_list_range_deptno_20 for values from ('1981-01-01 00:00:00+08') to ('1982-01-01 00:00:00+08');
create table emp_list_range_deptno_20_1982    partition of emp_list_range_deptno_20 for values from ('1982-01-01 00:00:00+08') to ('1983-01-01 00:00:00+08');
create table emp_list_range_deptno_20_1983    partition of emp_list_range_deptno_20 for values from ('1983-01-01 00:00:00+08') to ('1984-01-01 00:00:00+08');
create table emp_list_range_deptno_20_1984    partition of emp_list_range_deptno_20 for values from ('1984-01-01 00:00:00+08') to ('1985-01-01 00:00:00+08');
create table emp_list_range_deptno_20_1985    partition of emp_list_range_deptno_20 for values from ('1985-01-01 00:00:00+08') to ('1986-01-01 00:00:00+08');
create table emp_list_range_deptno_20_1986    partition of emp_list_range_deptno_20 for values from ('1986-01-01 00:00:00+08') to ('1987-01-01 00:00:00+08');
create table emp_list_range_deptno_20_1987    partition of emp_list_range_deptno_20 for values from ('1987-01-01 00:00:00+08') to ('1988-01-01 00:00:00+08');
create table emp_list_range_deptno_20_1988    partition of emp_list_range_deptno_20 for values from ('1988-01-01 00:00:00+08') to ('1989-01-01 00:00:00+08');
create table emp_list_range_deptno_20_default partition of emp_list_range_deptno_20 default;

create table emp_list_range_deptno_30_1979    partition of emp_list_range_deptno_30 for values from ('1979-01-01 00:00:00+08') to ('1980-01-01 00:00:00+08');
create table emp_list_range_deptno_30_1980    partition of emp_list_range_deptno_30 for values from ('1980-01-01 00:00:00+08') to ('1981-01-01 00:00:00+08');
create table emp_list_range_deptno_30_1981    partition of emp_list_range_deptno_30 for values from ('1981-01-01 00:00:00+08') to ('1982-01-01 00:00:00+08');
create table emp_list_range_deptno_30_1982    partition of emp_list_range_deptno_30 for values from ('1982-01-01 00:00:00+08') to ('1983-01-01 00:00:00+08');
create table emp_list_range_deptno_30_1983    partition of emp_list_range_deptno_30 for values from ('1983-01-01 00:00:00+08') to ('1984-01-01 00:00:00+08');
create table emp_list_range_deptno_30_1984    partition of emp_list_range_deptno_30 for values from ('1984-01-01 00:00:00+08') to ('1985-01-01 00:00:00+08');
create table emp_list_range_deptno_30_1985    partition of emp_list_range_deptno_30 for values from ('1985-01-01 00:00:00+08') to ('1986-01-01 00:00:00+08');
create table emp_list_range_deptno_30_1986    partition of emp_list_range_deptno_30 for values from ('1986-01-01 00:00:00+08') to ('1987-01-01 00:00:00+08');
create table emp_list_range_deptno_30_1987    partition of emp_list_range_deptno_30 for values from ('1987-01-01 00:00:00+08') to ('1988-01-01 00:00:00+08');
create table emp_list_range_deptno_30_1988    partition of emp_list_range_deptno_30 for values from ('1988-01-01 00:00:00+08') to ('1989-01-01 00:00:00+08');
create table emp_list_range_deptno_30_default partition of emp_list_range_deptno_30 default;

create table emp_list_range_deptno_default_1979    partition of emp_list_range_deptno_default for values from ('1979-01-01 00:00:00+08') to ('1980-01-01 00:00:00+08');
create table emp_list_range_deptno_default_1980    partition of emp_list_range_deptno_default for values from ('1980-01-01 00:00:00+08') to ('1981-01-01 00:00:00+08');
create table emp_list_range_deptno_default_1981    partition of emp_list_range_deptno_default for values from ('1981-01-01 00:00:00+08') to ('1982-01-01 00:00:00+08');
create table emp_list_range_deptno_default_1982    partition of emp_list_range_deptno_default for values from ('1982-01-01 00:00:00+08') to ('1983-01-01 00:00:00+08');
create table emp_list_range_deptno_default_1983    partition of emp_list_range_deptno_default for values from ('1983-01-01 00:00:00+08') to ('1984-01-01 00:00:00+08');
create table emp_list_range_deptno_default_1984    partition of emp_list_range_deptno_default for values from ('1984-01-01 00:00:00+08') to ('1985-01-01 00:00:00+08');
create table emp_list_range_deptno_default_1985    partition of emp_list_range_deptno_default for values from ('1985-01-01 00:00:00+08') to ('1986-01-01 00:00:00+08');
create table emp_list_range_deptno_default_1986    partition of emp_list_range_deptno_default for values from ('1986-01-01 00:00:00+08') to ('1987-01-01 00:00:00+08');
create table emp_list_range_deptno_default_1987    partition of emp_list_range_deptno_default for values from ('1987-01-01 00:00:00+08') to ('1988-01-01 00:00:00+08');
create table emp_list_range_deptno_default_1988    partition of emp_list_range_deptno_default for values from ('1988-01-01 00:00:00+08') to ('1989-01-01 00:00:00+08');
create table emp_list_range_deptno_default_default partition of emp_list_range_deptno_default default;

-- 将二级分区子表绑定到主表上，作为主表的一级分区子表
alter table emp_list_range attach partition emp_list_range_deptno_10      for values in (10);
alter table emp_list_range attach partition emp_list_range_deptno_20      for values in (20);
alter table emp_list_range attach partition emp_list_range_deptno_30      for values in (30);
alter table emp_list_range attach partition emp_list_range_deptno_default default;

插入测试数据

insert into emp_list_range select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss
select * from emp_list_range PARTITION (deptno_20);
-- PostgreSQL and orafce and IvorySQL
select * from emp_list_range_deptno_20;

查询分区表的单个子分区里的数据

-- Oracle and openGauss 3.0
select * from emp_list_range SUBPARTITION (deptno_20_hiredate_1980);
-- PostgreSQL and orafce and IvorySQL
select * from emp_list_range_deptno_20_1980;

列表 + 列表(List-List)的复合分区

创建列表 + 列表的复合分区表

-- Oracle and openGauss 3.0
CREATE TABLE emp_list_list
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY LIST (deptno)
SUBPARTITION BY LIST (job)
  (PARTITION deptno_10 VALUES (10)
    (SUBPARTITION deptno_10_CLERK     VALUES ('CLERK'),
     SUBPARTITION deptno_10_SALESMAN  VALUES ('SALESMAN'),
     SUBPARTITION deptno_10_MANAGER   VALUES ('MANAGER'),
     SUBPARTITION deptno_10_ANALYST   VALUES ('ANALYST'),
     SUBPARTITION deptno_10_PRESIDENT VALUES ('PRESIDENT'),
     SUBPARTITION deptno_10_default   VALUES (DEFAULT)),
   PARTITION deptno_20 VALUES (20)
    (SUBPARTITION deptno_20_CLERK     VALUES ('CLERK'),
     SUBPARTITION deptno_20_SALESMAN  VALUES ('SALESMAN'),
     SUBPARTITION deptno_20_MANAGER   VALUES ('MANAGER'),
     SUBPARTITION deptno_20_ANALYST   VALUES ('ANALYST'),
     SUBPARTITION deptno_20_PRESIDENT VALUES ('PRESIDENT'),
     SUBPARTITION deptno_20_default   VALUES (DEFAULT)),
   PARTITION deptno_30 VALUES (30)
    (SUBPARTITION deptno_30_CLERK     VALUES ('CLERK'),
     SUBPARTITION deptno_30_SALESMAN  VALUES ('SALESMAN'),
     SUBPARTITION deptno_30_MANAGER   VALUES ('MANAGER'),
     SUBPARTITION deptno_30_ANALYST   VALUES ('ANALYST'),
     SUBPARTITION deptno_30_PRESIDENT VALUES ('PRESIDENT'),
     SUBPARTITION deptno_30_default   VALUES (DEFAULT)),
   PARTITION deptno_default VALUES (DEFAULT)
    (SUBPARTITION deptno_default_CLERK     VALUES ('CLERK'),
     SUBPARTITION deptno_default_SALESMAN  VALUES ('SALESMAN'),
     SUBPARTITION deptno_default_MANAGER   VALUES ('MANAGER'),
     SUBPARTITION deptno_default_ANALYST   VALUES ('ANALYST'),
     SUBPARTITION deptno_default_PRESIDENT VALUES ('PRESIDENT'),
     SUBPARTITION deptno_default_default   VALUES (DEFAULT)));

-- MySQL
... ...

-- PostgreSQL and orafce and IvorySQL 创建主表
-- IvorySQL 支持 NUMBER and VARCHAR2，orafce 支持 VARCHAR2
-- 如果需要相关字段请自行替换，本实验将 NUMBER --> NUMERIC，VARCHAR2 --> VARCHAR，DATE --> TIMESTAMP
CREATE TABLE emp_list_list
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY LIST (deptno);

-- PostgreSQL and orafce and IvorySQL 创建二级分区主表
CREATE TABLE emp_list_list_deptno_10      (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (job);
CREATE TABLE emp_list_list_deptno_20      (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (job);
CREATE TABLE emp_list_list_deptno_30      (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (job);
CREATE TABLE emp_list_list_deptno_default (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (job);

-- 创建二级分区子表
create table emp_list_list_deptno_10_CLERK     partition of emp_list_list_deptno_10 for values in ('CLERK');
create table emp_list_list_deptno_10_SALESMAN  partition of emp_list_list_deptno_10 for values in ('SALESMAN');
create table emp_list_list_deptno_10_MANAGER   partition of emp_list_list_deptno_10 for values in ('MANAGER');
create table emp_list_list_deptno_10_ANALYST   partition of emp_list_list_deptno_10 for values in ('ANALYST');
create table emp_list_list_deptno_10_PRESIDENT partition of emp_list_list_deptno_10 for values in ('PRESIDENT');
create table emp_list_list_deptno_10_default   partition of emp_list_list_deptno_10 default;

create table emp_list_list_deptno_20_CLERK     partition of emp_list_list_deptno_20 for values in ('CLERK');
create table emp_list_list_deptno_20_SALESMAN  partition of emp_list_list_deptno_20 for values in ('SALESMAN');
create table emp_list_list_deptno_20_MANAGER   partition of emp_list_list_deptno_20 for values in ('MANAGER');
create table emp_list_list_deptno_20_ANALYST   partition of emp_list_list_deptno_20 for values in ('ANALYST');
create table emp_list_list_deptno_20_PRESIDENT partition of emp_list_list_deptno_20 for values in ('PRESIDENT');
create table emp_list_list_deptno_20_default   partition of emp_list_list_deptno_20 default;

create table emp_list_list_deptno_30_CLERK     partition of emp_list_list_deptno_30 for values in ('CLERK');
create table emp_list_list_deptno_30_SALESMAN  partition of emp_list_list_deptno_30 for values in ('SALESMAN');
create table emp_list_list_deptno_30_MANAGER   partition of emp_list_list_deptno_30 for values in ('MANAGER');
create table emp_list_list_deptno_30_ANALYST   partition of emp_list_list_deptno_30 for values in ('ANALYST');
create table emp_list_list_deptno_30_PRESIDENT partition of emp_list_list_deptno_30 for values in ('PRESIDENT');
create table emp_list_list_deptno_30_default   partition of emp_list_list_deptno_30 default;

create table emp_list_list_deptno_default_CLERK     partition of emp_list_list_deptno_default for values in ('CLERK');
create table emp_list_list_deptno_default_SALESMAN  partition of emp_list_list_deptno_default for values in ('SALESMAN');
create table emp_list_list_deptno_default_MANAGER   partition of emp_list_list_deptno_default for values in ('MANAGER');
create table emp_list_list_deptno_default_ANALYST   partition of emp_list_list_deptno_default for values in ('ANALYST');
create table emp_list_list_deptno_default_PRESIDENT partition of emp_list_list_deptno_default for values in ('PRESIDENT');
create table emp_list_list_deptno_default_default   partition of emp_list_list_deptno_default default;

-- 将二级分区子表绑定到主表上，作为主表的一级分区子表
alter table emp_list_list attach partition emp_list_list_deptno_10      for values in (10);
alter table emp_list_list attach partition emp_list_list_deptno_20      for values in (20);
alter table emp_list_list attach partition emp_list_list_deptno_30      for values in (30);
alter table emp_list_list attach partition emp_list_list_deptno_default default;

插入测试数据

insert into emp_list_list select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss
select * from emp_list_list PARTITION (deptno_20);
-- PostgreSQL and orafce and IvorySQL
select * from emp_list_list_deptno_20;

查询分区表的单个子分区里的数据

-- Oracle and openGauss 3.0
select * from emp_list_list SUBPARTITION (deptno_20_MANAGER);
-- PostgreSQL and orafce and IvorySQL
select * from emp_list_list_deptno_20_MANAGER;

列表 + 哈希(List-Hash)的复合分区

创建列表 + 哈希的复合分区表

-- Oracle and openGauss 3.0
CREATE TABLE emp_list_hash
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY LIST (deptno)
SUBPARTITION BY HASH (sal)
  (PARTITION deptno_10 VALUES (10)
    (SUBPARTITION deptno_10_hash_1,
     SUBPARTITION deptno_10_hash_2,
     SUBPARTITION deptno_10_hash_3,
     SUBPARTITION deptno_10_hash_4),
   PARTITION deptno_20 VALUES (20)
    (SUBPARTITION deptno_20_hash_1,
     SUBPARTITION deptno_20_hash_2,
     SUBPARTITION deptno_20_hash_3,
     SUBPARTITION deptno_20_hash_4),
   PARTITION deptno_30 VALUES (30)
    (SUBPARTITION deptno_30_hash_1,
     SUBPARTITION deptno_30_hash_2,
     SUBPARTITION deptno_30_hash_3,
     SUBPARTITION deptno_30_hash_4),
   PARTITION deptno_default VALUES (DEFAULT)
    (SUBPARTITION deptno_default_hash_1,
     SUBPARTITION deptno_default_hash_2,
     SUBPARTITION deptno_default_hash_3,
     SUBPARTITION deptno_default_hash_4));

-- MySQL
... ...

-- PostgreSQL and orafce and IvorySQL 创建主表
-- IvorySQL 支持 NUMBER and VARCHAR2，orafce 支持 VARCHAR2
-- 如果需要相关字段请自行替换，本实验将 NUMBER --> NUMERIC，VARCHAR2 --> VARCHAR，DATE --> TIMESTAMP
CREATE TABLE emp_list_hash
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY LIST (deptno);

-- PostgreSQL and orafce and IvorySQL 创建二级分区主表
CREATE TABLE emp_list_hash_deptno_10      (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_list_hash_deptno_20      (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_list_hash_deptno_30      (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);
CREATE TABLE emp_list_hash_deptno_default (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (sal);

-- 创建二级分区子表
create table emp_list_hash_deptno_10_hash_1 partition of emp_list_hash_deptno_10 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
create table emp_list_hash_deptno_10_hash_2 partition of emp_list_hash_deptno_10 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
create table emp_list_hash_deptno_10_hash_3 partition of emp_list_hash_deptno_10 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
create table emp_list_hash_deptno_10_hash_4 partition of emp_list_hash_deptno_10 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

create table emp_list_hash_deptno_20_hash_1 partition of emp_list_hash_deptno_20 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
create table emp_list_hash_deptno_20_hash_2 partition of emp_list_hash_deptno_20 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
create table emp_list_hash_deptno_20_hash_3 partition of emp_list_hash_deptno_20 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
create table emp_list_hash_deptno_20_hash_4 partition of emp_list_hash_deptno_20 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

create table emp_list_hash_deptno_30_hash_1 partition of emp_list_hash_deptno_30 FOR VALUES WITH (MODULUS 4, REMAINDER 0);
create table emp_list_hash_deptno_30_hash_2 partition of emp_list_hash_deptno_30 FOR VALUES WITH (MODULUS 4, REMAINDER 1);
create table emp_list_hash_deptno_30_hash_3 partition of emp_list_hash_deptno_30 FOR VALUES WITH (MODULUS 4, REMAINDER 2);
create table emp_list_hash_deptno_30_hash_4 partition of emp_list_hash_deptno_30 FOR VALUES WITH (MODULUS 4, REMAINDER 3);

create table emp_list_hash_deptno_default_hash_1 partition of emp_list_hash_deptno_default FOR VALUES WITH (MODULUS 4, REMAINDER 0);
create table emp_list_hash_deptno_default_hash_2 partition of emp_list_hash_deptno_default FOR VALUES WITH (MODULUS 4, REMAINDER 1);
create table emp_list_hash_deptno_default_hash_3 partition of emp_list_hash_deptno_default FOR VALUES WITH (MODULUS 4, REMAINDER 2);
create table emp_list_hash_deptno_default_hash_4 partition of emp_list_hash_deptno_default FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 将二级分区子表绑定到主表上，作为主表的一级分区子表
alter table emp_list_hash attach partition emp_list_hash_deptno_10      for values in (10);
alter table emp_list_hash attach partition emp_list_hash_deptno_20      for values in (20);
alter table emp_list_hash attach partition emp_list_hash_deptno_30      for values in (30);
alter table emp_list_hash attach partition emp_list_hash_deptno_default default;

插入测试数据

insert into emp_list_hash select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss
select * from emp_list_hash PARTITION (deptno_20);
-- PostgreSQL and orafce and IvorySQL
select * from emp_list_hash_deptno_20;

查询分区表的单个子分区里的数据

-- Oracle and openGauss 3.0
select * from emp_list_hash SUBPARTITION (deptno_20_hash_3);
-- PostgreSQL and orafce and IvorySQL
select * from emp_list_hash_deptno_20_hash_3;

哈希 + 范围(Hash-Range)的复合分区

创建哈希 + 范围的复合分区表

-- Oracle and openGauss 3.0
CREATE TABLE emp_hash_range
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY HASH (sal)
SUBPARTITION BY RANGE (hiredate)
  (PARTITION hash_1
    (SUBPARTITION hash_1_hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY')),
     SUBPARTITION hash_1_hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY')),
     SUBPARTITION hash_1_hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY')),
     SUBPARTITION hash_1_hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY')),
     SUBPARTITION hash_1_hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY')),
     SUBPARTITION hash_1_hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY')),
     SUBPARTITION hash_1_hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY')),
     SUBPARTITION hash_1_hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY')),
     SUBPARTITION hash_1_hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY')),
     SUBPARTITION hash_1_hiredate_1988 VALUES LESS THAN (TO_DATE('01-01-1989','DD-MM-YYYY')),
     SUBPARTITION hash_1_hiredate_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hash_2
    (SUBPARTITION hash_2_hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY')),
     SUBPARTITION hash_2_hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY')),
     SUBPARTITION hash_2_hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY')),
     SUBPARTITION hash_2_hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY')),
     SUBPARTITION hash_2_hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY')),
     SUBPARTITION hash_2_hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY')),
     SUBPARTITION hash_2_hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY')),
     SUBPARTITION hash_2_hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY')),
     SUBPARTITION hash_2_hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY')),
     SUBPARTITION hash_2_hiredate_1988 VALUES LESS THAN (TO_DATE('01-01-1989','DD-MM-YYYY')),
     SUBPARTITION hash_2_hiredate_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hash_3
    (SUBPARTITION hash_3_hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY')),
     SUBPARTITION hash_3_hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY')),
     SUBPARTITION hash_3_hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY')),
     SUBPARTITION hash_3_hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY')),
     SUBPARTITION hash_3_hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY')),
     SUBPARTITION hash_3_hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY')),
     SUBPARTITION hash_3_hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY')),
     SUBPARTITION hash_3_hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY')),
     SUBPARTITION hash_3_hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY')),
     SUBPARTITION hash_3_hiredate_1988 VALUES LESS THAN (TO_DATE('01-01-1989','DD-MM-YYYY')),
     SUBPARTITION hash_3_hiredate_max  VALUES LESS THAN (MAXVALUE)),
   PARTITION hash_4
    (SUBPARTITION hash_4_hiredate_1979 VALUES LESS THAN (TO_DATE('01-01-1980','DD-MM-YYYY')),
     SUBPARTITION hash_4_hiredate_1980 VALUES LESS THAN (TO_DATE('01-01-1981','DD-MM-YYYY')),
     SUBPARTITION hash_4_hiredate_1981 VALUES LESS THAN (TO_DATE('01-01-1982','DD-MM-YYYY')),
     SUBPARTITION hash_4_hiredate_1982 VALUES LESS THAN (TO_DATE('01-01-1983','DD-MM-YYYY')),
     SUBPARTITION hash_4_hiredate_1983 VALUES LESS THAN (TO_DATE('01-01-1984','DD-MM-YYYY')),
     SUBPARTITION hash_4_hiredate_1984 VALUES LESS THAN (TO_DATE('01-01-1985','DD-MM-YYYY')),
     SUBPARTITION hash_4_hiredate_1985 VALUES LESS THAN (TO_DATE('01-01-1986','DD-MM-YYYY')),
     SUBPARTITION hash_4_hiredate_1986 VALUES LESS THAN (TO_DATE('01-01-1987','DD-MM-YYYY')),
     SUBPARTITION hash_4_hiredate_1987 VALUES LESS THAN (TO_DATE('01-01-1988','DD-MM-YYYY')),
     SUBPARTITION hash_4_hiredate_1988 VALUES LESS THAN (TO_DATE('01-01-1989','DD-MM-YYYY')),
     SUBPARTITION hash_4_hiredate_max  VALUES LESS THAN (MAXVALUE)));

-- MySQL
... ...

-- PostgreSQL and orafce and IvorySQL 创建主表
-- IvorySQL 支持 NUMBER and VARCHAR2，orafce 支持 VARCHAR2
-- 如果需要相关字段请自行替换，本实验将 NUMBER --> NUMERIC，VARCHAR2 --> VARCHAR，DATE --> TIMESTAMP
CREATE TABLE emp_hash_range
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY HASH (sal);

-- PostgreSQL and orafce and IvorySQL 创建二级分区主表
CREATE TABLE emp_hash_range_hash_1 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (hiredate);
CREATE TABLE emp_hash_range_hash_2 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (hiredate);
CREATE TABLE emp_hash_range_hash_3 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (hiredate);
CREATE TABLE emp_hash_range_hash_4 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY RANGE (hiredate);

-- 创建二级分区子表
create table emp_hash_range_hash_1_1979    partition of emp_hash_range_hash_1 for values from ('1979-01-01 00:00:00+08') to ('1980-01-01 00:00:00+08');
create table emp_hash_range_hash_1_1980    partition of emp_hash_range_hash_1 for values from ('1980-01-01 00:00:00+08') to ('1981-01-01 00:00:00+08');
create table emp_hash_range_hash_1_1981    partition of emp_hash_range_hash_1 for values from ('1981-01-01 00:00:00+08') to ('1982-01-01 00:00:00+08');
create table emp_hash_range_hash_1_1982    partition of emp_hash_range_hash_1 for values from ('1982-01-01 00:00:00+08') to ('1983-01-01 00:00:00+08');
create table emp_hash_range_hash_1_1983    partition of emp_hash_range_hash_1 for values from ('1983-01-01 00:00:00+08') to ('1984-01-01 00:00:00+08');
create table emp_hash_range_hash_1_1984    partition of emp_hash_range_hash_1 for values from ('1984-01-01 00:00:00+08') to ('1985-01-01 00:00:00+08');
create table emp_hash_range_hash_1_1985    partition of emp_hash_range_hash_1 for values from ('1985-01-01 00:00:00+08') to ('1986-01-01 00:00:00+08');
create table emp_hash_range_hash_1_1986    partition of emp_hash_range_hash_1 for values from ('1986-01-01 00:00:00+08') to ('1987-01-01 00:00:00+08');
create table emp_hash_range_hash_1_1987    partition of emp_hash_range_hash_1 for values from ('1987-01-01 00:00:00+08') to ('1988-01-01 00:00:00+08');
create table emp_hash_range_hash_1_1988    partition of emp_hash_range_hash_1 for values from ('1988-01-01 00:00:00+08') to ('1989-01-01 00:00:00+08');
create table emp_hash_range_hash_1_default partition of emp_hash_range_hash_1 default;

create table emp_hash_range_hash_2_1979    partition of emp_hash_range_hash_2 for values from ('1979-01-01 00:00:00+08') to ('1980-01-01 00:00:00+08');
create table emp_hash_range_hash_2_1980    partition of emp_hash_range_hash_2 for values from ('1980-01-01 00:00:00+08') to ('1981-01-01 00:00:00+08');
create table emp_hash_range_hash_2_1981    partition of emp_hash_range_hash_2 for values from ('1981-01-01 00:00:00+08') to ('1982-01-01 00:00:00+08');
create table emp_hash_range_hash_2_1982    partition of emp_hash_range_hash_2 for values from ('1982-01-01 00:00:00+08') to ('1983-01-01 00:00:00+08');
create table emp_hash_range_hash_2_1983    partition of emp_hash_range_hash_2 for values from ('1983-01-01 00:00:00+08') to ('1984-01-01 00:00:00+08');
create table emp_hash_range_hash_2_1984    partition of emp_hash_range_hash_2 for values from ('1984-01-01 00:00:00+08') to ('1985-01-01 00:00:00+08');
create table emp_hash_range_hash_2_1985    partition of emp_hash_range_hash_2 for values from ('1985-01-01 00:00:00+08') to ('1986-01-01 00:00:00+08');
create table emp_hash_range_hash_2_1986    partition of emp_hash_range_hash_2 for values from ('1986-01-01 00:00:00+08') to ('1987-01-01 00:00:00+08');
create table emp_hash_range_hash_2_1987    partition of emp_hash_range_hash_2 for values from ('1987-01-01 00:00:00+08') to ('1988-01-01 00:00:00+08');
create table emp_hash_range_hash_2_1988    partition of emp_hash_range_hash_2 for values from ('1988-01-01 00:00:00+08') to ('1989-01-01 00:00:00+08');
create table emp_hash_range_hash_2_default partition of emp_hash_range_hash_2 default;

create table emp_hash_range_hash_3_1979    partition of emp_hash_range_hash_3 for values from ('1979-01-01 00:00:00+08') to ('1980-01-01 00:00:00+08');
create table emp_hash_range_hash_3_1980    partition of emp_hash_range_hash_3 for values from ('1980-01-01 00:00:00+08') to ('1981-01-01 00:00:00+08');
create table emp_hash_range_hash_3_1981    partition of emp_hash_range_hash_3 for values from ('1981-01-01 00:00:00+08') to ('1982-01-01 00:00:00+08');
create table emp_hash_range_hash_3_1982    partition of emp_hash_range_hash_3 for values from ('1982-01-01 00:00:00+08') to ('1983-01-01 00:00:00+08');
create table emp_hash_range_hash_3_1983    partition of emp_hash_range_hash_3 for values from ('1983-01-01 00:00:00+08') to ('1984-01-01 00:00:00+08');
create table emp_hash_range_hash_3_1984    partition of emp_hash_range_hash_3 for values from ('1984-01-01 00:00:00+08') to ('1985-01-01 00:00:00+08');
create table emp_hash_range_hash_3_1985    partition of emp_hash_range_hash_3 for values from ('1985-01-01 00:00:00+08') to ('1986-01-01 00:00:00+08');
create table emp_hash_range_hash_3_1986    partition of emp_hash_range_hash_3 for values from ('1986-01-01 00:00:00+08') to ('1987-01-01 00:00:00+08');
create table emp_hash_range_hash_3_1987    partition of emp_hash_range_hash_3 for values from ('1987-01-01 00:00:00+08') to ('1988-01-01 00:00:00+08');
create table emp_hash_range_hash_3_1988    partition of emp_hash_range_hash_3 for values from ('1988-01-01 00:00:00+08') to ('1989-01-01 00:00:00+08');
create table emp_hash_range_hash_3_default partition of emp_hash_range_hash_3 default;

create table emp_hash_range_hash_4_1979    partition of emp_hash_range_hash_4 for values from ('1979-01-01 00:00:00+08') to ('1980-01-01 00:00:00+08');
create table emp_hash_range_hash_4_1980    partition of emp_hash_range_hash_4 for values from ('1980-01-01 00:00:00+08') to ('1981-01-01 00:00:00+08');
create table emp_hash_range_hash_4_1981    partition of emp_hash_range_hash_4 for values from ('1981-01-01 00:00:00+08') to ('1982-01-01 00:00:00+08');
create table emp_hash_range_hash_4_1982    partition of emp_hash_range_hash_4 for values from ('1982-01-01 00:00:00+08') to ('1983-01-01 00:00:00+08');
create table emp_hash_range_hash_4_1983    partition of emp_hash_range_hash_4 for values from ('1983-01-01 00:00:00+08') to ('1984-01-01 00:00:00+08');
create table emp_hash_range_hash_4_1984    partition of emp_hash_range_hash_4 for values from ('1984-01-01 00:00:00+08') to ('1985-01-01 00:00:00+08');
create table emp_hash_range_hash_4_1985    partition of emp_hash_range_hash_4 for values from ('1985-01-01 00:00:00+08') to ('1986-01-01 00:00:00+08');
create table emp_hash_range_hash_4_1986    partition of emp_hash_range_hash_4 for values from ('1986-01-01 00:00:00+08') to ('1987-01-01 00:00:00+08');
create table emp_hash_range_hash_4_1987    partition of emp_hash_range_hash_4 for values from ('1987-01-01 00:00:00+08') to ('1988-01-01 00:00:00+08');
create table emp_hash_range_hash_4_1988    partition of emp_hash_range_hash_4 for values from ('1988-01-01 00:00:00+08') to ('1989-01-01 00:00:00+08');
create table emp_hash_range_hash_4_default partition of emp_hash_range_hash_4 default;

-- 将二级分区子表绑定到主表上，作为主表的一级分区子表
alter table emp_hash_range attach partition emp_hash_range_hash_1 for values with (modulus 4, remainder 0);
alter table emp_hash_range attach partition emp_hash_range_hash_2 for values with (modulus 4, remainder 1);
alter table emp_hash_range attach partition emp_hash_range_hash_3 for values with (modulus 4, remainder 2);
alter table emp_hash_range attach partition emp_hash_range_hash_4 for values with (modulus 4, remainder 3);

插入测试数据

insert into emp_hash_range select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss
select * from emp_hash_range PARTITION (hash_2);
-- PostgreSQL and orafce and IvorySQL
select * from emp_hash_range_hash_2;

查询分区表的单个子分区里的数据

-- Oracle and openGauss 3.0
select * from emp_hash_range SUBPARTITION (hash_2_hiredate_1981);
-- PostgreSQL and orafce and IvorySQL
select * from emp_hash_range_hash_2_1981;

哈希 + 列表(Hash-List)的复合分区

创建哈希 + 列表的复合分区表

-- Oracle and openGauss 3.0
CREATE TABLE emp_hash_list
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY HASH (sal)
SUBPARTITION BY LIST (deptno)
  (PARTITION hash_1
    (SUBPARTITION hash_1_deptno_10 VALUES (10),
     SUBPARTITION hash_1_deptno_20 VALUES (20),
     SUBPARTITION hash_1_deptno_30 VALUES (30),
     SUBPARTITION hash_1_deptno_default VALUES (DEFAULT)),
   PARTITION hash_2
    (SUBPARTITION hash_2_deptno_10 VALUES (10),
     SUBPARTITION hash_2_deptno_20 VALUES (20),
     SUBPARTITION hash_2_deptno_30 VALUES (30),
     SUBPARTITION hash_2_deptno_default VALUES (DEFAULT)),
   PARTITION hash_3
    (SUBPARTITION hash_3_deptno_10 VALUES (10),
     SUBPARTITION hash_3_deptno_20 VALUES (20),
     SUBPARTITION hash_3_deptno_30 VALUES (30),
     SUBPARTITION hash_3_deptno_default VALUES (DEFAULT)),
   PARTITION hash_4
    (SUBPARTITION hash_4_deptno_10 VALUES (10),
     SUBPARTITION hash_4_deptno_20 VALUES (20),
     SUBPARTITION hash_4_deptno_30 VALUES (30),
     SUBPARTITION hash_4_deptno_default VALUES (DEFAULT)));

-- MySQL
... ...

-- PostgreSQL and orafce and IvorySQL 创建主表
-- IvorySQL 支持 NUMBER and VARCHAR2，orafce 支持 VARCHAR2
-- 如果需要相关字段请自行替换，本实验将 NUMBER --> NUMERIC，VARCHAR2 --> VARCHAR，DATE --> TIMESTAMP
CREATE TABLE emp_hash_list
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY HASH (sal);

-- PostgreSQL and orafce and IvorySQL 创建二级分区主表
CREATE TABLE emp_hash_list_hash_1 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_hash_list_hash_2 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_hash_list_hash_3 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);
CREATE TABLE emp_hash_list_hash_4 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY LIST (deptno);

-- 创建二级分区子表
create table emp_hash_list_hash_1_deptno_10      partition of emp_hash_list_hash_1 for values in (10);
create table emp_hash_list_hash_1_deptno_20      partition of emp_hash_list_hash_1 for values in (20);
create table emp_hash_list_hash_1_deptno_30      partition of emp_hash_list_hash_1 for values in (30);
create table emp_hash_list_hash_1_deptno_default partition of emp_hash_list_hash_1 default;

create table emp_hash_list_hash_2_deptno_10      partition of emp_hash_list_hash_2 for values in (10);
create table emp_hash_list_hash_2_deptno_20      partition of emp_hash_list_hash_2 for values in (20);
create table emp_hash_list_hash_2_deptno_30      partition of emp_hash_list_hash_2 for values in (30);
create table emp_hash_list_hash_2_deptno_default partition of emp_hash_list_hash_2 default;

create table emp_hash_list_hash_3_deptno_10      partition of emp_hash_list_hash_3 for values in (10);
create table emp_hash_list_hash_3_deptno_20      partition of emp_hash_list_hash_3 for values in (20);
create table emp_hash_list_hash_3_deptno_30      partition of emp_hash_list_hash_3 for values in (30);
create table emp_hash_list_hash_3_deptno_default partition of emp_hash_list_hash_3 default;

create table emp_hash_list_hash_4_deptno_10      partition of emp_hash_list_hash_4 for values in (10);
create table emp_hash_list_hash_4_deptno_20      partition of emp_hash_list_hash_4 for values in (20);
create table emp_hash_list_hash_4_deptno_30      partition of emp_hash_list_hash_4 for values in (30);
create table emp_hash_list_hash_4_deptno_default partition of emp_hash_list_hash_4 default;

-- 将二级分区子表绑定到主表上，作为主表的一级分区子表
alter table emp_hash_list attach partition emp_hash_list_hash_1 for values with (modulus 4, remainder 0);
alter table emp_hash_list attach partition emp_hash_list_hash_2 for values with (modulus 4, remainder 1);
alter table emp_hash_list attach partition emp_hash_list_hash_3 for values with (modulus 4, remainder 2);
alter table emp_hash_list attach partition emp_hash_list_hash_4 for values with (modulus 4, remainder 3);

插入测试数据

insert into emp_hash_list select * from emp;

查询分区表的单个分区里的数据

-- Oracle and openGauss
select * from emp_hash_list PARTITION (hash_2);
-- PostgreSQL and orafce and IvorySQL
select * from emp_hash_list_hash_2;

查询分区表的单个子分区里的数据

-- Oracle and openGauss 3.0
select * from emp_hash_list SUBPARTITION (hash_2_deptno_10);
-- PostgreSQL and orafce and IvorySQL
select * from emp_hash_list_hash_2_deptno_10;

哈希 + 哈希(Hash-Hash)的复合分区

创建哈希 + 哈希的复合分区表

-- Oracle and openGauss 3.0
CREATE TABLE emp_hash_hash
    ( empno        NUMBER(4,0)
    , ename        VARCHAR2(10)
    , job          VARCHAR2(9)
    , mgr          NUMBER(4,0)
    , hiredate     DATE
    , sal          NUMBER(7,2)
    , comm         NUMBER(7,2)
    , deptno       NUMBER(2,0)
    ) 
PARTITION BY HASH (sal)
SUBPARTITION BY HASH (mgr)
  (PARTITION hash_1
    (SUBPARTITION hash_1_hash_1,
     SUBPARTITION hash_1_hash_2,
     SUBPARTITION hash_1_hash_3,
     SUBPARTITION hash_1_hash_4),
   PARTITION hash_2
    (SUBPARTITION hash_2_hash_1,
     SUBPARTITION hash_2_hash_2,
     SUBPARTITION hash_2_hash_3,
     SUBPARTITION hash_2_hash_4),
   PARTITION hash_3
    (SUBPARTITION hash_3_hash_1,
     SUBPARTITION hash_3_hash_2,
     SUBPARTITION hash_3_hash_3,
     SUBPARTITION hash_3_hash_4),
   PARTITION hash_4
    (SUBPARTITION hash_4_hash_1,
     SUBPARTITION hash_4_hash_2,
     SUBPARTITION hash_4_hash_3,
     SUBPARTITION hash_4_hash_4));

-- MySQL
... ...

-- PostgreSQL and orafce and IvorySQL 创建主表
-- IvorySQL 支持 NUMBER and VARCHAR2，orafce 支持 VARCHAR2
-- 如果需要相关字段请自行替换，本实验将 NUMBER --> NUMERIC，VARCHAR2 --> VARCHAR，DATE --> TIMESTAMP
CREATE TABLE emp_hash_hash
    ( empno        NUMERIC(4,0)
    , ename        VARCHAR(10)
    , job          VARCHAR(9)
    , mgr          NUMERIC(4,0)
    , hiredate     TIMESTAMP
    , sal          NUMERIC(7,2)
    , comm         NUMERIC(7,2)
    , deptno       NUMERIC(2,0)
    ) 
PARTITION BY HASH (sal);

-- PostgreSQL and orafce and IvorySQL 创建二级分区主表
CREATE TABLE emp_hash_hash_hash_1 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (mgr);
CREATE TABLE emp_hash_hash_hash_2 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (mgr);
CREATE TABLE emp_hash_hash_hash_3 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (mgr);
CREATE TABLE emp_hash_hash_hash_4 (empno NUMERIC(4,0),ename VARCHAR(10),job VARCHAR(9),mgr NUMERIC(4,0),hiredate TIMESTAMP,sal NUMERIC(7,2),comm NUMERIC(7,2),deptno NUMERIC(2,0)) PARTITION BY HASH (mgr);

-- 创建二级分区子表
create table emp_hash_hash_hash_1_hash_1 partition of emp_hash_hash_hash_1 for values with (modulus 4, remainder 0);
create table emp_hash_hash_hash_1_hash_2 partition of emp_hash_hash_hash_1 for values with (modulus 4, remainder 1);
create table emp_hash_hash_hash_1_hash_3 partition of emp_hash_hash_hash_1 for values with (modulus 4, remainder 2);
create table emp_hash_hash_hash_1_hash_4 partition of emp_hash_hash_hash_1 for values with (modulus 4, remainder 3);

create table emp_hash_hash_hash_2_hash_1 partition of emp_hash_hash_hash_2 for values with (modulus 4, remainder 0);
create table emp_hash_hash_hash_2_hash_2 partition of emp_hash_hash_hash_2 for values with (modulus 4, remainder 1);
create table emp_hash_hash_hash_2_hash_3 partition of emp_hash_hash_hash_2 for values with (modulus 4, remainder 2);
create table emp_hash_hash_hash_2_hash_4 partition of emp_hash_hash_hash_2 for values with (modulus 4, remainder 3);

create table emp_hash_hash_hash_3_hash_1 partition of emp_hash_hash_hash_3 for values with (modulus 4, remainder 0);
create table emp_hash_hash_hash_3_hash_2 partition of emp_hash_hash_hash_3 for values with (modulus 4, remainder 1);
create table emp_hash_hash_hash_3_hash_3 partition of emp_hash_hash_hash_3 for values with (modulus 4, remainder 2);
create table emp_hash_hash_hash_3_hash_4 partition of emp_hash_hash_hash_3 for values with (modulus 4, remainder 3);

create table emp_hash_hash_hash_4_hash_1 partition of emp_hash_hash_hash_4 for values with (modulus 4, remainder 0);
create table emp_hash_hash_hash_4_hash_2 partition of emp_hash_hash_hash_4 for values with (modulus 4, remainder 1);
create table emp_hash_hash_hash_4_hash_3 partition of emp_hash_hash_hash_4 for values with (modulus 4, remainder 2);
create table emp_hash_hash_hash_4_hash_4 partition of emp_hash_hash_hash_4 for values with (modulus 4, remainder 3);

-- 将二级分区子表绑定到主表上，作为主表的一级分区子表
alter table emp_hash_hash attach partition emp_hash_hash_hash_1 for values with (modulus 4, remainder 0);
alter table emp_hash_hash attach partition emp_hash_hash_hash_2 for values with (modulus 4, remainder 1);
alter table emp_hash_hash attach partition emp_hash_hash_hash_3 for values with (modulus 4, remainder 2);
alter table emp_hash_hash attach partition emp_hash_hash_hash_4 for values with (modulus 4, remainder 3);

插入测试数据

insert into emp_hash_hash select * from emp;

-- openGauss 3.0 插入数据报错
openGauss=# insert into emp_hash_hash select * from emp;
ERROR:  inserted partition key does not map to any table partition

查询分区表的单个分区里的数据

-- Oracle and openGauss
select * from emp_hash_hash PARTITION (hash_1);
-- PostgreSQL and orafce and IvorySQL
select * from emp_hash_hash_hash_1;

查询分区表的单个子分区里的数据

-- Oracle and openGauss 3.0
select * from emp_hash_hash SUBPARTITION (hash_1_hash_1);
-- PostgreSQL and orafce and IvorySQL
select * from emp_hash_hash_hash_1_hash_1;

问题：
- openGauss 3.0 对 Hash-Hash 表插入数据报错。

最后修改时间：2022-04-10 19:45:11

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

文章被以下合辑收录

PostgreSQL运维笔记（共8篇）

总结一些PostgreSQL小知识

几个开源数据库对比 Oracle 分区表--分区表的创建和简单查询

分区表支持情况

分区表创建示例

范围分区

列表分区

哈希(散列)分区

间隔(INTERVAL)分区

范围 + 范围(Range-Range)的复合分区

范围 + 列表(Range-List)的复合分区

范围 + 哈希(Range-Hash)的复合分区

列表 + 范围(List-Range)的复合分区

列表 + 列表(List-List)的复合分区

列表 + 哈希(List-Hash)的复合分区

哈希 + 范围(Hash-Range)的复合分区

哈希 + 列表(Hash-List)的复合分区

哈希 + 哈希(Hash-Hash)的复合分区

文章被以下合辑收录

评论