oraclepgsql分区表,postgresql分区表实现⽅式
1. 概述分区表
分区的意思是把逻辑上的⼀个⼤表分割成物理上的⼏块⼉，分区可以提供若⼲好处：
1). 某些类型的查询性能可以得到极⼤提升。
2). 更新的性能也可以得到提升，因为表的每块的索引要⽐在整个数据集上的索引要⼩。如果索引不能全部放在内存⾥，那么在索引上的读和写都会产⽣更多的磁盘访问。
3). 批量删除可以⽤简单地删除某个分区来实现。
4). 将很少⽤的数据可以移动到便宜的、慢⼀些地存储介质上。
假设当前的数据库并不⽀持分区表，⽽我们的应⽤所需处理的数据量也⾮常⼤，对于这种应⽤场景，我们不得不⼈为的将该⼤表按 照⼀定的规则，⼿⼯拆分成多个⼩表，让每个⼩表包含不同区间的数据。这样⼀来，我们就必须在数据插⼊、更新、删除和查询之前，先计算本次的指令需要操作的⼩表。对于有些查询⽽⾔，由于查询区间可能会跨越多个⼩表，这样我们⼜不得不将多个⼩表的查询结 果进⾏ union 操作，以合并来⾃多个表的数据，并最终形成⼀个结果集返回给客户端。可见，如果我们正在使⽤的数
据库不⽀持分区 表，那么在适合其应⽤的场景下，我们就需要做很多额外的编程⼯作以弥补这⼀缺失。然⽽需要说明的是，尽管功能可以勉强应付， 但是性能却和分区表⽆法相提并论。
⽬前 PostgreSQL ⽀持的分区形式主要为以下两种：
1).范围分区: 表被⼀个或者多个键字字段分区成"范围"，在这些范围之间没有重叠的数值分布到不同的分区⾥。⽐如，我们可以为特定的商业对象根据数据范围分区，或者根据标识符范围分区。
2).列表分区: 表是通过明确地列出每个分区⾥应该出现那些键字值实现的。
2.测试案例
--登录到数据库
postgres=# select current_database();
current_database
------------------
postgres
(1 row)
--创建数据库
postgres=# create database mytest;
--切换数据库sql中union多表合并
postgres=# \c mytest;
You are now connected to database "mytest" as user "postgres".
--创建⽗表
mytest=# create table parent(dpart_id bigserial,person_id bigserial,name varchar(20),birth date);
CREATE TABLE
mytest=# select * from parent;
dpart_id | person_id | name | birth
(0 rows)
--创建⼦表
pgsql本⾝像oracle那样的分区定义什么的，通过⼦表对⽗表的继承定义实现的，所以有多少个分区，我们就必须建多少个⼦表。mytest=# create table child_t01(check(dpart_id > 0 and dpart_id < 20)) inherits(parent);
CREATE TABLE
mytest=# create table child_t02(check(dpart_id >= 20 and dpart_id < 40)) inherits(parent);
CREATE TABLE
mytest=# create table child_t03(check(dpart_id >= 40 and dpart_id < 60)) inherits(parent);
CREATE TABLE
mytest=# create table child_t04(check(dpart_id >= 60 and dpart_id < 80)) inherits(parent);
CREATE TABLE
mytest=# create table child_t05(check(dpart_id >= 80 and dpart_id < 100)) inherits(parent);
CREATE TABLE
mytest=# create table child_t06(check(dpart_id >= 100)) inherits(parent);
--创建规则
建⽴好⼦表与⽗表的继承关系后，不必须定义规则，然后才能把相应的数据插⼊到指定的⼦表中，具体过程如下：
mytest=# CREATE OR REPLACE RULE t01_qualified AS
ON INSERT TO parent WHERE dpart_id > 0 and dpart_id < 20
DO INSTEAD
INSERT INTO child_t01 VALUES(NEW.*);
CREATE RULE
其它⼏个⼦表也按这种⽅式创建。
--插⼊数据测试
mytest=# insert into parent(dpart_id,person_id,name,birth) values(10,10011,'TOM',date'1984-05-04');
INSERT 0 0
mytest=# select * from parent;
dpart_id | person_id | name |  birth
----------+-----------+------+------------
10 |    10011 | TOM  | 1984-05-04
(1 row)
mytest=# select * from only parent;
dpart_id | person_id | name | birth
----------+-----------+------+-------
可以看到数据并没有存储到⽗表中，⽽存储在⼦表中
mytest=# select count(1) from parent;
count
-------
23
(1 row)
mytest=# select count(*) from only parent;
count
-------
(1 row)
总共插⼊了23条，我们可以看到⽗表中没有数据，都通过⼦表的。
mytest=#  explain select * from parent where dpart_id = 120;
QUERY PLAN
----------------------------------------------------------------
Append  (cost=0.00..1.05 rows=2 width=78)
->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=78)
Filter: (dpart_id = 120)
->  Seq Scan on child_t06  (cost=0.00..1.05 rows=1 width=78)
Filter: (dpart_id = 120)
从⼦表中查询出来的。
mytest=# explain select * from parent where dpart_id > 20 and dpart_id <=70; QUERY PLAN
----------------------------------------------------------------
Append  (cost=0.00..3.18 rows=4 width=78)
->  Seq Scan on parent  (cost=0.00..0.00 rows=1 width=78)
Filter: ((dpart_id > 20) AND (dpart_id <= 70))
->  Seq Scan on child_t02  (cost=0.00..1.06 rows=1 width=78)
Filter: ((dpart_id > 20) AND (dpart_id <= 70))
->  Seq Scan on child_t03  (cost=0.00..1.06 rows=1 width=78)
Filter: ((dpart_id > 20) AND (dpart_id <= 70))
->  Seq Scan on child_t04  (cost=0.00..1.06 rows=1 width=78)
Filter: ((dpart_id > 20) AND (dpart_id <= 70))
3、其它⽅式
在pgsql中，分区表通过⽗表与⼦表的继承关系，再加上定义的规则实现。当然也可以通过其它⽅式实现，如触发器
⾸先创建⼀个函数
CREATE OR REPLACE FUNCTION parent_insert_f()
RETURNS TRIGGER AS
$$
BEGIN
IF(NEW.dpart_id > 0 and NEW.dpart_id < 20) THEN
INSERT INTO child_t01 VALUES (NEW.*);
END IF;
IF(NEW.dpart_id >= 20 and NEW.dpart_id < 40) THEN
INSERT INTO child_t02 VALUES (NEW.*);
END IF;
.
.............  --以此推算
RETURN NULL;
END;
$$
LANGUAGE plpgsql ;
通过trigger调⽤函数
CREATE TRIGGER parent_insert_tr
BEFORE INSERT ON parent
FOR EACH row
EXECUTE PROCEDURE parent_insert_f() ;
4、总结
总的说来，分区表实现还是⽐较⿇烦的，不如oracle灵活，如果遇到复杂情况，就⽐较⿇烦了。并且⽀持的分区也相对少⼀些。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。