oracle split 怎么解决 default 分区,Oracle vs PostgreSQL DBA（13）- 拆分(split)分区

直至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的做法,加入此兼容性.

**参考资料**

[13.3 Partitioning Commands Compatible with Oracle Databases](https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/database-compatibility-for-oracle-developers-guide/9.5/Database_Compatibility_for_Oracle_Developers_Guide.1.329.html#)