几个开源数据库对比 oracle 分区表-m6米乐安卓版下载

分区类型	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，参考官方文档：，关于 mogdb 2.1 创建子分区参考官方文档：。
  • 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 表插入数据报错。