当前位置: 首页> 健康> 养生 > Oracle、PostgreSQL、MySQL、MogDB openGuass-分区键更新

Oracle、PostgreSQL、MySQL、MogDB openGuass-分区键更新

时间:2025/7/11 15:00:52来源:https://blog.csdn.net/weixin_tank88921/article/details/139968798 浏览次数:1次

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

分区类型OraclePostgreSQLMogDB/openGaussMySQL
range partitionORA-14402
启动row movement可以更新
正常更新正常更新正常更新
list partitionORA-14403
启动row movement可以更新
正常更新不支持正常更新
关键字:Oracle、PostgreSQL、MySQL、MogDB openGuass-分区键更新

版权声明:

本网仅为发布的内容提供存储空间,不对发表、转载的内容提供任何形式的保证。凡本网注明“来源:XXX网络”的作品,均转载自其它媒体,著作权归作者所有,商业转载请联系作者获得授权,非商业转载请注明出处。

我们尊重并感谢每一位作者,均已注明文章来源和作者。如因作品内容、版权或其它问题,请及时与我们联系,联系邮箱:809451989@qq.com,投稿邮箱:809451989@qq.com

责任编辑: