資料表分割槽分割與刪除歷史資料

1，查詢

PMMSTKNWIP;select * from PMMSTKNWIP

2，分割分割槽

ALTER TABLE PMMSTKNWIP SPLIT PARTITION P2011
AT('20120101')
INTO (PARTITION P2011,PARTITION P2012);

ALTER TABLE PMMSTKNWIP SPLIT PARTITION P2012
AT('20130101')
INTO (PARTITION P2012,PARTITION P2013);

3，刪除分割槽，同時刪除資料

ALTER TABLE PMMSTKNWIP drop  PARTITION P2010;
ALTER TABLE PMMSTKNWIP drop  PARTITION P2011;

4，刪除索引

ALTER TABLE PMMSTKNWIP
MODIFY PRIMARY KEY disable;------對主鍵
 drop index PK_MSTKNWI ;
-- Create/Recreate indexes
drop index IDX_0002;
drop index IDX_0006;
drop index IDX_0007 ;
drop index IDX_001 ;
drop index IDX_003 ;
drop index IDX_004 ;
drop index IDX_COMBINE ;
drop index INDEX_CONFDATE ;
drop index INDEX_PN ;

5，重建索引
ALTER TABLE PMMSTKNWIP
MODIFY PRIMARY KEY enable------主鍵

-- Create/Recreate indexes
create index IDX_0002 on PMMSTKNWIP (STKNWIP002)
  tablespace NDX_SFCDATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_0006 on PMMSTKNWIP (STKNWIP006)
  tablespace NDX_SFCDATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_0007 on PMMSTKNWIP (STKNWIP007)
  tablespace NDX_SFCDATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_001 on PMMSTKNWIP (STKNWIP001)
  tablespace NDX_SFCDATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_003 on PMMSTKNWIP (STKNWIP003)
  tablespace NDX_SFCDATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_004 on PMMSTKNWIP (STKNWIP004)
  tablespace NDX_SFCDATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index IDX_COMBINE on PMMSTKNWIP (STKNWIP001, STKNWIP002, STKNWIP006)
  tablespace NDX_SFCDATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index INDEX_CONFDATE on PMMSTKNWIP (CONFDATE)
  tablespace NDX_SFCDATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );
create index INDEX_PN on PMMSTKNWIP (STKNWIP003, STKNWIP004, LAYER)
  tablespace NDX_SFCDATA
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 2M
    next 2M
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/15187685/viewspace-740609/，如需轉載，請註明出處，否則將追究法律責任。