Oracle与PostgreSQL拆分分区有什么不同

本篇内容主要讲解"Oracle与PostgreSQL拆分分区有什么不同"，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习"Oracle与PostgreSQL拆分分区有什么不同"吧!

直至12版本,PostgreSQL仍没有提供直接拆分分区的功能,暂时只能通过detach&attach实现,相对于Oracle的split支持,PG显得比较的simple&naive.

PG 12

[pg12@localhost ~]$ psql -d testdbTiming is on.Expanded display is used automatically.psql (12beta1)Type "help" for help.[local]:5432 pg12@testdb=# drop table t_p1;) to (200);create table t_p1_maxvalue partition of t_p1 for values from (200) to (maxvalue);truncate table t_p1;insert into t_p1(id,c1) values(1,1);insert into t_p1(id,c1) values(2,100);insert into t_p1(id,c1) values(3,125);insert into t_p1(id,c1) values(4,200);insert into t_p1(id,c1) values(5,250);insert into t_p1(id,c1) values(6,300);insert into t_p1(id,c1) values(7,350);insert into t_p1(id,c1) values(8,4500);alter table t_p1 detach partition t_p1_maxvalue;create table t_p1_3 partition of t_ERROR:  table "t_p1" does not existTime: 8.497 ms[local]:5432 pg12@testdb=# create table t_p1 (id int, c1 int) partition by range (c1);p1 for values from (200) to (300);insert into t_p1_3 select * from t_p1_maxvalue where c1 >= 200 and c1 < 300;delete from t_p1_maxvalue where c1 >= 200 and c1 < 300;alter table t_p1 attach partition t_p1_maxvalue for values from (300) to (maxvalue);CREATE TABLETime: 235.099 ms[local]:5432 pg12@testdb=# create table t_p1_default partition of t_p1 default;CREATE TABLETime: 11.941 ms[local]:5432 pg12@testdb=# create table t_p1_1 partition of t_p1 for values from (1) to (100);CREATE TABLETime: 15.247 ms[local]:5432 pg12@testdb=# create table t_p1_2 partition of t_p1 for values from (100) to (200);CREATE TABLETime: 1.705 ms[local]:5432 pg12@testdb=# create table t_p1_maxvalue partition of t_p1 for values from (200) to (maxvalue);CREATE TABLETime: 1.842 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# truncate table t_p1;TRUNCATE TABLETime: 3.413 ms[local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(1,1);INSERT 0 1Time: 1.152 ms[local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(2,100);INSERT 0 1Time: 0.871 ms[local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(3,125);INSERT 0 1Time: 0.487 ms[local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(4,200);INSERT 0 1Time: 0.949 ms[local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(5,250);INSERT 0 1Time: 0.494 ms[local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(6,300);INSERT 0 1Time: 0.463 ms[local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(7,350);INSERT 0 1Time: 0.481 ms[local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(8,4500);INSERT 0 1Time: 0.464 ms[local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# alter table t_p1 detach partition t_p1_maxvalue;ALTER TABLETime: 0.864 ms[local]:5432 pg12@testdb=# create table t_p1_3 partition of t_p1 for values from (200) to (300);CREATE TABLETime: 1.752 ms[local]:5432 pg12@testdb=# insert into t_p1_3 select * from t_p1_maxvalue where c1 >= 200 and c1 < 300;INSERT 0 2Time: 7.578 ms[local]:5432 pg12@testdb=# delete from t_p1_maxvalue where c1 >= 200 and c1 < 300;DELETE 2Time: 21.992 ms[local]:5432 pg12@testdb=# alter table t_p1 attach partition t_p1_maxvalue for values from (300) to (maxvalue);ALTER TABLETime: 7.356 ms[local]:5432 pg12@testdb=#

Oracle

TEST-orcl@DESKTOP-V430TU3>create table t_p1(id int,c1 int)  2  partition by range(c1)  3  (partition p1 values less than(100),  4   partition p2 values less than(200),  5   partition pmax values less than(maxvalue)  6  );Table created.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>truncate table t_p1;Table truncated.TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(1,1);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(2,100);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(3,125);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(4,200);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(5,250);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(6,300);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(7,350);1 row created.TEST-orcl@DESKTOP-V430TU3>insert into t_p1(id,c1) values(8,4500);1 row created.TEST-orcl@DESKTOP-V430TU3>alter table t_p1 split partition pmax at(1000) into (partition p3,partition pmx);Table altered.TEST-orcl@DESKTOP-V430TU3>

可以参照EDB的做法,加入此兼容性.

到此，相信大家对"Oracle与PostgreSQL拆分分区有什么不同"有了更深的了解，不妨来实际操作一番吧！这里是网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！