今天因为要对一套数据库的数据抽取进行io优化，希望通过修改表结构将抽取io降下来，因为抽取只针对标签HAVE_FLAG为“0”的值进行抽取，抽取之后更新HAVE_FLAG为其他值，所以计划将HAVE_FLAG在时间分区里面再按照“0”和其他值进行list分区，控制SQL扫描的范围，再配合索引，降低IO。

准备工作

提取表和索引的语句。

SELECT DBMS_METADATA.GET_DDL(OBJECT_TYPE => 'TABLE', NAME => 'CONTACT_CLSLIST') ||  DBMS_METADATA.GET_DEPENDENT_DDL(OBJECT_TYPE => 'INDEX',  BASE_OBJECT_NAME => 'CONTACT_CLSLIST') FROM DUAL ;

根据生产表的结构修改中间表

CREATE TABLE "INCRCBPS8"."CONTACT_CLSLIST_TEST"   (    "CUST_ID" NUMBER(38,0),        "CONTACT_SEQ" NUMBER(38,0),        "CONTACT_CLASS_SEQ" NUMBER(38,0),        "CONTACT_CLASS_CODE" CHAR(1),        "CONTACT_NO" VARCHAR2(680),        "CONTACT_EXT" VARCHAR2(680),        "FROM_BRANCH" VARCHAR2(30),        "TRANS_ID" VARCHAR2(60),        "COMMIT_TIMESTAMP" VARCHAR2(20),        "COMMIT_CSN" NUMBER,        "OP_SEQ" NUMBER,        "LAST_OP_FLAG" VARCHAR2(20),        "LAST_TIME" DATE DEFAULT SYSDATE,        "HAVE_FLAG" VARCHAR2(50) DEFAULT '0',        "HAVE_FLAG_1" VARCHAR2(50) DEFAULT '0',        "HAVE_FLAG_2" VARCHAR2(50) DEFAULT '0',        "HAVE_FLAG_3" VARCHAR2(50) DEFAULT '0',        "HAVE_FLAG_4" VARCHAR2(50) DEFAULT '0'   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  NOLOGGING  STORAGE(  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "CMDS"  PARTITION BY RANGE ("LAST_TIME") INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))  SUBPARTITION BY LIST ("HAVE_FLAG")  SUBPARTITION TEMPLATE (    SUBPARTITION "SUBP0" VALUES ( '0' ),    SUBPARTITION "SUBOTHER" VALUES ( default ) ) (PARTITION "P1"  VALUES LESS THAN (TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255  STORAGE(  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)  TABLESPACE "CMDS"  NOLOGGING )

中间表创建完毕之后，创建相关索引

将步骤1的SQL中的表名CONTACT_CLSLIST替换为CONTACT_CLSLIST_TEST创建中间表

中间表新增字段remark01

alter table CONTACT_CLSLIST_TEST add remark01 varchar2(10) default 'A';

检查能否进行重定义,过程执行成功即说明可以重定义，因为测试表没有主键，所以要写上用rowid，如果有主键，就是CONS_USE_PK

beginDBMS_REDEFINITION.CAN_REDEF_TABLE('cmds','CONTACT_CLSLIST',DBMS_REDEFINITION.CONS_USE_ROWID);end;/

如果有主键，就是CONS_USE_PK

beginDBMS_REDEFINITION.CAN_REDEF_TABLE('cmds','CONTACT_CLSLIST',DBMS_REDEFINITION.CONS_USE_PK);end;/

开始重定义表

运行start_redef_table过程

BEGIN  dbms_redefinition.start_redef_table(uname => CMDS,orig_table => 'CONTACT_CLSLIST', int_table => 'CONTACT_CLSLIST_TEST', options_flag => DBMS_REDEFINITION.cons_use_rowid); END; /

开始同步中间表

BEGIN  dbms_redefinition.sync_interim_table(uname => 'CMDS',orig_table => 'CONTACT_CLSLIST',int_table => 'CONTACT_CLSLIST_TEST');  END;/

完成同步

BEGIN  dbms_redefinition.finish_redef_table(uname => 'CMDS',orig_table => 'CONTACT_CLSLIST',int_table => 'CONTACT_CLSLIST_TEST');  END;/

删除中间表

drop table CONTACT_CLSLIST_TEST;

修改索引名称

alter index "IDX_CONTACT_CLSLIST_NO_mid" rename to IDX_CONTACT_CLSLIST_NO;  alter index "IDX_CONTACT_CLSLIST_INDEX_mid" rename to IDX_CONTACT_CLSLIST_INDEX;

执行完以上的9个步骤，新增字段就创建成功了。