文档章节

oracle一卡通消费数据分区表建立及自动建立分区存储过程

李狗蛋丶
 李狗蛋丶
发布于 2016/11/05 09:55
字数 801
阅读 30
收藏 0

由于一卡通消费数据过多,不利于按时间统计计算,故采取建立分区表的方式来优化查询效率,建立过程如下

  1. 创建一卡通消费数据分区表,按时间进行分区,建立时可暂时只创建一个最早时间分区,后续分区通过存储过程自动创建。这里把一卡通消费表直接复制过来,由于操作时间在自动建立分区时不好处理,故修改为varchar类型。

    create table M_REC_CONSUME
    (
    ECODE VARCHAR2(20) default 00000000 not null,
    ID NUMBER not null,
    NOTECASE NUMBER,
    CUSTOMERID NUMBER not null,
    OUTID VARCHAR2(18) not null,
    CARDSN NUMBER not null,
    OPCOUNT NUMBER,
    OPDT VARCHAR2(100) not null,
    ODDFARE NUMBER not null,
    OPFARE NUMBER not null,
    MNGFARE NUMBER not null,
    ACCCODE NUMBER not null,
    DSCRP VARCHAR2(20) not null,
    TERMID NUMBER not null,
    RECNO NUMBER not null,
    UPLOADDATE DATE not null,
    SAMCARDNO NUMBER not null,
    SAMTRADENO NUMBER not null,
    TRADECARDTYPE NUMBER not null,
    TAC NUMBER,
    ASN VARCHAR2(20),
    UPDATEFLAG VARCHAR2(20) not null,
    UPDATEDT DATE default sysdate not null,
    DOWNDT DATE,
    SOURCETABLE NUMBER not null
    )
    partition by range (opdt)

    (

    partition p201201 values less than ('2012-02-01 00:00:00')

    --创建分区以历史有效记录的第一个月开始,这里要注意分区条件为less than
    )

  2. 创建存储过程自动建立到当前月份的分区,并写入日志。
    存储过程创建:

    CREATE OR REPLACE PROCEDURE GENE_AUTO_ADD_PARTITION
    IS
    /***********************************************************************
    /* Procedure : GENE_AUTO_ADD_PARTITION
    /* Description : General类程序,用于自动增加分区表的分区 == 计算到part_month为止,该增加多少个分区
    /* Logic : 1、找出需要增加分区的分区表,以及这些分区表的最近分区
    /* 2、计算这些分区表要增加几个分区
    /* 3、循环为这些分区表增加分区,直至增加到part_month分区为止
    /* 4、记录日志
    /* Warning : 确保有足够权限,需显示授权:Grant select on dba_segments to user;
    /* eg : call gene_auto_add_partition;
    /* Author YYYY-MM-DD DSC
    /* lihan 2015-04-24 初始版本
    /*
    /*********************************************************************/
    part_month varchar2(100);
    vcurrPartitionName varchar2(100);
    vhistPartitionName varchar2(100);
    vCount number;
    vDDLSql varchar2(1024);
    vLessthan varchar2(100);
    i1 number;
    i2 number;

    BEGIN
    --初始化参数值
    part_month :=to_char(sysdate + 90,'yyyymm');--可提前新建三个月后的分区,预留问题发现及修复时间
    i1 :=0;
    --format current partition name
    vcurrPartitionName := 'p'||part_month;

    For x in (
    SELECT segment_name , max(partition_name) partition_name, max(tablespace_name) tablespace_name
    FROM dba_segments a
    WHERE owner = 'YBG_CP'
    AND segment_type = 'TABLE PARTITION'
    AND segment_name not like '%BIN%'
    AND not exists (
    SELECT 1 FROM dba_segments b
    WHERE a.segment_name = b.segment_name
    AND b.partition_name = vcurrPartitionName)
    GROUP BY a.segment_name
    ) LOOP

    --计算需要增加几个分区
    SELECT (extract(year from to_date(part_month,'yyyymm')) - extract(year from to_date(substr(x.partition_name,2,6),'yyyymm')))*12 +
    (extract(month from to_date(part_month,'yyyymm')) - extract(month from to_date(substr(x.partition_name,2,6),'yyyymm')))
    INTO vCount
    FROM dual;

    For j in 1..vCount LOOP


    --get history Partition name
    vhistPartitionName := 'p'||to_char(add_months(to_date(substr(x.partition_name,2,6),'yyyymm'),j),'yyyymm');
    vLessthan := to_char(add_months(to_date(substr(x.partition_name,2,6)||'01','yyyy-mm-dd hh24:mi:ss'),j+1),'yyyy-mm-dd hh24:mi:ss');

     


    vDDLSql := 'ALTER TABLE M_REC_CONSUME ADD PARTITION '||vhistPartitionName||' VALUES LESS THAN ('''||vLessthan||''' ) TABLESPACE '||x.tablespace_name;

    DBMS_UTILITY.exec_ddl_statement(vDDLSql);

    i1:= i1 + 1;

    END LOOP;

    i1 := i1;
    i2 := i2 + 1;

    END LOOP;

    insert into m_rec_consume_log(updtime,prc_name,dscp)values(sysdate,'GENE_AUTO_ADD_PARTITION','为M_REC_CONSUME创建三个月后子分区成功,共创建了'||i1||'个分区');
    commit;
    EXCEPTION
    WHEN others THEN
    raise;
    rollback;
    insert into m_rec_consume_log(updtime,prc_name,dscp)values(sysdate,'GENE_AUTO_ADD_PARTITION','更新分区失败!');

    END;

    还需创建一个日志表:

    create table m_rec_consume_log
    (
    updtime date,
    prc_name varchar2(1000),
    dscp varchar2(1000)
    )

  3. 设定job定时执行存储过程完成分区自动创建。

 

© 著作权归作者所有

李狗蛋丶
粉丝 1
博文 28
码字总数 20793
作品 0
广州
数据库管理员
私信 提问
oracle常见基础知识

oracle使用的脚本语言称为什么? PL-SQL oracle中游标的分类? oracle中游标分为两类:显示游标和隐式游标 oracle中function与procedure的区别? (1) function一定要有返回值,但procedure不...

Gong_Chen
2016/04/04
103
0
segment,extent,block

1.segment 段的不同类型: 分区表 tablepartition 表 table 索引 index 簇 cluster 索引分区indexpartition 索引表 index-organized table 临时段 temporary segment 还原段 undosegment 大对......

长平狐
2013/09/17
264
0
mysql 动态新建以及删除分区表

因为项目需要,最近研究了一下在mysql数据库下如何动态新建以及删除分区表。如果全部借助存储过程的话,新建以及删除分区表在逻辑上比较死板、不灵活,而且还容易出错。因此,我新建了一个数...

heiansan
2013/06/06
2.8K
0
Oracle表空间、段·区·块

在Oracle学习过程中,存储结构,表段区块可能是每个初学者都要涉及到的概念。表空间、段、分区和数据块分别表示了Oracle进行数据存储的不同层次和结构。了解清楚这几个结构,有助于我们奠定一...

0o清风徐来o0
2012/10/17
67
0
oracle系列(四)oracle事务和常用数据库对象

博主QQ:819594300 博客地址:http://zpf666.blog.51cto.com/ 有什么疑问的朋友可以联系博主,博主会帮你们解答,谢谢支持! 前言:本次内容的相关知识点我们在学习sqlserver2008R2的时候介绍...

Mr大表哥
2017/05/27
0
0

没有更多内容

加载失败,请刷新页面

加载更多

用 Sphinx 搭建博客时,如何自定义插件?

之前有不少同学看过我的个人博客(http://python-online.cn),也根据我写的教程完成了自己个人站点的搭建。 点此:使用 Python 30分钟 教你快速搭建一个博客 为防有的同学不清楚 Sphinx ,这...

王炳明
昨天
2
0
黑客之道-40本书籍助你快速入门黑客技术免费下载

场景 黑客是一个中文词语,皆源自英文hacker,随着灰鸽子的出现,灰鸽子成为了很多假借黑客名义控制他人电脑的黑客技术,于是出现了“骇客”与"黑客"分家。2012年电影频道节目中心出品的电影...

badaoliumang
昨天
12
0
很遗憾,没有一篇文章能讲清楚线程的生命周期!

(手机横屏看源码更方便) 注:java源码分析部分如无特殊说明均基于 java8 版本。 简介 大家都知道线程是有生命周期,但是彤哥可以认真负责地告诉你网上几乎没有一篇文章讲得是完全正确的。 ...

彤哥读源码
昨天
13
0
jquery--DOM操作基础

本文转载于:专业的前端网站➭jquery--DOM操作基础 元素的访问 元素属性操作 获取:attr(name);$("#my").attr("src"); 设置:attr(name,value);$("#myImg").attr("src","images/1.jpg"); ......

前端老手
昨天
6
0
Django的ChoiceField和MultipleChoiceField错误提示,选择一个有效的选项

在表单验证时提示错误:选择一个有效的选项 例如有这样一个表单: class ProductForm(Form): category = fields.MultipleChoiceField( widget=widgets.SelectMultiple(), ...

编程老陆
昨天
13
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部