Oracle一次4亿数据表清理，清理后1.2亿

oracle
oracle
发布日期:   2019-07-13
文章字数:   1.5k
阅读时长:   6 分
阅读次数:  

Oracle一次4亿数据表清理，清理后1.2亿

背景

​ 生产有张表数据量4亿左右，由于任务在表中加了一个字段GBAFLAG，默认值0，用alter脚本

alter table RICH_CUSTLOKPROFITNORMAL add GBAFLAG NUMBER(1) default 0;

执行的时候非常慢，跑了4个小时还没搞定，因此趁着这个机会，同create table as的形式创建表，并且进行一次数据清理。

​ 生产库通过OGG进行的主从备份，从库作为查询统计模块数据库，因此在数据清理的时候会考虑OGG的一些东西。整个过程耗时1小时左右。

清理思路

  • 创建保留数据表

  • 核对保留表数据是否为需要数据

  • OGG同步表停止OGG

  • 删除原表索引, 重命名原表为his表

  • 将保留数据表重命名为原表, 创建索引和主键

  • 同步数据库删除原表索引，重命名原表

  • 源数据库导出表并导入同步数据库

  • 启动OGG，源端修改一条数据测试同步情况

  • 源端和同步端做表分析

步骤1,2在不停机的时候进行操作，后面步骤在停机后做

实操脚本

1. 创建保留数据表

使用create as方式创建表，为了提高效率，创建表时，开了并行。将新增加字段查询出来作为一个字段创建到表中。
create as方式创建表注意表字段默认值需要检查

--创建保留数据表-保留到期后3个月数据
create table RICH_CUSTLOKPROFITNORMAL_X96
PARALLEL(DEGREE 16)
as 
select t.*,0 as GBAFLAG
     from rich_custlokprofitnormal t, rich_custinterest i
    where t.custinterestid = i.id
      and i.maturitydate >= to_date('20190322', 'yyyymmdd');
--设置表默认值
alter table RICH_CUSTLOKPROFITNORMAL_X96 modify SERIALNO not null; 
alter table RICH_CUSTLOKPROFITNORMAL_X96 modify CASHPROFIT default 0;
alter table RICH_CUSTLOKPROFITNORMAL_X96 modify BILLPROFIT default 0;
alter table RICH_CUSTLOKPROFITNORMAL_X96 modify STATE default 0;
alter table RICH_CUSTLOKPROFITNORMAL_X96 modify FLAG default 0;
alter table RICH_CUSTLOKPROFITNORMAL_X96 modify GBAFLAG default 0;
-- Add comments to the table 
comment on table RICH_CUSTLOKPROFITNORMAL_X96
  is '客户收益表';
-- Add comments to the columns 
comment on column RICH_CUSTLOKPROFITNORMAL_X96.serialno
  is '交易流水号';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.account
  is '客户理财帐户';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.custaccount
  is '客户银行账号(收益返还用)';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.issueid
  is '产品代码';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.type
  is '收益类型 收益类型 0--正常付息,1--提前赎回,2--到期本息,3--解冻付息,4--活期收益';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.startdate
  is '计息开始';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.enddate
  is '计息结束';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.cashunit
  is '现钞份额';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.billunit
  is '现汇份额';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.cashprofit
  is '现钞收益';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.billprofit
  is '现汇收益';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.execdate
  is '处理时间';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.state
  is '0--未计算,1--已计算';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.flag
  is '0--未入表,1--已入表,2--不入账';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.benchmark
  is '客户业绩基准';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.cashmanaexpense
  is '现钞管理费';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.ceilingyield
  is '产品峰顶收益率';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.postactualyield
  is '投资组合实际收益率';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.payccy
  is '支付货币';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.billmanaexpense
  is '现汇管理费';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.custinterestid
  is '付息ID';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.issueprofitid
  is '收益确认ID';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.payrate
  is '实际支付收益率';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.paymanaerate
  is '实际支付管理费率';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.custbenchmark
  is '客户最终业绩基准';
comment on column RICH_CUSTLOKPROFITNORMAL_X96.gbaflag
  is '境外交易标识0-境内资金交易 1-境外资金交易';
 --还原并行度 
ALTER TABLE RICH_CUSTLOKPROFITNORMAL_X96 PARALLEL 1;

2. 核对数据正确性

在特定的业务场景下确保原表中需要的数据和保留数据表中的数据一致，可以通过多个业务维度去验证

3. OGG同步表停止OGG（oracle gloden gate）

生产上通过OGG进行数据库数据同步，在操作同步表时需要停止OGG

4. 删除原表索引

--删除源表约束索引
DROP INDEX IDX_NORMAL_ISSUEID;
DROP INDEX IDX_NORMAL_STARTDATE;
DROP INDEX IDX_UNION_IND_IIS;
DROP INDEX LOKPROFITNM_INTERESTID;
DROP INDEX RCLN_CUSTACCOUNT_INDEX;
DROP INDEX RICHCLN_FLAG_STATE_INDEX;
DROP INDEX RICHCLPNINDEX;
--删除主键
alter table RICH_CUSTLOKPROFITNORMAL drop constraint PK_RICH_CUSTLOKPROFITNORMAL;
--重命名原表为 HISS表
ALTER TABLE RICH_CUSTLOKPROFITNORMAL RENAME TO RICH_CUSTLOKPROFITNORMAL_HISS;
comment on table RICH_CUSTLOKPROFITNORMAL_HISS
  is '锁定期客户收益表备份表';

5. 数据保留表重命名为原表，创建主键和索引

由于数据量1.2亿左右，在创建索引的时候，开了并行，并且用nologging的方式，极大提高了效率。索引创建完成后需要还原并行度

--数据保留表重命名为原表
ALTER TABLE RICH_CUSTLOKPROFITNORMAL_X96 RENAME TO RICH_CUSTLOKPROFITNORMAL;
--创建约束索引
create index IDX_NORMAL_ISSUEID on RICH_CUSTLOKPROFITNORMAL (ISSUEID) PARALLEL 16 NOLOGGING tablespace XPADINDEX ;
create index IDX_NORMAL_STARTDATE on RICH_CUSTLOKPROFITNORMAL (STARTDATE) PARALLEL 16 NOLOGGING tablespace XPADINDEX;
create index IDX_UNION_IND_IIS on RICH_CUSTLOKPROFITNORMAL (ISSUEID, ISSUEPROFITID, STATE) PARALLEL 16 NOLOGGING tablespace XPADINDEX ;
create index LOKPROFITNM_INTERESTID on RICH_CUSTLOKPROFITNORMAL (CUSTINTERESTID) PARALLEL 16 NOLOGGING tablespace XPADINDEX;
create index RCLN_CUSTACCOUNT_INDEX on RICH_CUSTLOKPROFITNORMAL (CUSTACCOUNT) PARALLEL 16 NOLOGGING tablespace XPADINDEX ;
create index RICHCLN_FLAG_STATE_INDEX on RICH_CUSTLOKPROFITNORMAL (CUSTINTERESTID, ISSUEID, FLAG, STATE, ENDDATE) PARALLEL 16 NOLOGGING tablespace XPADINDEX ;
create unique index RICHCLPNINDEX on RICH_CUSTLOKPROFITNORMAL (ACCOUNT, CUSTACCOUNT, ISSUEID, TYPE, STARTDATE, ENDDATE, CUSTINTERESTID) PARALLEL 16 NOLOGGING tablespace XPADINDEX;
-- Create/Recreate primary, unique and foreign key constraints 
alter table RICH_CUSTLOKPROFITNORMAL add constraint PK_RICH_CUSTLOKPROFITNORMAL primary key (SERIALNO) using index tablespace XPADINDEX;

--还原表及索引的并行度
ALTER TABLE RICH_CUSTLOKPROFITNORMAL PARALLEL 1;

--索引并行度
ALTER INDEX IDX_NORMAL_ISSUEID PARALLEL 1;
ALTER INDEX IDX_NORMAL_STARTDATE PARALLEL 1;
ALTER INDEX IDX_UNION_IND_IIS PARALLEL 1;
ALTER INDEX LOKPROFITNM_INTERESTID PARALLEL 1;
ALTER INDEX RCLN_CUSTACCOUNT_INDEX PARALLEL 1;
ALTER INDEX RICHCLN_FLAG_STATE_INDEX PARALLEL 1;
ALTER INDEX RICHCLPNINDEX PARALLEL 1;
ALTER INDEX PK_RICH_CUSTLOKPROFITNORMAL PARALLEL 1;

6. 同步数据库删除原表索引，重命名原表

--删除源表约束索引
DROP INDEX IDX_NORMAL_ISSUEID;
DROP INDEX IDX_NORMAL_STARTDATE;
DROP INDEX IDX_UNION_IND_IIS;
DROP INDEX LOKPROFITNM_INTERESTID;
DROP INDEX RCLN_CUSTACCOUNT_INDEX;
DROP INDEX RICHCLN_FLAG_STATE_INDEX;
DROP INDEX RICHCLPNINDEX;
alter table RICH_CUSTLOKPROFITNORMAL drop constraint PK_RICH_CUSTLOKPROFITNORMAL;

--重命名原表为 HISS表
ALTER TABLE RICH_CUSTLOKPROFITNORMAL RENAME TO RICH_CUSTLOKPROFITNORMAL_HISS;
comment on table RICH_CUSTLOKPROFITNORMAL_HISS
  is '锁定期客户收益表备份表';
--将RICH_CUSTLOKPROFITNORMAL 从XPAD库复制到RPT库

7. 源数据库端导出dmp，并导入同步端数据库

8. 启动OGG，在源端做update测试一下同步情况

9. 做表分析,源端和OGG同步端都需要做表分析

--21执行表分析RICH_CUSTLOKPROFITNORMAL
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => 'XPAD',
                                TABNAME          => 'RICH_CUSTLOKPROFITNORMAL',
                                CASCADE          => TRUE,
                                DEGREE           => 8,
                                ESTIMATE_PERCENT => 10,
                                NO_INVALIDATE    => FALSE,
                                FORCE            => TRUE
								);
END;
/
文章作者: 海东青
文章链接: http://guodong.fun/2019/07/13/oracle-yi-ci-4-yi-shu-ju-biao-shu-ju-qing-li-qing-li-hou-1.2-yi/
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 海东青 !
oracle
赏
  目录