oracle pgsql分区表,postgres实现分区表功能

PG没有类似ORACLE的分区表功能，但它可以通过继承来实现这一功能。

CREATE TABLE t1 (like t

including all) INHERITS (t);

这样，表t的结构，包括索引和约束，都被t1继承了。

一般的应用经常会根据时间字段来做分区，假设一张总表t，它的分表一般为t_yyyymm或t_yyyymmdd，按月或按天分表。

在对历史数据做维护的时候，我们可以只维护分表，而不动到总表。

这里特别需要注意，如果要清理数据数据，用truncate命令，truncate table

t_yyyymm是不行的，这样会导致总表的数据(即所有分区的数据)都被清理掉。

可以在命令里加上only来限制它只删除分表的数据：truncate table only t_yyyymm

PG的分区表有个限制，用以下例子说明：

create table t(id integer,pdate timestamp without time

zone);

CREATE TABLE t_201301 (like t

including all) INHERITS (t);

CREATE TABLE t_201302 (like t

including all) INHERITS (t);

CREATE TABLE t_201303 (like t

including all) INHERITS (t);

CREATE TABLE t_201304 (like t

including all) INHERITS (t);

CREATE TABLE t_201305 (like t

including all) INHERITS (t);

select * from t where pdate>='20130101' and

p_date

这样PG会扫描所有的分区(ORACLE不同，会自动对应到相应分区)

我们可以通过设置参数以及为分表的pdate字段增加check约束来实现分区对应的功能。

SET constraint_exclusion = on;

create table t(id integer,pdate timestamp without time

zone);

create table t_201301 (check (pdate>=date'2013-01-01' and

pdate

create table t_201302 (check (pdate>=date'2013-02-01' and

pdate

create table t_201303 (check (pdate>=date'2013-03-01' and

pdate

create table t_201304 (check (pdate>=date'2013-04-01' and

pdate

create table t_201305 (check (pdate>=date'2013-05-01' and

pdate

select * from t where pdate>='20130101' and

p_date

这样，上述SQL只扫描t_201301和201302。

当对t进行dml操作的时候，例：insert into t

values(9,'20130301');数据只会进入t,不会进入t_201303。我们可以通过对表创建trigger来实现插入的数据自动进入对应的分表。

CREATE OR REPLACE FUNCTION p_insert_trigger()

RETURNS TRIGGER AS $$

BEGIN

IF ( NEW.pdate >= DATE '2013-01-01' AND NEW.pdate < DATE

'2013-02-01' ) THEN

INSERT INTO t_201301 VALUES (NEW.*);

ELSIF ( NEW.pdate >= DATE '2013-02-01' AND NEW.pdate < DATE

'2013-03-01' ) THEN

INSERT INTO t_201302 VALUES (NEW.*);

ELSIF ( NEW.pdate >= DATE '2013-03-01' AND NEW.pdate < DATE

'2013-04-01' ) THEN

INSERT INTO t_201303 VALUES (NEW.*);

ELSIF ( NEW.pdate >= DATE '2013-04-01' AND NEW.pdate < DATE

'2013-05-01' ) THEN

INSERT INTO t_21304 VALUES (NEW.*);

ELSIF ( NEW.pdate >= DATE '2013-05-01' AND NEW.pdate < DATE

'2013-06-01' ) THEN

INSERT INTO t_201305 VALUES (NEW.*);

ELSE

RAISE EXCEPTION 'Date out of range. Fix the p_insert_trigger()

function!';

END IF;

RETURN NULL;

END;

$$

LANGUAGE plpgsql;

CREATE TRIGGER t_trigger_insert

BEFORE INSERT ON t

FOR EACH ROW EXECUTE PROCEDURE p_insert_trigger();

update和delete操作方法相同，语法参考上面例子。

虽然可以通过check

constraint和trigger来实现ORACLE分区表的功能，但在生产环境中，我们是严禁这么做的。以INSERT为例，这样做的结果，比把数据直接插入表中，效率慢了10倍以上！！！所以我们要求在应用程序中通过动态SQL，根据当前日期拼接出数据所要进入的分表表名，再把数据直接插入到分表中。

最后再解释下constraint_exclusion 参数。

constraint_exclusion 为enum类型，一共有3个可选值

on：检查所有表的constraint

off：不检查所有表的constraint

partition：default值，只检查inheritance表和union all

subquery的constarint

该参数只被check constraint驱动，所以要想它起作用必须在分区表上加上check constraint