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 - Production
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));
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 change
SQL> 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 1
anbob=# 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 TABLE
openGauss=# 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 change
SQL> 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 TABLE
openGauss=# 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 movement
openGauss=# 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可以更新 | 正常更新 | 不支持 | 正常更新 |