ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于操作日志的优化-分区表(二)

关于操作日志的优化-分区表(二)

原创 Linux操作系统 作者:golden_zhou 时间:2012-07-02 15:04:47 0 删除 编辑
CREATE INDEX TOPUP.IDX_OPERATIONLOGBAK_PK ON TOPUP.TAB_BASE_OPERATIONLOG_BAK
(PK)
  TABLESPACE PART_base_INDX
  PCTFREE    10
  INITRANS   2
  MAXTRANS   255
LOCAL ( 
  PARTITION P201204
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_base_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             50M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P201207
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_base_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             50M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P201210
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_base_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             50M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P201301
    LOGGING
    NOCOMPRESS
    TABLESPACE PART_base_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             50M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               ), 
  PARTITION P201304
    NOLOGGING
    NOCOMPRESS
    TABLESPACE PART_base_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             50M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
                BUFFER_POOL      DEFAULT
               )
)
NOPARALLEL;
 
CREATE UNIQUE INDEX TOPUP.PK_TAB_BASE_OPERATIONLOG_BAK ON TOPUP.TAB_BASE_OPERATIONLOG_BAK
(ID)
LOGGING
TABLESPACE PART_base_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             50M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
ALTER TABLE TOPUP.TAB_BASE_OPERATIONLOG_BAK ADD (
  CONSTRAINT PK_TAB_BASE_OPERATIONLOG_BAK
 PRIMARY KEY
 (ID)
    USING INDEX
    TABLESPACE PART_base_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          50M
                NEXT             50M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));
 
insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate < to_date('2011-05-01','yyyy-mm-dd');
commit;
insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-05-01','yyyy-mm-dd') and createdate < to_date('2011-06-01','yyyy-mm-dd');
commit;
以上操作完成
insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-06-01','yyyy-mm-dd') and createdate < to_date('2011-07-01','yyyy-mm-dd');
commit;

insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-07-01','yyyy-mm-dd') and createdate < to_date('2011-08-01','yyyy-mm-dd');
commit;

insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-08-01','yyyy-mm-dd') and createdate < to_date('2011-09-01','yyyy-mm-dd');
commit;

insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-09-01','yyyy-mm-dd') and createdate < to_date('2011-10-01','yyyy-mm-dd');
commit;

insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-10-01','yyyy-mm-dd') and createdate < to_date('2011-11-01','yyyy-mm-dd');
commit;

insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-11-01','yyyy-mm-dd') and createdate < to_date('2011-12-01','yyyy-mm-dd');
commit;
以上操作完成
insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-12-01','yyyy-mm-dd') and createdate < to_date('2012-01-01','yyyy-mm-dd');
commit;

insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2012-01-01','yyyy-mm-dd') and createdate < to_date('2012-02-01','yyyy-mm-dd');
commit;

insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2012-02-01','yyyy-mm-dd') and createdate < to_date('2012-03-01','yyyy-mm-dd');
commit;
insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2012-03-01','yyyy-mm-dd') and createdate < to_date('2012-05-01','yyyy-mm-dd');
commit;
insert /*+ appand*/ into TOPUP.TAB_BASE_OPERATIONLOG_BAK
  select * from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2012-05-01','yyyy-mm-dd') and createdate < to_date('2012-06-01','yyyy-mm-dd');
commit;

以上操作完成
以下操作COPY 索引脚本,以备索引删除,数据处理后再加入
CREATE INDEX TOPUP.IDX_OPERATIONLOG_CREATEDATE ON TOPUP.TAB_BASE_OPERATIONLOG
(CREATEDATE)
LOGGING
TABLESPACE BASE_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE INDEX TOPUP.IDX_OPERATIONLOG_OPERATIONCLAS ON TOPUP.TAB_BASE_OPERATIONLOG
(OPERATIONCLASS)
LOGGING
TABLESPACE BASE_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE INDEX TOPUP.IDX_OPERATIONLOG_OPERATION_ID ON TOPUP.TAB_BASE_OPERATIONLOG
(OPERATION_ID)
LOGGING
TABLESPACE BASE_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             100M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE INDEX TOPUP.IDX_OPERATIONLOG_PK ON TOPUP.TAB_BASE_OPERATIONLOG
(PK)
LOGGING
TABLESPACE BASE_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             50M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

CREATE UNIQUE INDEX TOPUP.PK_TAB_BASE_OPERATIONLOG ON TOPUP.TAB_BASE_OPERATIONLOG
(ID)
LOGGING
TABLESPACE ORDER_INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          50M
            NEXT             50M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
ALTER TABLE TOPUP.TAB_BASE_OPERATIONLOG ADD (
  CONSTRAINT PK_TAB_BASE_OPERATIONLOG
 PRIMARY KEY
 (ID)
    USING INDEX
    TABLESPACE ORDER_INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          50M
                NEXT             50M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                PCTINCREASE      0
               ));

delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate < to_date('2011-05-01','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-05-01','yyyy-mm-dd') and createdate < to_date('2011-05-15','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-05-15','yyyy-mm-dd') and createdate < to_date('2011-06-01','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-06-01','yyyy-mm-dd') and createdate < to_date('2011-06-15','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-06-15','yyyy-mm-dd') and createdate < to_date('2011-07-01','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-07-01','yyyy-mm-dd') and createdate < to_date('2011-07-15','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-07-15','yyyy-mm-dd') and createdate < to_date('2011-08-01','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-08-01','yyyy-mm-dd') and createdate < to_date('2011-08-15','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-08-15','yyyy-mm-dd') and createdate < to_date('2011-09-01','yyyy-mm-dd');
commit;

delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-09-01','yyyy-mm-dd') and createdate < to_date('2011-09-15','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-09-15','yyyy-mm-dd') and createdate < to_date('2011-10-01','yyyy-mm-dd');
commit;

delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-10-01','yyyy-mm-dd') and createdate < to_date('2011-10-15','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-10-15','yyyy-mm-dd') and createdate < to_date('2011-11-01','yyyy-mm-dd');
commit;

delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-11-01','yyyy-mm-dd') and createdate < to_date('2011-11-15','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-11-15','yyyy-mm-dd') and createdate < to_date('2011-12-01','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-12-01','yyyy-mm-dd') and createdate < to_date('2011-12-15','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2011-12-15','yyyy-mm-dd') and createdate < to_date('2012-01-01','yyyy-mm-dd');
commit;

delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2012-01-01','yyyy-mm-dd') and createdate < to_date('2012-02-01','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2012-02-01','yyyy-mm-dd') and createdate < to_date('2012-03-01','yyyy-mm-dd');
commit;
delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2012-03-01','yyyy-mm-dd') and createdate < to_date('2012-05-01','yyyy-mm-dd');
commit;

delete from TOPUP.TAB_BASE_OPERATIONLOG where createdate >= to_date('2012-05-01','yyyy-mm-dd') and createdate < to_date('2012-06-01','yyyy-mm-dd');
commit;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25198367/viewspace-734346/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-09

  • 博文量
    238
  • 访问量
    292295