oralce大数据处理常用方法

oralce大数据处理常用方法：

1.创建分区表

create table TG_CDR_CD_D_TEM
(
  DEAL_TIME       VARCHAR2(8),USER_ID         NUMBER(16),MSISDN          VARCHAR2(40),MOC_CALL_TIMERS NUMBER(12)
)

partition by list (DEAL_TIME)
(
? partition TG_CDR_CD_D_20140710 values ('20140710')
? ? tablespace CMRSDATA
? ? pctfree 10
? ? pctused 40
? ? initrans 1
? ? maxtrans 255
? ? storage
? ? (
? ? ? initial 8M
? ? ? next 1M
? ? ? minextents 1
? ? ? maxextents unlimited
? ? ),? partition TG_CDR_CD_D_20140720 values ('20140720')
? ? tablespace CMRSDATA
? ? pctfree 10
? ? pctused 40
? ? initrans 1
? ? maxtrans 255
? ? storage
? ? (
? ? ? initial 8M
? ? ? next 1M
? ? ? minextents 1
? ? ? maxextents unlimited
? ? )
? 
);

BEGIN
      EXECUTE IMMEDIATE 'alter table TG_CDR_CD_D_TEM drop partition TG_CDR_CD_D_<span style="font-family: Arial,Helvetica,sans-serif;">20140710‘</span>;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;

 execute immediate 'alter table TG_CDR_CD_D_TEM add partition TG_CDR_CD_D_' ||  V_DAY || ' values('''|| V_DAY ||''')';

--创建临时表   沉淀本期数据到临时表
   EXECUTE IMMEDIATE 'create table TG_CDR_CD_D_TEM1 nologging parallel 8 as 
        SELECT /*+ parallel(c,8) */
           '||V_DAY||' V_DAY,USER_ID,MSISDN,SUM(MOC_CALL_TIMERS) MOC_CALL_TIMERS
      FROM TG_CDR_CD_D_TEM c
     WHERE  C.DEAL_TIME  >= '||BEGIN_DATE||'
       AND  C.DEAL_TIME  <= '||END_DATE||'
     GROUP BY USER_ID,MSISDN';    

 EXECUTE IMMEDIATE 'INSERT INTO TG_CDR_CD_D_TEM nologging
          SELECT /*+ parallel(c,8) */
                 V_DAY,MOC_CALL_TIMERS
          FROM TG_CDR_CD_D_TEM1 c ';
  COMMIT;

EXECUTE IMMEDIATE ' create index TG_CDR_CD_D_HIGH_TEM_index on TG_CDR_CD_D_HIGH_TEM ( USER_ID)';

 EXECUTE IMMEDIATE ' drop index TG_CDR_CD_D_HIGH_TEM_index ';