千万级数据查询处理方案

前段时间有一个项目涉及到了很多的查询与报表生成。因为单表数据量已经达到了千万级，再多表关联已经达到亿级数据了。之前没有接触过这么大数据量的处理，所以刚接到这个项目时有点脑瓜子疼。

最开始我还是想通过优化sql与索引方式来提高查询的性能，但是效果并不明显。（由于业务的需求，sql关联了很多表，其真实的执行时间已经超过一分钟了【不能忍了】）

然后找了公司的dba讨论如何解决，我提出的第一个方案是采用中间表存储历史数据，由于业务需求查询条件涉及了时间，所以中间表存储的维度是以时间来存储的。最后查询实时的数据是先查询这张历史表的数据加当天的实时数据出结果的。

中间表的存储过程：

reate or replace PROCEDURE JOB_PROC_OUTPATIENTS IS
V_DATE VARCHAR2(32);  
VE_DATE VARCHAR2(32);  
BEGIN
    V_DATE:=TO_CHAR(SYSDATE-1,'YYYY-MM-DD hh24:mi:ss ');
    VE_DATE:=TO_CHAR(SYSDATE,'YYYY-MM-DD hh24:mi:ss ');
    --当日期大于END_DATE的时候推出循环
  INSERT INTO OUTPATIENTS(KEY,NUM,CHOSCODE,MZDATE,机构级别,机构类别,是否民营,SBK_NUM,JE,JE2) 
   select SEQ_OUTPATIENTS_KEY.NEXTVAL key,NUM,CHOSCODE,MZDATE,机构级别,机构类别,是否民营,SBK_NUM,JE,JE2
    from(
    select 
    sum(m. NUM) NUM,
       m.CHOSCODE,
       to_date(m.MZDATE,'yyyy-MM-dd') MZDATE,
       机构级别,机构类别,是否民营,
       sum(m.SBK_NUM) SBK_NUM,
       sum(m.JE) JE,
       sum(m.JE2) JE2
  from (select /*+parallel(t,32)parallel(a,32)*/
         count(1) NUM,
         t.choscode CHOSCODE,
         to_char(t.就诊时间, 'yyyy-MM-dd') MZDATE,
         机构级别,机构类别,是否民营
        ,sum(c.金额) as JE,
         sum(p.FARMERS_COMPENSATION) as JE2,
         sum(decode(a.卡类型, '1', 1, 0)) SBK_NUM
          from 门诊医嘱主表 t
          left join 一卡通业务 a
            on a.流水号 = t.卡流水
           and a.choscode = t.choscode
           and nvl(a.src_flag, '0') <> '1'
          left join sysdicthospital t1
            on t1.choscode = t.choscode
           and t1.isjkdaupload = '0'
            left join (select /*+parallel(a,32)parallel(e,32)parallel(f,32)*/
                     sum(nvl(f.金额, 0)) 金额, e.卡流水, e.choscode
                      from 门诊医嘱主表 a, 门诊处方表 e, 门诊处方明细表 f
                     where e.choscode = f.choscode
                       and e.处方号 = f.处方号
                       and a.choscode = e.choscode
                       and a.choscode = f.choscode
                       and a.卡流水 = e.卡流水
                       and nvl(a.src_flag, '0') <> '1'
                        and a.就诊时间 >=to_date(V_DATE,'yyyy-mm-dd hh24:mi:ss')
                        and a.就诊时间 <to_date(VE_DATE,'yyyy-mm-dd hh24:mi:ss')
                     group by e.卡流水, e.choscode) c
            on c.卡流水 = t.卡流水
             left join 农合结算表 p
            on a.流水号 = p.卡流水
         where nvl(t.src_flag, '0') <> '1'
         and t.就诊时间 >=to_date(V_DATE,'yyyy-mm-dd hh24:mi:ss')
           and t.就诊时间 <to_date(VE_DATE,'yyyy-mm-dd hh24:mi:ss')
         group by t.choscode, to_char(t.就诊时间, 'yyyy-MM-dd'),机构级别,机构类别,是否民营
       ) m
 group by m.CHOSCODE, to_date(m.MZDATE,'yyyy-MM-dd'),机构级别,机构类别,是否民营);
  END;

 

创建每天跑的任务：

DECLARE 

  JOB NUMBER; 

BEGIN 

  DBMS_JOB.SUBMIT(JOB, ' JOB_PROC_OUTPATIENTS;', SYSDATE, 'TRUNC(SYSDATE + 1)'); 

END/ 

COMMIT;

后期我们又改用了物化视图来替换中间表：

drop MATERIALIZED VIEW OUTPATIENTS;
alter table OUTPATIENTS rename to OUTPATIENTS_OLD;
CREATE MATERIALIZED VIEW OUTPATIENTSCREATE MATERIALIZED VIEW OUTPATIENTS
BUILD IMMEDIATE 
REFRESH  ON demand
start with sysdate next trunc(sysdate,'dd')+1+1/24 
AS
select rownum   as key,
       NUM,
       CHOSCODE,
       to_date(MZDATE, 'yyyy-MM-dd') MZDATE,
       机构级别,
       机构类别,
       是否民营,
       SBK_NUM,
       sysdate  as create_time
  from (select /*+parallel(t,16)parallel(a,16)*/
         count(1) NUM,
         t.choscode CHOSCODE,
         to_char(t.就诊时间, 'yyyy-MM-dd') MZDATE,
         机构级别,
         机构类别,
         是否民营,
         sum(decode(a.卡类型, '1', 1, 0)) SBK_NUM
          from 门诊医嘱主表 t
          left join 一卡通业务 a
            on a.流水号 = t.卡流水
           and a.choscode = t.choscode
           and nvl(a.src_flag, '0') <> '1'
          left join sysdicthospital t1
            on t1.choscode = t.choscode
           and t1.isjkdaupload = '0'
         where nvl(t.src_flag, '0') <> '1'
         group by t.choscode,
                  to_char(t.就诊时间, 'yyyy-MM-dd'),
                  机构级别,
                  机构类别,
                  是否民营);