ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 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博客 ” ，链接：https://blog.itpub.net/25198367/viewspace-734346/ ，如需转载，请注明出处，否则将追究法律责任。

0
0
上一篇： 关于操作日志的优化-分区表(一)
下一篇： 大表自动增加删除分区案例
请登录后发表评论 登录
全部评论
golden_zhou

注册时间：2011-03-09

  • 博文量
    238
  • 访问量
    344465

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593