项目背景
生产环境下，将目标生产表TM_DO_STATUS中2019年以前的数据迁移至历史表，保留2019年的数据。目标表中包含2014年至今的数据，数据量约10G，若采用先备走历史数据再delete的方式，delete的数据量较大，非常慢，对数据库性能影响较大，并且不能保证业务数据的一致性（如果在迁移过程修改了2019年以前的数据行）。另外，Oracle表在delete操作后还需要回收空间才能降低高水位线，回收空间的操作也是非常慢的。
优化方案
将目标原生产表2019年以后的数据写入新表，同时利用触发器审计写入新表期间的数据变更，写入新表完成后，将原生产表重命名为历史表，以防应用更改数据，然后将审计的变更数据（增删改操作）同步至新表，最后将新表重命名为生产表。这样，整个过程中表数据不可用的时间仅为同步变更数据的时间，同步时间很短。
实施细节
准备阶段
原生产表结构
SQL> desc tm_do_status
名称 空值? 类型
-------------------- -------- -------------
UUID NOT NULL VARCHAR2(32)
DO_ID VARCHAR2(60)
ACCEPT_TIME DATE
NAME VARCHAR2(300)
REMARK VARCHAR2(500)
STATUS VARCHAR2(20)
SCAN_FLAG VARCHAR2(4)
ORDER_NO VARCHAR2(250)
MEMO VARCHAR2(450)
FACTORY_ID VARCHAR2(60)
LOCATION VARCHAR2(100)
ERRORCOUNT NUMBER
STATUS_REASON VARCHAR2(10)
CREATE_DATE DATE
CREATOR VARCHAR2(30)
LAST_MODIFY_TIME DATE
LAST_MODIFIER VARCHAR2(30)
TRACKINFO_UUID VARCHAR2(32)
ABNORMAL_DESCRIPTION VARCHAR2(900)
ABNORMAL_TYPE_ID VARCHAR2(10)
TRANS_ID VARCHAR2(20)
创建审计数据表
create table aud_tm_do_status(
old_UUID VARCHAR2(32) ,
old_DO_ID VARCHAR2(60) ,
old_ACCEPT_TIME DATE ,
old_NAME VARCHAR2(300) ,
old_REMARK VARCHAR2(500) ,
old_STATUS VARCHAR2(20) ,
old_SCAN_FLAG VARCHAR2(4) ,
old_ORDER_NO VARCHAR2(250) ,
old_MEMO VARCHAR2(450) ,
old_FACTORY_ID VARCHAR2(60) ,
old_LOCATION VARCHAR2(100) ,
old_ERRORCOUNT NUMBER ,
old_STATUS_REASON VARCHAR2(10) ,
old_CREATE_DATE DATE ,
old_CREATOR VARCHAR2(30) ,
old_LAST_MODIFY_TIME DATE ,
old_LAST_MODIFIER VARCHAR2(30) ,
old_TRACKINFO_UUID VARCHAR2(32) ,
old_ABNORMAL_DESCRIPTION VARCHAR2(900) ,
old_ABNORMAL_TYPE_ID VARCHAR2(10) ,
old_TRANS_ID VARCHAR2(20) ,
new_UUID VARCHAR2(32) ,
new_DO_ID VARCHAR2(60) ,
new_ACCEPT_TIME DATE ,
new_NAME VARCHAR2(300) ,
new_REMARK VARCHAR2(500) ,
new_STATUS VARCHAR2(20) ,
new_SCAN_FLAG VARCHAR2(4) ,
new_ORDER_NO VARCHAR2(250) ,
new_MEMO VARCHAR2(450) ,
new_FACTORY_ID VARCHAR2(60) ,
new_LOCATION VARCHAR2(100) ,
new_ERRORCOUNT NUMBER ,
new_STATUS_REASON VARCHAR2(10) ,
new_CREATE_DATE DATE ,
new_CREATOR VARCHAR2(30) ,
new_LAST_MODIFY_TIME DATE ,
new_LAST_MODIFIER VARCHAR2(30) ,
new_TRACKINFO_UUID VARCHAR2(32) ,
new_ABNORMAL_DESCRIPTION VARCHAR2(900) ,
new_ABNORMAL_TYPE_ID VARCHAR2(10) ,
new_TRANS_ID VARCHAR2(20) ,
systemdate TIMESTAMP
);
创建审计触发器
create or replace trigger tri_aud_tm_do_status
after insert or update or delete on tm_do_status
for each row
begin
INSERT INTO aud_tm_do_status
VALUES (
:OLD.UUID ,
:OLD.DO_ID ,
:OLD.ACCEPT_TIME ,
:OLD.NAME ,
:OLD.REMARK ,
:OLD.STATUS ,
:OLD.SCAN_FLAG ,
:OLD.ORDER_NO ,
:OLD.MEMO ,
:OLD.FACTORY_ID ,
:OLD.LOCATION ,
:OLD.ERRORCOUNT ,
:OLD.STATUS_REASON ,
:OLD.CREATE_DATE ,
:OLD.CREATOR ,
:OLD.LAST_MODIFY_TIME ,
:OLD.LAST_MODIFIER ,
:OLD.TRACKINFO_UUID ,
:OLD.ABNORMAL_DESCRIPTION ,
:OLD.ABNORMAL_TYPE_ID ,
:OLD.TRANS_ID ,
:NEW.UUID ,
:NEW.DO_ID ,
:NEW.ACCEPT_TIME ,
:NEW.NAME ,
:NEW.REMARK ,
:NEW.STATUS ,
:NEW.SCAN_FLAG ,
:NEW.ORDER_NO ,
:NEW.MEMO ,
:NEW.FACTORY_ID ,
:NEW.LOCATION ,
:NEW.ERRORCOUNT ,
:NEW.STATUS_REASON ,
:NEW.CREATE_DATE ,
:NEW.CREATOR ,
:NEW.LAST_MODIFY_TIME ,
:NEW.LAST_MODIFIER ,
:NEW.TRACKINFO_UUID ,
:NEW.ABNORMAL_DESCRIPTION ,
:NEW.ABNORMAL_TYPE_ID ,
:NEW.TRANS_ID ,
sysdate
);
end;
/
--开始写入新生产表时才开启触发器
alter trigger tri_aud_tm_do_status disable;
truncate table aud_tm_do_status;
TM_DO_STATUS建表语句
CREATE TABLE "TMSNEW"."TM_DO_STATUS"
( "UUID" VARCHAR2(32 BYTE) NOT NULL ENABLE,
"DO_ID" VARCHAR2(60 BYTE),
"ACCEPT_TIME" DATE,
"NAME" VARCHAR2(300 BYTE),
"REMARK" VARCHAR2(500 BYTE),
"STATUS" VARCHAR2(20 BYTE),
"SCAN_FLAG" VARCHAR2(4 BYTE),
"ORDER_NO" VARCHAR2(250 BYTE),
"MEMO" VARCHAR2(450 BYTE),
"FACTORY_ID" VARCHAR2(60 BYTE),
"LOCATION" VARCHAR2(100 BYTE),
"ERRORCOUNT" NUMBER DEFAULT 0,
"STATUS_REASON" VARCHAR2(10 BYTE),
"CREATE_DATE" DATE,
"CREATOR" VARCHAR2(30 BYTE),
"LAST_MODIFY_TIME" DATE,
"LAST_MODIFIER" VARCHAR2(30 BYTE),
"TRACKINFO_UUID" VARCHAR2(32 BYTE),
"ABNORMAL_DESCRIPTION" VARCHAR2(900 BYTE),
"ABNORMAL_TYPE_ID" VARCHAR2(10 BYTE),
"TRANS_ID" VARCHAR2(20 BYTE),
CONSTRAINT "PK_TM_DO_STATUS" PRIMARY KEY ("UUID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "INDX" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ENABLE ROW MOVEMENT ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_STATUS_CRDATE" ON "TMSNEW"."TM_DO_STATUS" ("CREATE_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_STATUS_FACID" ON "TMSNEW"."TM_DO_STATUS" ("FACTORY_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_STATUS_ORDERNO" ON "TMSNEW"."TM_DO_STATUS" ("ORDER_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_STATUS_SCANFLAG" ON "TMSNEW"."TM_DO_STATUS" ("SCAN_FLAG")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_STATUS_STATUS" ON "TMSNEW"."TM_DO_STATUS" ("STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_ST_STATUS" ON "TMSNEW"."TM_DO_STATUS" ("DO_ID", "STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
按照TM_DO_STATUS表的结构创建新生产表TM_DO_STATUS_TMP
CREATE TABLE "TMSNEW"."TM_DO_STATUS_TMP"
( "UUID" VARCHAR2(32 BYTE) NOT NULL ENABLE,
"DO_ID" VARCHAR2(60 BYTE),
"ACCEPT_TIME" DATE,
"NAME" VARCHAR2(300 BYTE),
"REMARK" VARCHAR2(500 BYTE),
"STATUS" VARCHAR2(20 BYTE),
"SCAN_FLAG" VARCHAR2(4 BYTE),
"ORDER_NO" VARCHAR2(250 BYTE),
"MEMO" VARCHAR2(450 BYTE),
"FACTORY_ID" VARCHAR2(60 BYTE),
"LOCATION" VARCHAR2(100 BYTE),
"ERRORCOUNT" NUMBER DEFAULT 0,
"STATUS_REASON" VARCHAR2(10 BYTE),
"CREATE_DATE" DATE,
"CREATOR" VARCHAR2(30 BYTE),
"LAST_MODIFY_TIME" DATE,
"LAST_MODIFIER" VARCHAR2(30 BYTE),
"TRACKINFO_UUID" VARCHAR2(32 BYTE),
"ABNORMAL_DESCRIPTION" VARCHAR2(900 BYTE),
"ABNORMAL_TYPE_ID" VARCHAR2(10 BYTE),
"TRANS_ID" VARCHAR2(20 BYTE),
CONSTRAINT "PK_TM_DO_STATUS_TMP" PRIMARY KEY ("UUID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "INDX" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ENABLE ROW MOVEMENT ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_STATUS_TMP_CRDATE" ON "TMSNEW"."TM_DO_STATUS_TMP" ("CREATE_DATE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_STATUS_TMP_FACID" ON "TMSNEW"."TM_DO_STATUS_TMP" ("FACTORY_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_STATUS_TMP_ORDERNO" ON "TMSNEW"."TM_DO_STATUS_TMP" ("ORDER_NO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_STATUS_TMP_SCANFLAG" ON "TMSNEW"."TM_DO_STATUS_TMP" ("SCAN_FLAG")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_STATUS_TMP_STATUS" ON "TMSNEW"."TM_DO_STATUS_TMP" ("STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
CREATE INDEX "TMSNEW"."IDX_TM_DO_ST_STATUS_TMP" ON "TMSNEW"."TM_DO_STATUS_TMP" ("DO_ID", "STATUS")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TMSNEW" ;
迁移阶段
开启触发器，并立刻开始写入新表操作。
alter trigger aud_tm_do_status enable;
insert into tm_do_status_tmp select * from tm_do_status where CREATE_DATE>to_date('2019-01-01','yyyy-mm-dd');
将原生产表重命名，然后开始同步变更数据
alter table tm_do_status rename to tm_do_status_his;
--删除old_uuid非空的,这些行被删除或更新过
delete from tm_do_status_tmp where uuid in
(select old_uuid from aud_tm_do_status where create_date>to_date('2019-01-01','yyyy-mm-dd'));
--通过new_uuid反查原表，同步更新过的或新写入的行(new_uuid非空)
insert into tm_do_status_tmp select * from tm_do_status where uuid in
(select new_uuid from aud_tm_do_status where create_date>to_date('2019-01-01','yyyy-mm-dd'));
--将tmp表重命名为新的生产表
alter table tm_do_status_tmp rename to tm_do_status;
总结：
经测试，整个迁移过程，应用对生产表不可访问时间不到一秒，下面总结一下迁移过程需要执行的SQL脚本（假设触发器和审计表结构以及新表结构均已准备完成）
--清空审计表
truncate table aud_tm_do_status;
--开启触发器
alter trigger tri_aud_tm_do_status enable;
--立刻开始传输数据
insert into tm_do_status_tmp select * from tm_do_status where CREATE_DATE>to_date('2019-01-01','yyyy-mm-dd');
commit;
--重命名生产表
alter table tm_do_status rename to tm_do_status_his;
--同步变更数据
delete from tm_do_status_tmp where uuid in (select old_uuid from aud_tm_do_status where create_date>to_date('2019-01-01','yyyy-mm-dd'));
insert into tm_do_status_tmp select * from tm_do_status where uuid in (select new_uuid from aud_tm_do_status where create_date>to_date('2019-01-01','yyyy-mm-dd'));
commit;
--重命名新生产表
alter table tm_do_status_tmp rename to tm_do_status;
