处理500G大表重复数据

作者简介:戴秋龙,拥有超过八年的电信、保险、税务行业核心系统ORACLE数据库优化,优化经验,具备丰富的行业服务背景。对Oracle数据库有深刻的理解,擅长数据库故障诊断,数据库性能调优。

背景

晚间某工程师反应需要DBA协助,系统中某个表很大有上亿条数据需要在表上面建主键,而且任务紧急。

据工程师反馈建立主键时候报错,有重复数据导致失败。目前瓶颈在处理重复数据上面。

分析

总体思路


登陆系统,查询出该表500G+。 Temp,IO 等各项资源都很空闲。而且据业务反应该表建其他普通索引时候,也就20分钟左右。 说明工程师反应的实际情况靠谱。 看了下处理逻辑。 SQL:

按照SQL语义可以理解为只要id 相等就可以认为数据重复, 需要删除,只保留一条。

当时看到这个语句就知道肯定不可行, 试想500G关联500G,TEMP 空间无法满足如此大的表关联。

根据业务逻辑, 我采取的原理是新建表,新表中有原表的rowid,id. 这个表只有2个字段,虽然表记录数没有变,但是表的字段少了很多,所以表的体积可以减少很多。 在新表中根据id找到需要删除的数据,那么就知道原表中需要删除数据的rowid, 根据rowid 删除数据。总的思路就是这样,但还是要进一步细化, 因为毕竟有上亿条记录,关联起来还是要费时费力。从事后看细化这一步是最为关键的。

进一步细化

细化这一步在新建的小表上面, 添加一个字段,作为list分区用, 新表 sourcerowid, id, partitionflag。 partitionflag取值ora_hash(id,9),也就是限制在0-9 之内。

意义在于把数据平均分成10份,0结尾放在第一个分区, 1结尾放在第二个分区, 2结尾放在第三个分区…那我找重复数据,肯定是0结尾的和0结尾的比较,1结尾的和1结尾的比较。这样又可以把数据量缩小10倍。


抽取数据到新表中。这个大表一级分区 90个,二级分区5500个,我目前根据一级分区做数据抽取。分9批同时抽取。抽取花了20分钟左右。
这个新表近20G
之前就意识到做细化,从这个表的体积看,是做对了。

最终处理

  1. 小表10个分区,开10个窗口同时执行。

    每个SQL删除2000+条数据,100+秒执行成功。

    处理掉重复数据,接着开64个并行,nologging模式,session 级别设置多块读每次读128个数据块,建唯一索引。160多秒就执行成功了,然后设置并行度为1, 并加上主键约束。

    任务虽然紧急但是从准备工作,到完工花了40分钟不到, 按时完成任务。


未经允许不得转载:Oracle一体机用户组 » 处理500G大表重复数据

相关推荐