海量資料處理_表分割槽

分割槽act_log表：

執行情況參考：
資料:2億條
insert as select parallel 8=〉undo size:0 time:524 sec
gather_table_stats=>undo size:0 time:360 sec
create index parallel 8=>undo size:0 time:400 sec

1,建立分割槽表：
注意：用生成的sql替換建立分割槽表ddl中的分割槽子句，並註釋此查詢語句。
SELECT *
  FROM (SELECT /*+ parallel(t,8) */
         'partition p' || to_char(action_date, 'YYYYMM') ||
         ' values less than (to_date(''' ||
         to_char(add_months(to_date(to_char(action_date, 'YYYYMM') || '01',
                                    'YYYYMMDD'),
                            1),
                 'YYYYMMDD') || ''',''YYYYMMDD'')),'
          FROM act_log t
         GROUP BY to_char(action_date, 'YYYYMM')
        UNION
        SELECT 'partition p' || to_char(add_months(SYSDATE, 1), 'YYYYMM') ||
               ' values less than (to_date(''' ||
               to_char(to_date(to_char(add_months(SYSDATE, 2), 'YYYYMM') || '01',
                               'YYYYMMDD'),
                       'YYYYMMDD') || ''',''YYYYMMDD'')),'
          FROM dual
        UNION
        SELECT 'partition p' || to_char(add_months(SYSDATE, 2), 'YYYYMM') ||
               ' values less than (to_date(''' ||
               to_char(to_date(to_char(add_months(SYSDATE, 3), 'YYYYMM') || '01',
                               'YYYYMMDD'),
                       'YYYYMMDD') || ''',''YYYYMMDD'')),'
          FROM dual
        UNION
        SELECT 'partition pmax values less than (maxvalue)' FROM dual)
 ORDER BY 1;
exit;

create table ACT_LOG_PART
(
  USER_ID     INTEGER not null,
  ACTION_DATE TIMESTAMP(6) not null,
  TYPE        VARCHAR2(50 BYTE) not null,
  DETAIL      VARCHAR2(4000 BYTE) not null,
  CHNL        VARCHAR2(1 BYTE) default '1'
) partition by range(action_date)(
partition p201101 values less than (to_date('20110201','YYYYMMDD')),
partition p201102 values less than (to_date('20110301','YYYYMMDD')),
partition p201103 values less than (to_date('20110401','YYYYMMDD')),
partition p201104 values less than (to_date('20110501','YYYYMMDD')),
partition pmax values less than (maxvalue)
);

2,遷移
--time,undo space,redo space
alter table act_log_part nologging;

insert /*+ append,parallel(tp,8) */ into ACT_LOG_PART tp
select /*+ parallel(t,8) */ * from act_log t;
commit;

alter table act_log_part logging;

analyze table:
begin
  dbms_stats.gather_table_stats(ownname => user,tabname => 'ACT_LOG_PART',degree => 8);
end;
/ 

3,create index
create index IDX_ACT_LOG_USERID_ACTDATE on ACT_LOG_PART (USER_ID,ACTION_DATE)
local
parallel 8
nologging;

alter index IDX_ACT_LOG_USERID_ACTDATE logging noparallel;

4,exchange table
rename action_LOG to action_LOG_bak;

rename ACT_LOG_PART to act_log;

分割槽act_log表完成。

5,分割槽維護,請資料庫管理人員手工維護，維護說明如下：
--每月月初，刪除包含13個月前資料的分割槽，並新增下下個月的分割槽。
--分割槽命名規則為：'p' + 'YYYYMM',其中包含'YYYYMM'月份的資料。
--如：partition p200901 values less than (to_date('20090201','YYYYMMDD')),

--刪除分割槽：
--建議在刪除分割槽前備份該分割槽，如採用外部表方式備份。
--alter table act_log drop partition partition_name;
--如：當前是2010年3月，要刪除的分割槽為：p200902，刪除命令為：
--alter table act_log drop partition p200902;

--增加分割槽：
--alter table act_log split partition pmax at (split_date)
--into (partition l_partition_name, partition pmax);
--如：當前是2010年3月，要增加5月份的分割槽：p201005,增加命令為：
--alter table act_log split partition pmax at (to_date( '20100601','YYYYMMDD'))
--into (partition p201005, partition pmax);