oracle 分区表插入数据_PostgreSQL分区表性能优化

最新推荐文章于 2023-04-15 17:08:52 发布

weixin_39593354

最新推荐文章于 2023-04-15 17:08:52 发布

阅读量1.4k

点赞数

文章标签： oracle 分区表插入数据

分区表需要使用表的继承来实现，这是PG的原生特性，由于需要使用触发器，insert性能相对比较差。所谓继承就是面向对象里面的继承，数据库中也可以支持面向对象的。一个表可以继承另一个表的所有特性，比如：

父表：

create table tbl_partition3(
id integer,
name varchar(20),
gender boolean,
join_date date,
dept char(4));

子表：

create table tbl_partition3_201211 (
check ( join_date >= DATE '2012-11-01' AND join_date < DATE '2012-12-01' )
) INHERITS (tbl_partition3);
create table tbl_partition3_201212 (
check ( join_date >= DATE '2012-12-01' AND join_date < DATE '2013-01-01' )
) INHERITS (tbl_partition3);
create table tbl_partition3_201301 (
check ( join_date >= DATE '2013-01-01' AND join_date < DATE '2013-02-01' )
) INHERITS (tbl_partition3);
create table tbl_partition3_201302 (
check ( join_date >= DATE '2013-02-01' AND join_date < DATE '2013-03-01' )
) INHERITS (tbl_partition3);
create table tbl_partition3_201303 (
check ( join_date >= DATE '2013-03-01' AND join_date < DATE '2013-04-01' )
) INHERITS (tbl_partition3);
create table tbl_partition3_201304 (
check ( join_date >= DATE '2013-04-01' AND join_date < DATE '2013-05-01' )
) INHERITS (tbl_partition3);
create table tbl_partition3_201305 (
check ( join_date >= DATE '2013-05-01' AND join_date < DATE '2013-06-01' )
) INHERITS (tbl_partition3);

查看父表的表结构，可以看到它有子表：

子表继承了父表的表结构，同时可以拥有自己的属性、约束和索引。如果想把表的继承作为分区表使用，一般在子表中增加check约束，每个表只保存某个范围的数据。实际应用中，父表中一般是没有数据的，数据全部插入子表中。

但是，仅仅创建父子表，往父表中insert数据时，数据并不会自动落入对应的子表：

虽然insert不会自动判断数据应该插入哪个子表，但是查询（包括更新和删除）确是可以做到分区裁剪的，根据分区列的查询条件查询必要的子表。

优化器扫描时判断了表上的check约束，如果数据不可能存在这个表里那么优化器就跳过这个表。

注意需要配置参数constraint_exclusion：默认值：partition

为了实现分区表的功能，还需要增加一个触发器，这样才能保证数据可以插入对应的子表，否则数据都插入父表了。下面是触发器的例子，实际项目中，由于日期是不断变化的，表名和insert语句都是动态的。

create or replace TRIGGER tbl_partition_trg3 BEFORE INSERT ON tbl_partition3 FOR EACH ROW
declare
begin
if ( :new.join_date >= date '2012-11-01' and :new.join_date < date '2012-12-01' ) then
insert into tbl_partition_201211(id, name, gender, join_date, dept) values (:new.id, :new.name, :new.gender, :new.join_date, :new.dept);
elsif ( :new.join_date >= date '2012-12-01' and :new.join_date < date '2013-01-01' ) then
insert into tbl_partition_201212 (id, name, gender, join_date, dept) values (:new.id, :new.name, :new.gender, :new.join_date, :new.dept);
elsif ( :new.join_date >= date '2013-01-01' and :new.join_date < date '2013-02-01' ) then
insert into tbl_partition_201301 (id, name, gender, join_date, dept) values (:new.id, :new.name, :new.gender, :new.join_date, :new.dept);
elsif ( :new.join_date >= date '2013-02-01' and :new.join_date < date '2013-03-01' ) then
insert into tbl_partition_201302 (id, name, gender, join_date, dept) values (:new.id, :new.name, :new.gender, :new.join_date, :new.dept);
elsif ( :new.join_date >= date '2013-03-01' and :new.join_date < date '2013-04-01' ) then
insert into tbl_partition_201303 (id, name, gender, join_date, dept) values (:new.id, :new.name, :new.gender, :new.join_date, :new.dept);
elsif ( :new.join_date >= date '2013-04-01' and :new.join_date < date '2013-05-01' ) then
insert into tbl_partition_201304 values (:new.id, :new.name, :new.gender, :new.join_date, :new.dept);
elsif ( :new.join_date >= date '2013-05-01' and :new.join_date < date '2013-06-01' ) then
insert into tbl_partition_201305 (id, name, gender, join_date, dept) values (:new.id, :new.name, :new.gender, :new.join_date, :new.dept);
else
raise exception 'date out of range. fix the tbl_partition_insert_trigger() function!';
end if;
end;
/