Shulou(Shulou.com)05/31 Report--

This article mainly talks about "what is the difference between Oracle and PostgreSQL split partition". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next let the editor to take you to learn "what is the difference between Oracle and PostgreSQL split partition"!

Until the 12th version, PostgreSQL still does not provide the function of directly splitting partitions, which can only be realized through detach&attach for the time being. Compared with Oracle's split support, PG appears to be more 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 tactip1;) to (200); create table t_p1_maxvalue partition of t_p1 for values from (200) to (maxvalue); truncate table tactip1; into t_p1 (id,c1) values (1); insert into t_p1 (id,c1) values (2100); insert into t_p1 (id,c1) values (3125) Insert into t_p1 (id,c1) values (4200); insert into t_p1 (id,c1) values (5250); insert into t_p1 (id,c1) values (6300); insert into t_p1 (id,c1) values (7350); insert into t_p1 (id,c1) values (8pm 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 (8.497); 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, 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 tactile p1bot table truncated.TEST-orcl@DESKTOP-V430TU3 > insert into t_p1 (id,c1) values (1Jing 1); 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into t_p1 (id,c1) values (2100) 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into t_p1 (id,c1) values (3125); 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into t_p1 (id,c1) values (4200); 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into t_p1 (id,c1) values (5250); 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into t_p1 (id,c1) values (6300); 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into t_p1 (id,c1) values (7350) 1 row created.TEST-orcl@DESKTOP-V430TU3 > insert into t_p1 (id,c1) values; 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 >

You can follow the practice of EDB to add this compatibility.

At this point, I believe you have a deeper understanding of "what is the difference between Oracle and PostgreSQL split partition". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.