首页 » ORACLE 9i-23ai » Oracle、PostgreSQL、MySQL、MogDB/openGauss数据库比较系列(六): 分区键更新

Oracle、PostgreSQL、MySQL、MogDB/openGauss数据库比较系列(六): 分区键更新

  • 2022/04/29
  • ORACLE 9i-23ai
  • 294 views
  • Oracle、PostgreSQL、MySQL、MogDB/openGauss数据库比较系列(六): 分区键更新已关闭评论

今天在看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)
 11   );
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可以更新		 正常更新 不支持 正常更新
打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：