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    )  );

3.删除数据，删除一个的数据大概1千万

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 ||''')';

4.快速筛选大表中相关数据

--创建临时表   沉淀本期数据到临时表
   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';    

5.高效率插入数据

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

6.创建索引

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

7.删除索引

 EXECUTE IMMEDIATE ' drop index TG_CDR_CD_D_HIGH_TEM_index ';