历史库有张分区表有二十亿数据,有300w数据是重复的,开发写的删除脚本效率比较低。用分析函数改写删除脚本,按分区来删除。
select *
from TAB
where rowid in (select rid
from (select rowid as rid,
RCVBL_AMT_ID,
row_number() over(partition by RCVBL_AMT_ID order by rowid desc) as seq
from TAB t
where t.ORG_NO LIKE '32405' || '%'
AND t.RCVBL_YM = '201403')
where seq > 1);