Oracle与PostgreSQL拆分分区有什么不同
本篇内容主要讲解“Oracle与PostgreSQL拆分分区有什么不同”,感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷,实用性强。下面就让小编来带大家学习“Oracle与PostgreSQL拆分分区有什么不同”吧!
创新互联-专业网站定制、快速模板网站建设、高性价比临漳网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式临漳网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖临漳地区。费用合理售后完善,十多年实体公司更值得信赖。
直至12版本,PostgreSQL仍没有提供直接拆分分区的功能,暂时只能通过detach&attach实现,相对于Oracle的split支持,PG显得比较的simple&naive.
PG 12
[pg12@localhost ~]$ psql -d testdb Timing 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 exist Time: 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 TABLE Time: 235.099 ms [local]:5432 pg12@testdb=# create table t_p1_default partition of t_p1 default; CREATE TABLE Time: 11.941 ms [local]:5432 pg12@testdb=# create table t_p1_1 partition of t_p1 for values from (1) to (100); CREATE TABLE Time: 15.247 ms [local]:5432 pg12@testdb=# create table t_p1_2 partition of t_p1 for values from (100) to (200); CREATE TABLE Time: 1.705 ms [local]:5432 pg12@testdb=# create table t_p1_maxvalue partition of t_p1 for values from (200) to (maxvalue); CREATE TABLE Time: 1.842 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# truncate table t_p1; TRUNCATE TABLE Time: 3.413 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(1,1); INSERT 0 1 Time: 1.152 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(2,100); INSERT 0 1 Time: 0.871 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(3,125); INSERT 0 1 Time: 0.487 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(4,200); INSERT 0 1 Time: 0.949 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(5,250); INSERT 0 1 Time: 0.494 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(6,300); INSERT 0 1 Time: 0.463 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(7,350); INSERT 0 1 Time: 0.481 ms [local]:5432 pg12@testdb=# insert into t_p1(id,c1) values(8,4500); INSERT 0 1 Time: 0.464 ms [local]:5432 pg12@testdb=# [local]:5432 pg12@testdb=# alter table t_p1 detach partition t_p1_maxvalue; ALTER TABLE Time: 0.864 ms [local]:5432 pg12@testdb=# create table t_p1_3 partition of t_p1 for values from (200) to (300); CREATE TABLE Time: 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 2 Time: 7.578 ms [local]:5432 pg12@testdb=# delete from t_p1_maxvalue where c1 >= 200 and c1 < 300; DELETE 2 Time: 21.992 ms [local]:5432 pg12@testdb=# alter table t_p1 attach partition t_p1_maxvalue for values from (300) to (maxvalue); ALTER TABLE Time: 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拆分分区有什么不同”有了更深的了解,不妨来实际操作一番吧!这里是创新互联网站,更多相关内容可以进入相关频道进行查询,关注我们,继续学习!
当前标题:Oracle与PostgreSQL拆分分区有什么不同
本文URL:http://scyanting.com/article/ijjpdj.html