注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

PostgreSQL 中文网

 
 
 

日志

 
 

PostgreSQL: 通过函数初始化三层结构分区表并创建约束关系  

2011-12-03 20:24:43|  分类: PG性能优化 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |


       PostgreSQL 没有提供像 Oracle 里那样比较智能的分区表功能,比如插入时需要指定子表,但是在一定
程序上也能构建分区表,平常用得比较多的是两层结构,通过时间进行分区,今天介绍下构建三导分区表的方
法,三层结构分区别可参考以下。


一 体系结构
--1 三层结构分区表结构

 

PostgreSQL: 通过函数初始化三层结构分区表并创建约束关系 - francs - My DBA LIFE

 
   备注:三层结构分区表包括三层,第一层为父表, 第二层通过某字段取模分区分了多个表,上图分为
            128个表, 第三层为年月表,根据时间字段分区,这样便实现了三层结构分区。接下来介绍下
             创建三层结构分区表的具体步骤。


二 创建三层结构分区表
---2.1 创建父表 ( 第一层 )
create table tbl_log(
    sky_imsi            character varying(50)           not null,
    xxx                 character varying(12)                    ,
    xxx                 character varying(30)           not null,
    xxx                 integer                             ,
    xxx                character varying                        ,
    xxx                 character varying(20)                    ,
    trade_time          timestamp without time zone     not null,
    col1                character varying(20),                      --预留字段1
    col2                character varying (30),                     --预留字段2
    col3                character varying (50),                     --预留字段3
    remark              character varying(100)                  ,   --备注
    CONSTRAINT pk_tbl_log PRIMARY KEY (order_id)
);

create index idx_tbl_log on  tbl_log(sky_imsi);
create index idx_tbl_log_ctime on tbl_log using btree (trade_time);


--2.2 创建第二层表
create table tbl_log_128_0   ( like tbl_log including constraints including defaults including indexes ) inherits ( tbl_log );
create table tbl_log_128_1   ( like tbl_log including constraints including defaults including indexes ) inherits ( tbl_log );
create table tbl_log_128_2   ( like tbl_log including constraints including defaults including indexes ) inherits ( tbl_log );
......
create table tbl_log_128_127 ( like tbl_log including constraints including defaults including indexes ) inherits ( tbl_log );
 
   备注:上面创建了 128 张第二层表。
 
 
--2.3 创建年月配置表并初始化配置数据
create table tmp_date ( table_suff character varying(6) primary key );

insert into tmp_date values ('201112');
insert into tmp_date values ('201201');
insert into tmp_date values ('201202');
insert into tmp_date values ('201203');
insert into tmp_date values ('201204');
insert into tmp_date values ('201205');
insert into tmp_date values ('201206');
insert into tmp_date values ('201207');

   备注:  这个表为配置表,用来存年月信息,初始化数据时只要输入年月信息即可,这里共配置了
               8 个月的分区,下面的 function 会用到这个配置表来创建第三层表。
 

 
--2.4 创建第三层子表和约束关系的 function
CREATE OR REPLACE FUNCTION  func_create_table()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
declare
v_next_month      char(6);
v_second_tabname  varchar(64);
v_third_tabname   varchar(64);
rec_date           RECORD;
v_date            varchar(6);
v_cur_date        varchar(64);
v_tmo_date        varchar(64);

begin

    
for i in 0..127 loop

   v_second_tabname := 'tbl_log_128_' || i::text;
  
   --raise notice 'i=%',i;
   FOR rec_date in (  select table_suff from tmp_date order by tmp_date ) LOOP
       v_date := rec_date.table_suff;
      -- raise notice 'date=% ', rec_date.table_suff;
       v_third_tabname :=v_second_tabname || '_' || v_date;
      
       v_cur_date      := v_date || '01';
       v_tmo_date      := to_char(v_cur_date::date + interval '1 months','yyyymmdd');
      
       --raise notice 'v_cur_date=% ', v_cur_date;
       --raise notice 'v_tmo_date=% ', v_tmo_date;
      execute  'create table ' || v_third_tabname || '( like ' || v_second_tabname || ' including constraints including indexes including defaults) inherits (' || v_second_tabname || ')' ;       
      execute  'alter table ' || v_third_tabname  || ' add constraint '  || v_third_tabname || E'_check check (((trade_time >=\'' || v_cur_date  || E'\') AND (trade_time < \'' || v_tmo_date || E'\')))';
   end loop;

end loop;

end
$function$;


--2.5 执行函数 func_create_table()
select func_create_table():

 
三 总结
     1 这里只简单介绍了三层结构分构表的初始化过程,平常维护成本还是较高的;通过函数创建分区表和约束
        关系大大减少了工作量。
      
     2  关于分区表的查询性能这里不做介绍,之后的博文里会做这方面的分析。 

  评论这张
 
阅读(26456)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2016