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

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.