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

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

PG 12

[pg12@localhost~]$psql-dtestdbTimingison.Expandeddisplayisusedautomatically.psql(12beta1)Type"help"forhelp.[local]:5432pg12@testdb=#droptablet_p1;)to(200);createtablet_p1_maxvaluepartitionoft_p1forvaluesfrom(200)to(maxvalue);truncatetablet_p1;insertintot_p1(id,c1)values(1,1);insertintot_p1(id,c1)values(2,100);insertintot_p1(id,c1)values(3,125);insertintot_p1(id,c1)values(4,200);insertintot_p1(id,c1)values(5,250);insertintot_p1(id,c1)values(6,300);insertintot_p1(id,c1)values(7,350);insertintot_p1(id,c1)values(8,4500);altertablet_p1detachpartitiont_p1_maxvalue;createtablet_p1_3partitionoft_ERROR:table"t_p1"doesnotexistTime:8.497ms[local]:5432pg12@testdb=#createtablet_p1(idint,c1int)partitionbyrange(c1);p1forvaluesfrom(200)to(300);insertintot_p1_3select*fromt_p1_maxvaluewherec1>=200andc1<300;deletefromt_p1_maxvaluewherec1>=200andc1<300;altertablet_p1attachpartitiont_p1_maxvalueforvaluesfrom(300)to(maxvalue);CREATETABLETime:235.099ms[local]:5432pg12@testdb=#createtablet_p1_defaultpartitionoft_p1default;CREATETABLETime:11.941ms[local]:5432pg12@testdb=#createtablet_p1_1partitionoft_p1forvaluesfrom(1)to(100);CREATETABLETime:15.247ms[local]:5432pg12@testdb=#createtablet_p1_2partitionoft_p1forvaluesfrom(100)to(200);CREATETABLETime:1.705ms[local]:5432pg12@testdb=#createtablet_p1_maxvaluepartitionoft_p1forvaluesfrom(200)to(maxvalue);CREATETABLETime:1.842ms[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#truncatetablet_p1;TRUNCATETABLETime:3.413ms[local]:5432pg12@testdb=#insertintot_p1(id,c1)values(1,1);INSERT01Time:1.152ms[local]:5432pg12@testdb=#insertintot_p1(id,c1)values(2,100);INSERT01Time:0.871ms[local]:5432pg12@testdb=#insertintot_p1(id,c1)values(3,125);INSERT01Time:0.487ms[local]:5432pg12@testdb=#insertintot_p1(id,c1)values(4,200);INSERT01Time:0.949ms[local]:5432pg12@testdb=#insertintot_p1(id,c1)values(5,250);INSERT01Time:0.494ms[local]:5432pg12@testdb=#insertintot_p1(id,c1)values(6,300);INSERT01Time:0.463ms[local]:5432pg12@testdb=#insertintot_p1(id,c1)values(7,350);INSERT01Time:0.481ms[local]:5432pg12@testdb=#insertintot_p1(id,c1)values(8,4500);INSERT01Time:0.464ms[local]:5432pg12@testdb=#[local]:5432pg12@testdb=#altertablet_p1detachpartitiont_p1_maxvalue;ALTERTABLETime:0.864ms[local]:5432pg12@testdb=#createtablet_p1_3partitionoft_p1forvaluesfrom(200)to(300);CREATETABLETime:1.752ms[local]:5432pg12@testdb=#insertintot_p1_3select*fromt_p1_maxvaluewherec1>=200andc1<300;INSERT02Time:7.578ms[local]:5432pg12@testdb=#deletefromt_p1_maxvaluewherec1>=200andc1<300;DELETE2Time:21.992ms[local]:5432pg12@testdb=#altertablet_p1attachpartitiont_p1_maxvalueforvaluesfrom(300)to(maxvalue);ALTERTABLETime:7.356ms[local]:5432pg12@testdb=#

Oracle

TEST-orcl@DESKTOP-V430TU3>createtablet_p1(idint,c1int)2partitionbyrange(c1)3(partitionp1valueslessthan(100),4partitionp2valueslessthan(200),5partitionpmaxvalueslessthan(maxvalue)6);Tablecreated.TEST-orcl@DESKTOP-V430TU3>TEST-orcl@DESKTOP-V430TU3>truncatetablet_p1;Tabletruncated.TEST-orcl@DESKTOP-V430TU3>insertintot_p1(id,c1)values(1,1);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintot_p1(id,c1)values(2,100);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintot_p1(id,c1)values(3,125);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintot_p1(id,c1)values(4,200);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintot_p1(id,c1)values(5,250);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintot_p1(id,c1)values(6,300);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintot_p1(id,c1)values(7,350);1rowcreated.TEST-orcl@DESKTOP-V430TU3>insertintot_p1(id,c1)values(8,4500);1rowcreated.TEST-orcl@DESKTOP-V430TU3>altertablet_p1splitpartitionpmaxat(1000)into(partitionp3,partitionpmx);Tablealtered.TEST-orcl@DESKTOP-V430TU3>

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

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