| 维护 | pgsql 13.3 | og 2.1 | og 3.0 | mysql 8.0 | ivorysql 1.2 |
|---|---|---|---|---|---|
| 增加分区 | y | y | y | - | y |
| 删除分区 | y | y | y | - | y |
| 截断分区 | y | y | y | - | y |
| 合并分区 | n | y | y | - | n |
| 拆分分区 | n | y | y | - | n |
| 交换分区 | n | y | y | - | n |
| 移动分区 | y | y | y | - | y |
| 修改分区名称 | y | y | y | - | y |
- 备注：
- opengauss 的交换分区测试失败，不晓得啥原因，看文档是支持交换分区。
- postgresql and ivorysql 对虽然不支持交换分区，单是可以通过分区的解绑和绑定来很简单的变相实现。
- 本文均是测试结果，仅供参考
-- oracle
select table_name,partition_name,num_rows,high_value from dba_tab_partitions where table_name='emp_range_range';
table_name partition_name num_rows high_value
----------------- ---------------- ---------- ------------------------------------------------------------------------------------
emp_range_range hiredate_max maxvalue
emp_range_range hiredate_1987 to_date(' 1988-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
emp_range_range hiredate_1986 to_date(' 1987-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
emp_range_range hiredate_1985 to_date(' 1986-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
emp_range_range hiredate_1984 to_date(' 1985-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
emp_range_range hiredate_1983 to_date(' 1984-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
emp_range_range hiredate_1982 to_date(' 1983-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
emp_range_range hiredate_1981 to_date(' 1982-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
emp_range_range hiredate_1980 to_date(' 1981-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
emp_range_range hiredate_1979 to_date(' 1980-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss', 'nls_calendar=gregorian')
10 rows selected.
select table_name,partition_name,subpartition_name,num_rows,high_value from dba_tab_subpartitions where table_name='emp_range_range';
table_name partition_name subpartition_name num_rows high_value
------------------------------ ------------------------------ ------------------------------ ---------- -----------------
emp_range_range hiredate_1980 hiredate_1980_min 1000
emp_range_range hiredate_1980 hiredate_1980_1000 2000
emp_range_range hiredate_1980 hiredate_1980_2000 3000
emp_range_range hiredate_1980 hiredate_1980_3000 4000
emp_range_range hiredate_1980 hiredate_1980_4000 5000
emp_range_range hiredate_1980 hiredate_1980_max maxvalue
... ...
emp_range_range hiredate_1987 hiredate_1987_min 1000
emp_range_range hiredate_1987 hiredate_1987_1000 2000
emp_range_range hiredate_1987 hiredate_1987_2000 3000
emp_range_range hiredate_1987 hiredate_1987_3000 4000
emp_range_range hiredate_1987 hiredate_1987_4000 5000
emp_range_range hiredate_1987 hiredate_1987_max maxvalue
emp_range_range hiredate_max hiredate_max_min 1000
emp_range_range hiredate_max hiredate_max_1000 2000
emp_range_range hiredate_max hiredate_max_2000 3000
emp_range_range hiredate_max hiredate_max_3000 4000
emp_range_range hiredate_max hiredate_max_4000 5000
emp_range_range hiredate_max hiredate_max_max maxvalue
-- postgresql and ivorysql
\d emp_range_range --只能查到一级分区，二级分区需要继续使用 \d 查看
postgres=# \d emp_range_range
partitioned table "public.emp_range_range"
column | type | collation | nullable | default | storage | stats target | description
---------- ----------------------- ----------- ---------- --------- ---------- -------------- -------------
empno | numeric(4,0) | | | | main | |
ename | character varying(10) | | | | extended | |
job | character varying(9) | | | | extended | |
mgr | numeric(4,0) | | | | main | |
hiredate | date | | | | plain | |
sal | numeric(7,2) | | | | main | |
comm | numeric(7,2) | | | | main | |
deptno | numeric(2,0) | | | | main | |
partition key: range (hiredate)
partitions: emp_range_range_1979 for values from ('1979-01-01') to ('1980-01-01'), partitioned,
emp_range_range_1980 for values from ('1980-01-01') to ('1981-01-01'), partitioned,
emp_range_range_1981 for values from ('1981-01-01') to ('1982-01-01'), partitioned,
emp_range_range_1982 for values from ('1982-01-01') to ('1983-01-01'), partitioned,
emp_range_range_1983 for values from ('1983-01-01') to ('1984-01-01'), partitioned,
emp_range_range_1984 for values from ('1984-01-01') to ('1985-01-01'), partitioned,
emp_range_range_1985 for values from ('1985-01-01') to ('1986-01-01'), partitioned,
emp_range_range_1986 for values from ('1986-01-01') to ('1987-01-01'), partitioned,
emp_range_range_1987 for values from ('1987-01-01') to ('1988-01-01'), partitioned,
emp_range_range_1988 for values from ('1988-01-01') to ('1989-01-01'), partitioned,
emp_range_range_default default, partitioned
postgres=# \d emp_range_range_1979
partitioned table "public.emp_range_range_1979"
column | type | collation | nullable | default | storage | stats target | description
---------- ----------------------- ----------- ---------- --------- ---------- -------------- -------------
empno | numeric(4,0) | | | | main | |
ename | character varying(10) | | | | extended | |
job | character varying(9) | | | | extended | |
mgr | numeric(4,0) | | | | main | |
hiredate | date | | | | plain | |
sal | numeric(7,2) | | | | main | |
comm | numeric(7,2) | | | | main | |
deptno | numeric(2,0) | | | | main | |
partition of: emp_range_range for values from ('1979-01-01') to ('1980-01-01')
partition constraint: ((hiredate is not null) and (hiredate >= '1979-01-01'::date) and (hiredate < '1980-01-01'::date))
partition key: range (sal)
partitions: emp_range_range_1979_1000 for values from (1000.00) to (2000.00),
emp_range_range_1979_2000 for values from (2000.00) to (3000.00),
emp_range_range_1979_3000 for values from (3000.00) to (4000.00),
emp_range_range_1979_4000 for values from (4000.00) to (5000.00),
emp_range_range_1979_max for values from (5000.00) to (maxvalue),
emp_range_range_1979_min for values from (minvalue) to (1000.00)
-- opengauss
\d emp_range_range --只能看到分区个数
opengauss=# \d emp_range_range
table "public.emp_range_range"
column | type | modifiers | storage | stats target | description
---------- -------------------------------- ----------- ---------- -------------- -------------
empno | numeric(4,0) | | main | |
ename | character varying(10) | | extended | |
job | character varying(9) | | extended | |
mgr | numeric(4,0) | | main | |
hiredate | timestamp(0) without time zone | | plain | |
sal | numeric(7,2) | | main | |
comm | numeric(7,2) | | main | |
deptno | numeric(2,0) | | main | |
partition by range(hiredate) subpartition by range(sal)
number of partitions: 10 (view pg_partition to check each partition range.)
number of subpartitions: 60 (view pg_partition to check each subpartition range.)
has oids: no
options: orientation=row, compression=no
-- opengauss 的 pg_partition 视图查询一级分区还可以，如果想一次性查询二级分区(subpartition)比较费劲，此时可以使用 compare_tool 工具
opengauss=# select relname,parttype,partstrategy,parentid,boundaries from pg_partition where parentid=(select parentid from pg_partition where relname ='emp_range_range');
relname | parttype | partstrategy | parentid | boundaries
----------- ---------- -------------- ---------- ------------
emp_list | r | l | 25617 |
deptno_10 | p | l | 25617 | {10}
deptno_20 | p | l | 25617 | {20}
deptno_30 | p | l | 25617 | {30}
compare_tool 工具
compare_tool 是一个兼容工具集合，旨在为从其他异构数据库，迁移到 opengauss 之后的系统，创建必要的函数，以及系统视图的兼容。为后续的系统运维与应用改造提供便利。
compare_tool 详情参考：
- 以下使用 compare_tool 创建 oracle 兼容视图
tar -xvf compat-tools-v2022.03.23.tar
gsql -d postgres -p 1412 -u omm -r -f compat-tools/oracle_views.sql
gsql -d postgres -p 1412 -u omm -r
opengauss=# select table_name,partition_name,subpartition_name,num_rows,high_value from dba_tab_subpartitions where table_name='emp_range_range';
table_name | partition_name | subpartition_name | num_rows | high_value
----------------- ---------------- -------------------- ---------- ------------
emp_range_range | hiredate_1979 | hiredate_1979_min | 0 | 1000
emp_range_range | hiredate_1979 | hiredate_1979_1000 | 0 | 2000
emp_range_range | hiredate_1979 | hiredate_1979_2000 | 0 | 3000
emp_range_range | hiredate_1979 | hiredate_1979_3000 | 0 | 4000
emp_range_range | hiredate_1979 | hiredate_1979_4000 | 0 | 5000
emp_range_range | hiredate_1979 | hiredate_1979_max | 0 | maxvalue
... ...
-- oracle and opengauss
alter table emp_range drop partition hiredate_1979;
-- postgresql and ivorysql 删除子表就是删除分区
drop table emp_range_hiredate_2020;
- oracle 和 opengauss 最后一个分区或子分区不允许删除，postgresql and ivorysql 可以删除最后一个分区或子分区
-- oracle
ora-14083: cannot drop the only partition of a partitioned table
ora-14629: cannot drop the only subpartition of a partition
-- opengauss
error: cannot drop the only partition of a partitioned table
error: cannot drop the only subpartition of a partitioned table
- oracle 和 opengauss 不允许删除哈希分区表的任何分区或子分区，报错信息如下
-- oracle
ora-14255: table is not partitioned by range, list, composite range or composite list method
ora-14206: table is not subpartitioned by list or range methods
-- opengauss
error: droping hash partition is unsupported.
error: un-support feature
detail: the syntax is unsupported for hash subpartition
- postgresql 和 ivorysql 虽然可以删除哈希分区表的分区，但是后续插入的数据还会分配到已经删除的分区中，当发现子分区不存在会报出以下错误信息
postgres=# insert into emp_hash (sal) values (2225);
error: no partition of relation "emp_hash" found for row
detail: partition key of the failing row contains (sal) = (2225.00).
- oracle 不允许删除间隔分区表手工创建的最后一个分区，报错信息如下
ora-14758: last partition in the range section cannot be dropped
解决方法：将间隔分区表转为普通分区表
-- 设置自动分区为普通范围分区
sql> alter table emp_interval set interval();
-- 查看修改后的分区信息
sql> select table_name,partition_name,num_rows,high_value,interval from dba_tab_partitions where table_name='emp_interval';
-- 删除手工创建的分区
sql> alter table emp_interval drop partition sal_p1;
-- 设置普通分区为自动分区
sql> alter table emp_interval set interval(1000);
- 添加分区的方式
-- oracle and opengauss
alter table emp_range add partition hiredate_1988 values less than (to_date('1989-01-01','yyyy-dd-mm'));
alter table emp_list add partition deptno_40 values (40);
-- postgresql and ivorysql 创建子表进行增加分区
create table emp_range_hiredate_1989 partition of emp_range for values from ('1989-01-01 00:00:00 08') to ('1990-01-01 00:00:00 08');
create table emp_list_deptno_40 partition of emp_list for values in (40);
- 如果 postgresql 和 ivorysql 在 range 和 list 类型的分区表的 default 分区中存在要添加的分区数据时
-- 解绑 default 分区
alter table emp_range detach partition emp_range_hiredate_default;
-- 创建新分区
create table emp_range_hiredate_2020 partition of emp_range for values from ('2020-01-01 00:00:00 08') to ('2021-01-01 00:00:00 08');
-- default 分区数据转移到 新分区
insert into emp_range_hiredate_2020 select * from emp_range_hiredate_default where hiredate >= ('2020-01-01 00:00:00 08') and hiredate < ('2021-01-01 00:00:00 08');
delete from emp_range_hiredate_default where hiredate >= ('2020-01-01 00:00:00 08') and hiredate < ('2021-01-01 00:00:00 08');
-- 重新绑定 default 分区
alter table emp_range attach partition emp_range_hiredate_default default;
-- 解绑 default 分区
alter table emp_list detach partition emp_list_deptno_default;
-- 创建新分区
create table emp_list_deptno_50 partition of emp_list for values in (50);
-- default 分区数据转移到 新分区
insert into emp_list_deptno_50 select * from emp_list_deptno_default where deptno = 50;
delete from emp_list_deptno_default where deptno = 50;
-- 重新绑定 default 分区
alter table emp_list attach partition emp_list_deptno_default default;
- range 分区表存在 maxvalue 时添加分区的报错信息
-- oracle
sql> alter table emp_range add partition hiredate_1988 values less than (to_date('1989-01-01','yyyy-dd-mm'));
ora-14074: partition bound must collate higher than that of the last partition
-- opengauss
opengauss=# alter table emp_range add partition hiredate_1988 values less than (to_date('1989-01-01','yyyy-dd-mm'));
error: upper boundary of adding partition must overtop last existing partition
- list 分区表存在 default 时添加分区的报错信息
-- oracle
sql> alter table emp_list add partition deptno_40 values (40);
ora-14323: cannot add partition when default partition exists
-- opengauss 3.0，opengauss 2.1 不支持 default 分区
opengauss=# alter table emp_list add partition deptno_40 values (40);
error: list boundary of adding partition must not overlap with existing partition
- postgresql 在 range 和 list 类型的分区表的 default 分区中存在要添加的分区数据时的报错信息
postgres=# insert into emp_range (hiredate) values ('2020-02-02');
postgres=# create table emp_range_hiredate_2020 partition of emp_range for values from ('2020-01-01 00:00:00 08') to ('2021-01-01 00:00:00 08');
error: updated partition constraint for default partition "emp_range_hiredate_default" would be violated by some row
postgres=# insert into emp_list (deptno) values (50);
postgres=# create table emp_list_deptno_50 partition of emp_list for values in (50);
error: updated partition constraint for default partition "emp_list_deptno_default" would be violated by some row
- oracle 支持为 hash 分区表添加分区，添加分区完成后会进行数据重分布
sql> analyze table emp_hash compute statistics;
sql> select table_name,partition_name,num_rows,high_value from dba_tab_partitions where table_name='emp_hash';
table_name partition_name num_rows high_value
------------ ----------------- ---------- ------------
emp_hash emp_hash_4 4
emp_hash emp_hash_3 2
emp_hash emp_hash_2 4
emp_hash emp_hash_1 4
sql> alter table emp_hash add partition emp_hash_5;
sql> analyze table emp_hash compute statistics;
sql> select table_name,partition_name,num_rows,high_value from dba_tab_partitions where table_name='emp_hash';
table_name partition_name num_rows high_value
------------ ---------------- ---------- -----------
emp_hash emp_hash_5 2
emp_hash emp_hash_4 4
emp_hash emp_hash_3 2
emp_hash emp_hash_2 4
emp_hash emp_hash_1 2
- opengauss 不支持为 hash 分区表添加分区
opengauss=# alter table emp_hash add partition emp_hash_5;
error: syntax error at or near ";"
- postgresql and ivorysql 的 hash 分区表问题
-- 哈希分区有 modulus 总分区个数限制，当分区个数 remainder 达到 modulus 上限时不能添加分区
postgres=# create table emp_hash_5 partition of emp_hash for values with (modulus 4, remainder 4);
error: remainder for hash partition must be less than modulus
-- 如果分区个数与 modulus 不相等，则插入数据会存在报错现象，
-- postgresql 认为当前表的分区个数与 modulus 相等，插入数据时会计算进去，不管真实存在多少个分区。
create table emp_hash (empno numeric(4,0),ename varchar(10),job varchar(9),mgr numeric(4,0),hiredate date,sal numeric(7,2),comm numeric(7,2),deptno numeric(2,0)) partition by hash (sal);
create table emp_hash_1 partition of emp_hash for values with (modulus 6, remainder 0);
create table emp_hash_2 partition of emp_hash for values with (modulus 6, remainder 1);
create table emp_hash_3 partition of emp_hash for values with (modulus 6, remainder 2);
create table emp_hash_4 partition of emp_hash for values with (modulus 6, remainder 3);
postgres=# insert into emp_hash select * from emp;
error: no partition of relation "emp_hash" found for row
detail: partition key of the failing row contains (sal) = (800.00).
- interval 分区表不支持添加分区
sql> alter table emp_interval_date_year add partition hiredate_p2 values less than (to_date('1978-01-01','yyyy-dd-mm'));
ora-14760: add partition is not permitted on interval partitioned objects
opengauss=# alter table emp_interval_date_year add partition hiredate_p2 values less than (to_date('1978-01-01','yyyy-dd-mm'));
error: can not add partition against interval partitioned table
- 截断分区的方式
-- oracle and opengauss
alter table emp_range truncate partition hiredate_1979;
alter table emp_range_range truncate subpartition hiredate_1979_3000;
-- postgresql and ivorysql 创建子表进行 truncate
truncate table emp_range_hiredate_1979;
truncate table emp_range_range_1979_3000;
- 合并分区的方式
-- oracle and opengauss
-- opengauss 不支持合并 list/hash 分区表，不支持合并组合分区表，不支持合并 subpartition
alter table emp_range merge partitions hiredate_1979,hiredate_1980 into partition hiredate_1980;
alter table emp_range_range merge subpartitions hiredate_1980_min,hiredate_1980_1000 into subpartition hiredate_1980_1000;
alter table emp_list merge partitions deptno_10,deptno_20 into partition deptno_10_20;
alter table emp_interval_date_year merge partitions sys_p1,sys_p2 into partition sys_p2;
- opengauss 不支持合并 list/hash 分区表
opengauss=# alter table emp_list merge partitions deptno_10,deptno_20 into partition deptno_10_20;
error: can not merge list/hash partition table
- opengauss 不支持合并组合分区表
opengauss=# alter table emp_range_range merge partitions hiredate_1979,hiredate_1980 into partition hiredate_1980;
error: un-support feature
detail: for subpartition table, merge partitions is not yet supported.
- opengauss 不支持合并 subpartition
opengauss=# alter table emp_range_range merge subpartitions hiredate_1980_min,hiredate_1980_1000 into subpartition hiredate_1980_1000;
error: syntax error at or near "subpartitions"
line 1: alter table emp_range_range merge subpartitions hiredate_198...
^
- hash 分区表不支持合并
-- oracle
sql> alter table emp_hash merge partitions emp_hash_3,emp_hash_4 into partition emp_hash_3;
ora-14255: table is not partitioned by range, list, composite range or composite list method
-- opengauss
opengauss=# alter table emp_hash merge partitions emp_hash_3,emp_hash_4 into partition emp_hash_3;
error: can not merge list/hash partition table
- 拆分分区的方式
-- oracle
alter table emp_range split partition hiredate_1980 at (to_date('01-01-1980','dd-mm-yyyy')) into (partition hiredate_1979,partition hiredate_1980);
alter table emp_range split partition hiredate_max at (to_date('01-01-1989','dd-mm-yyyy')) into (partition hiredate_1988,partition hiredate_max);
alter table emp_list split partition deptno_10_20 values(10) into (partition deptno_10, partition deptno_20);
alter table emp_interval split partition sys_p135 at (3000) into (partition emp_interval_3000,partition emp_interval_4000);
alter table emp_interval_date_year split partition sys_p138 at (to_date('1981-01-01','yyyy-mm-dd')) into (partition sys_p137,partition sys_p138);
alter table emp_range_list split partition hiredate_max at (to_date('1989-01-01','yyyy-mm-dd')) into (partition hiredate_1988,partition hiredate_max);
alter table emp_range_list split subpartition hiredate_1988_deptno_default values(40) into (subpartition hiredate_1988_deptno_40,subpartition hiredate_1988_deptno_default);
- opengauss 拆分分区，新分区名不能与被拆的分区名相同
opengauss=# alter table emp_range split partition hiredate_max at (to_date('01-01-1989','dd-mm-yyyy')) into (partition hiredate_1988,partition hiredate_max);
error: resulting partition "hiredate_max" name conflicts with that of an existing partition
opengauss=# alter table emp_range split partition hiredate_max at (to_date('01-01-1989','dd-mm-yyyy')) into (partition hiredate_1988,partition hiredate_max2);
alter table
- opengauss 不支持拆分 list 分区
opengauss=# alter table emp_list split partition deptno_10_20 values(10) into (partition deptno_10, partition deptno_20);
error: syntax error at or near "values"
line 1: alter table emp_list split partition deptno_10_20 values(10)...
^
opengauss=# alter table emp_list split partition deptno_10_20 at (10) into (partition deptno_10, partition deptno_20);
error: can not split list/hash partition table
- opengauss 不支持拆分组合分区表
opengauss=# alter table emp_range_list split partition hiredate_max at (to_date('1989-01-01','yyyy-mm-dd')) into (partition hiredate_1988,partition hiredate_max);
error: un-support feature
detail: for subpartition table, split partition is not supported yet.
- opengauss 居然支持拆分 subpartition，好意外，而且拆分的子分区还是 list 分区，名称还是冲突的，看来对 subpartition 是 list 分区有特殊对待？
opengauss=# alter table emp_range_list split subpartition hiredate_1987_deptno_default values(40) into (subpartition hiredate_1987_deptno_40,subpartition hiredate_1987_deptno_default);
alter table
opengauss=# alter table emp_range_range split subpartition hiredate_1987_max at (6000) into (subpartition hiredate_1987_5000, subpartition hiredate_1987_max);
error: resulting subpartition "hiredate_1987_max" name conflicts with that of an existing subpartition
opengauss=# alter table emp_range_range split subpartition hiredate_1987_max at (6000) into (subpartition hiredate_1987_5000, subpartition hiredate_1987_max2);
alter table
- oracle 的分区交换
-- 创建一个空的普通表
sql> create table hiredate_1981_tt as select * from emp where rownum=0;
-- 执行分区交换
sql> alter table emp_range exchange partition hiredate_1981 with table hiredate_1981_tt;
-- 查询交换后的普通表和分区里的数据
sql> select * from emp_range partition(hiredate_1981);
no rows selected
sql> select * from hiredate_1981;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7499 allen salesman 7698 1981-02-20 00:00:00 1600 300 30
7521 ward salesman 7698 1981-02-22 00:00:00 1250 500 30
7566 jones manager 7839 1981-04-02 00:00:00 2975 20
7654 martin salesman 7698 1981-09-28 00:00:00 1250 1400 30
7698 blake manager 7839 1981-05-01 00:00:00 2850 30
7782 clark manager 7839 1981-06-09 00:00:00 2450 10
7839 king president 1981-11-17 00:00:00 5000 10
7844 turner salesman 7698 1981-09-08 00:00:00 1500 0 30
7900 james clerk 7698 1981-12-03 00:00:00 950 30
7902 ford analyst 7566 1981-12-03 00:00:00 3000 20
- oracle 的组合分区交换一级分区
-- 创建一个空的分区表，分区内容为原分区表的二级分区
create table hiredate_1982_tt
( 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)
(partition hiredate_1982_min values less than (1000),
partition hiredate_1982_1000 values less than (2000),
partition hiredate_1982_2000 values less than (3000),
partition hiredate_1982_3000 values less than (4000),
partition hiredate_1982_4000 values less than (5000),
partition hiredate_1982_max values less than (maxvalue));
-- 执行分区交换
sql> alter table emp_range_range exchange partition hiredate_1982 with table hiredate_1982_tt;
-- 查询交换后的普通表和分区里的数据
sql> select * from hiredate_1982_tt;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7934 miller clerk 7782 1982-01-23 00:00:00 1300 10
- oracle 的组合分区交换一个 subpartition
-- 创建一个空的普通表
sql> create table hiredate_1981_2000_tt as select * from emp where rownum=0;
-- 执行分区交换
sql> alter table emp_range_range exchange subpartition hiredate_1981_2000 with table hiredate_1981_2000_tt;
-- 查询交换后的普通表和分区里的数据
sql> select * from hiredate_1981_2000_tt;
empno ename job mgr hiredate sal comm deptno
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7566 jones manager 7839 1981-04-02 00:00:00 2975 20
7698 blake manager 7839 1981-05-01 00:00:00 2850 30
7782 clark manager 7839 1981-06-09 00:00:00 2450 10
- opengauss 上没有测试成功，不晓得啥原因
opengauss=# create table hiredate_1981_tt as select * from emp where rownum=0;
insert 0 0
opengauss=# alter table emp_range exchange partition hiredate_1981 with table hiredate_1981_tt;
error: syntax error at or near "hiredate_1981"
line 1: alter table emp_range exchange partition hiredate_1981 with ...
^
opengauss=# alter table emp_range_range exchange subpartition hiredate_1981_2000 with table hiredate_1981_2000_tt;
error: syntax error at or near "subpartition hiredate_1981_2000"
line 1: alter table emp_range_range exchange subpartition hiredate_1...
^
- postgresql and ivorysql 的分区交换就是先解绑子表再绑定普通表
-- 创建一个空的普通表
postgres=# create table hiredate_1981_tt as select * from emp limit 0;
-- 解绑子表
postgres=# alter table emp_range detach partition emp_range_hiredate_1981;
-- 绑定普通表
postgres=# alter table emp_range attach partition hiredate_1981_tt for values from ('1981-01-01') to ('1982-01-01');
-- 看心情改名字
alter table ... rename to ...;
-- oracle
sql> alter table emp_range move partition hiredate_1987 tablespace users;
sql> alter table emp_range_range move subpartition hiredate_1987_3000 tablespace users;
-- opengauss
[omm@mysql ~]$ mkdir /home/omm/tbs_users
opengauss=# create tablespace tbs_users owner omm location '/home/omm/tbs_users';
opengauss=# alter table emp_range move partition hiredate_1987 tablespace tbs_users;
-- postgresql and ivorysql 就是对表进行移动
[postgres@pgtest2 ~]$ mkdir /home/postgres/tbs_users
postgres=# create tablespace tbs_users owner postgres location '/home/postgres/tbs_users';
postgres=# alter table emp_range_hiredate_1987 set tablespace tbs_users;
- oracle 和 opengauss 不能移动组合分区的一级分区
sql> alter table emp_range_range move partition hiredate_1987 tablespace users;
ora-14257: cannot move partition other than a range, list, system, or hash partition
opengauss=# alter table emp_range_range move partition hiredate_1987 tablespace tbs_users;
error: un-support feature
detail: for subpartition table, modifying tablespace is not yet supported.
- opengauss 不能移动 subpartition
opengauss=# alter table emp_range_range move subpartition hiredate_1987_3000 tablespace tbs_users;
error: syntax error at or near "subpartition hiredate_1987_3000"
line 1: alter table emp_range_range move subpartition hiredate_1987_...
^
-- oracle and opengauss
alter table emp_interval rename partition sys_p133 to emp_interval_2000;
alter table emp_range_range rename partition hiredate_1987 to hiredate_1987_2;
alter table emp_range_range rename subpartition hiredate_1987_1000 to hiredate_1987_1000_2;
-- postgresql and ivorysql 就是修改表名
alter table emp_range_hiredate_1983 rename to emp_range_hiredate_1983_2;
alter table emp_range_range_1983 rename to emp_range_range_1983_2;
alter table emp_range_range_1983_3000 rename to emp_range_range_1983_3000_2;
- opengauss 3.0 不支持修改组合分区的一级分区名
opengauss=# alter table emp_range_range rename partition hiredate_1987 to hiredate_1987_2;
error: un-support feature
detail: for subpartition table, alter table ... rename partition/subpartition is not yet supported.
- opengauss 不支持修改 subpartition 的名称
opengauss=# alter table emp_range_range rename subpartition hiredate_1987_1000 to hiredate_1987_1000_2;
error: syntax error at or near "hiredate_1987_1000"
line 1: alter table emp_range_range rename subpartition hiredate_198...
^
最后修改时间：2022-04-13 14:18:59
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【米乐app官网下载的版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。