分区表维护支持情况
| 维护 | 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 详情参考：https://gitee.com/enmotech/compat-tools
- 以下使用 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)
- 合并分区的方式
-- 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 and openGauss)
- 拆分分区的方式
-- 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
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
