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分区作为一级分区)。
版权声明:本文为博主原创文章,未经博主允许不得转载。