oracle pgsql分区表,PostgreSQL组合分区及修改分区表分区范围

oracle中支持多种分区类型的组合，组合分区是range、hash、list分区的相互组合，但不允许hash分区作为 Top level。

pg中同样也支持类似的组合分区，PG支持非常灵活的分区布局，支持任意层级的分区，支持每个分区的层级深度不一样。通过detach、attach的方法来实现分区的拆分、合并。

下面以hash分区为例，使用拆分合并的方法实现组合分区。https://www.cndba.cn/foucus/article/3818

—创建hash分区表

共4个分区

https://www.cndba.cn/foucus/article/3818https://www.cndba.cn/foucus/article/3818

bill=# create table t_hash (id int , info text) PARTITION BY hash (id);

CREATE TABLE

bill=# create table t_hash0 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 0);

CREATE TABLE

bill=# create table t_hash1 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 1);

CREATE TABLE

bill=# create table t_hash2 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 2);

CREATE TABLE

bill=# create table t_hash3 partition of t_hash FOR VALUES with (MODULUS 4, REMAINDER 3);

CREATE TABLE

—查看分区表https://www.cndba.cn/foucus/article/3818

bill=# /d+ t_hash

Partitioned table "public.t_hash"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

--------+---------+-----------+----------+---------+----------+--------------+-------------

id | integer | | | | plain | |

info | text | | | | extended | |

Partition key: HASH (id)

Partitions: t_hash0 FOR VALUES WITH (modulus 4, remainder 0),

t_hash1 FOR VALUES WITH (modulus 4, remainder 1),

t_hash2 FOR VALUES WITH (modulus 4, remainder 2),

t_hash3 FOR VALUES WITH (modulus 4, remainder 3)

—插入数据

bill=# insert into t_hash select generate_series(1,10);

INSERT 0 10

—查看数据分布

bill=# select tableoid::regclass,* from t_hash;

tableoid | id | info

----------+----+------

t_hash0 | 1 |

t_hash1 | 3 |

t_hash1 | 5 |

t_hash1 | 8 |

t_hash1 | 9 |

t_hash2 | 2 |

t_hash3 | 4 |

t_hash3 | 6 |

t_hash3 | 7 |

t_hash3 | 10 |

(10 rows)

接下来将t_hash1分区拆分成2个分区，即其它分区作为直接分区，而t_hash1分区被1个二级分区代替。

https://www.cndba.cn/foucus/article/3818

—解绑分区

bill=# alter table t_hash DETACH PARTITION t_hash1;

ALTER TABLE

https://www.cndba.cn/foucus/article/3818

bill=# create table t_hash1_subp (id int, info text) PARTITION BY hash (id);;

CREATE TABLE

bill=# create table t_hash1_subp1 partition of t_hash1_subp FOR VALUES with (MODULUS 8, REMAINDER 1);

CREATE TABLE

bill=# create table t_hash1_subp5 partition of t_hash1_subp FOR VALUES with (MODULUS 8, REMAINDER 5);

CREATE TABLE

—绑定二级分区到一级分区

bill=# alter table t_hash attach partition t_hash1_subp FOR VALUES WITH ( MODULUS 4, REMAINDER 1 );

ALTER TABLE

bill=# insert into t_hash1_subp select * from t_hash1;

INSERT 0 4

—查询数据

bill=# select tableoid::regclass,* from t_hash;

tableoid | id | info

---------------+----+------

t_hash0 | 1 |

t_hash1_subp1 | 3 |

t_hash1_subp5 | 5 |

t_hash1_subp5 | 8 |

t_hash1_subp5 | 9 |

t_hash2 | 2 |

t_hash3 | 4 |

t_hash3 | 6 |

t_hash3 | 7 |

t_hash3 | 10 |

(10 rows)

—查看分区表情况

可以发现分区表变成了非平衡的复合分区

bill=# /d+ t_hash

Partitioned table "public.t_hash"

Column | Type | Collation | Nullable | Default | Storage | Stats target | Description

--------+---------+-----------+----------+---------+----------+--------------+-------------

id | integer | | | | plain | |

info | text | | | | extended | |

Partition key: HASH (id)

Partitions: t_hash0 FOR VALUES WITH (modulus 4, remainder 0),

t_hash1_subp FOR VALUES WITH (modulus 4, remainder 1), PARTITIONED,

t_hash2 FOR VALUES WITH (modulus 4, remainder 2),

t_hash3 FOR VALUES WITH (modulus 4, remainder 3)

同样，我们还可以将其它分区拆分，例如将t_hash2拆分成range分区或者list分区，实现oracle组合分区一样的功能，且更加灵活(支持hash分区作为一级分区)。

版权声明：本文为博主原创文章，未经博主允许不得转载。