Oracle、PostgreSQL、MySQL、MogDB/openGuass-分区键更新
在看MogDB的官方文档时看到一条关于分区的支持“Update操作时，支持数据跨分区移动（不支持Partition/SubPartition Key为List或Hash分区类型）” 也就是分区键更新， 那默认情况下Oracle、MySQL、PostGreSQL、MogDB(opengauss)横向对比一下表现，在rang\list partition update分区键在跨分区的表现。
测试range partition分区键更新
Oracle
SQL> select banner from v$version;BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - ProductionCREATE TABLE testrange(num NUMBER(8) NOT NULL,var varCHAR2(10)
)
PARTITION BY RANGE (num)(PARTITION p9 VALUES LESS THAN (20180901) ,PARTITION p10 VALUES LESS THAN (20181001) ,PARTITION p11 VALUES LESS THAN (20181101) ,PARTITION p12 VALUES LESS THAN (20181201));
Table created.SQL> insert into testrange(num,var)values(20180901,'132');
1 row created.SQL> insert into testrange(num,var)values(20181011,'133');
1 row created.SQL> commit;
Commit complete.SQL> update testrange set num=20181101 where num=20180901;
update testrange set num=20181101 where num=20180901*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition changeSQL> alter table testrange enable row movement;
Table altered.SQL> update testrange set num=20181101 where num=20180901;
1 row updated.SQL> commit;
Commit complete.SQL> alter table testrange disable row movement;
Table altered.
PostGreSQL
[postgres@oel7db1 ~]$ psql
psql (13.2)anbob=# CREATE TABLE testrange(
anbob(# num int NOT NULL,
anbob(# var varCHAR(10)
anbob(# )
anbob-# PARTITION BY RANGE (num);
CREATE TABLE
anbob=# create table testrange_p9 partition of testrange for values from(20180901) to (20181001);
CREATE TABLE
anbob=# create table testrange_p12 partition of testrange for values from(20181201) to (20190101);
CREATE TABLE
anbob=# create table testrange_p10 partition of testrange for values from(20181001) to (20181101);
CREATE TABLE
anbob=# create table testrange_p11 partition of testrange for values from(20181101) to (20181201);
CREATE TABLE
anbob=# insert into testrange(num,var)values(20180901,'132');
INSERT 0 1
anbob=# insert into testrange(num,var)values(20181011,'133');
INSERT 0 1anbob=# update testrange set num=20181101 where num=20180901;
UPDATE 1
MogDB
openGauss=# select version();version
--------------------------------------------------------------------------------------------------------------------------------------------------(MogDB 2.1.1 build b5f25b20) compiled at 2022-03-21 14:42:30 commit 0 last mr on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)openGauss=# CREATE TABLE testrange(num NUMBER(8) NOT NULL,var varCHAR2(10)
)
PARTITION BY RANGE (num)(PARTITION p9 VALUES LESS THAN (20180901) ,PARTITION p10 VALUES LESS THAN (20181001) ,PARTITION p11 VALUES LESS THAN (20181101) ,PARTITION p12 VALUES LESS THAN (20181201));
CREATE TABLEopenGauss=# insert into testrange(num,var)values(20180901,'132');
INSERT 0 1
openGauss=# insert into testrange(num,var)values(20181011,'133');
INSERT 0 1
openGauss=# update testrange set num=20181101 where num=20180901;
UPDATE 1
MySQL
mysql> SELECT VERSION();
+-------------------+
| VERSION() |
+-------------------+
| 8.0.20-commercial |
+-------------------+
1 row in set (0.00 sec)mysql> CREATE TABLE testrange(-> num int NOT NULL,-> var varCHAR(10)-> )-> PARTITION BY RANGE (num)-> (-> PARTITION p9 VALUES LESS THAN (20180901) ,-> PARTITION p10 VALUES LESS THAN (20181001) ,-> PARTITION p11 VALUES LESS THAN (20181101) ,-> PARTITION p12 VALUES LESS THAN (20181201)-> );
Query OK, 0 rows affected (0.14 sec)mysql> insert into testrange(num,var)values(20180901,'132');
Query OK, 1 row affected (0.05 sec)mysql> insert into testrange(num,var)values(20181011,'133');
Query OK, 1 row affected (0.00 sec)mysql> update testrange set num=20181101 where num=20180901;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
测试LIST Partition更新
Oracle
CREATE TABLE tblist (id INT NOT NULL,store_id INT
)
PARTITION BY LIST(store_id) (PARTITION a VALUES (1,5,6),PARTITION b VALUES (2,7,8),PARTITION c VALUES (3,9,10),PARTITION d VALUES (4,11,12)10 );
Table created.SQL> insert into tblist values(5,5);
1 row created.SQL> update tblist set store_id=7 where store_id=5;
update tblist set store_id=7 where store_id=5*
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition changeSQL> alter table tblist enable row movement;
Table altered.SQL> update tblist set store_id=7 where store_id=5;
1 row updated.SQL> alter table tblist disable row movement;
Table altered.
PostgreSQL
anbob=# create table tblist_a partition of tblist for VALUES IN (1,5,6);
CREATE TABLE
anbob=# create table tblist_b partition of tblist for VALUES IN (2,7,8);
CREATE TABLE
anbob=# insert into tblist values(5,5);
INSERT 0 1
anbob=# update tblist set store_id=7 where store_id=5;
UPDATE 1
anbob=# select * from tblist;id | store_id
----+----------5 | 7
(1 row)
MogDB
openGauss=# CREATE TABLE tblist (id INT NOT NULL,store_id INT
)
PARTITION BY LIST(store_id) (PARTITION a VALUES (1,5,6),PARTITION b VALUES (2,7,8),PARTITION c VALUES (3,9,10),PARTITION d VALUES (4,11,12)
);
CREATE TABLEopenGauss=# insert into tblist values(5,5);
INSERT 0 1
openGauss=# update tblist set store_id=7 where store_id=5;
ERROR: fail to update partitioned table "tblist"
DETAIL: disable row movementopenGauss=# alter table tblist enable row movement;
ERROR: Row Movement
DETAIL: Row Movement is not supported in List/Hash Partition currently
MySQL
mysql> CREATE TABLE tblist (-> id INT NOT NULL,-> store_id INT-> )-> PARTITION BY LIST(store_id) (-> PARTITION a VALUES IN (1,5,6),-> PARTITION b VALUES IN (2,7,8),-> PARTITION c VALUES IN (3,9,10),-> PARTITION d VALUES IN (4,11,12)-> );
Query OK, 0 rows affected (0.14 sec)mysql> insert into tblist values(5,5);
Query OK, 1 row affected (0.01 sec)mysql> update tblist set store_id=7 where store_id=5;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Summary
| 分区类型 | Oracle | PostgreSQL | MogDB/openGauss | MySQL |
|---|---|---|---|---|
| range partition | ORA-14402 启动row movement可以更新 | 正常更新 | 正常更新 | 正常更新 |
| list partition | ORA-14403 启动row movement可以更新 | 正常更新 | 不支持 | 正常更新 |
