6、下次课讲500G大表处理重复数据
500G大表是分区表, 逻辑是只要ID相同就认为是重复数据, 保留一条即可。大家可以思考下。
方案:
大表50G,最快的访问方式就是通过rowid来访问。于是可以创建一张中间表存放大表的rowid和id,那么在这个小表中寻找相同的id 那就能找到rowid,然后根据rowid 访问原数据删除重复的。
中间表的rowid不变,但是字段少了很多(只包含id,rowid),所以通过这个表来找重复数据自然要快很多。
即使使用中间表,至少也有30亿条数据,这么多数据做关联 还是风险很大。所以这个时候我们可以考虑是否可以将这30亿条数据拆分,每次关联一部分,查出重复数据并得到他的rowid在删除,这样就好很多。
这么多数据(30亿)怎么拆分呢?如果能解决拆分问题还可以使用多线程(多个语句同时执行)的方式去删除重复数据。拆分数据最好的方式就是分区,把这30亿条数据均匀分不到各个分区上,每个分区数据量基本一致,然后对每个分区做查重,然后在删除重复数据。
拆出去一张小表 1:1 关联,小表跟大表关联,走NL连接。
第二种切表方法:
我们可以使用ora_hash(id,10)函数来把id 平均分区,分为10个分区,然后小表跟大表做关联。
delete from PUB_E_PQ_AMT where rowid in (
select sourcerowid from (
--- 需要删除的 rowid
select sourcerowid,id, case when sourcerowid= mid then 1 else 0 end delflag
--- 把需要删除的数据,标记成0
from (
select sourcerowid,id, max(sourcerowid) over(partition by id) mid from taba where partitionflag =0
)
) where delflag = 0
500G大表假设叫PQ ,主键为pid
1.新建一个临时表PD,字段 pqrowid,id,pid (pid取值在 ora_hash(id,9)之间),pid做list分区 分成10个小分区表
将PQ表 主键+rowid插入临时表。pid默认为主键最后一个数字。
2.delete from PQ where rowid in (
select pqrowid,id,case when pqrowid=aid then 'Y' else 'N' end hh
from (select pqrowid,id,max(pqrowid) over(partition by id) aid from PD partition(P0) where pid=0) where hh='N'
)
并行执行10次 --
delete from PQ where rowid in (
select pqrowid,id,case when id=aid then 'Y' else 'N' end hh
from (select pqrowid,id,max(id) over(partition by id) aid from PD partition(P9) where pid=9) where hh='N'
)
删完 开并行+nologging,session 级别设置多128块读,建唯一索引,关闭并行并加上主键约束