表上某个字段(或某几个字段)有重复值,有需求要把重复记录删除,只保留一条.
如果是小表,随便怎么折腾都行; 如果是大表(至少1千万条记录以上,或者占用10G以上空间), 我们可能需要想办法加快这个速度 , 这时可以参考下面方法:
要求:
删除t1表 object_name字段上的重复记录,只保留其对应created字段最大的那一条.
先查表的总记录数和需要删除的重复记录数,dup_cnt就是需要删除的重复记录数:
代码语言：javascript复制--如果是多个字段去重,一起写到 group by 后面
select /* parallel(8) */
sum(cnt) as total_cnt
,sum(cnt-1) as dup_cnt
from (select count(*) cnt from t1 group by object_name )
;1.如果需要删除的重复记录比较少,比如几万条以下,可以用下面方法:
代码语言：javascript复制--如果是多个字段去重, 一起写到partition by 后面
delete
/* parallel(8)
opt_param('_hash_join_enabled' 'false')
opt_param('_optimizer_sortmerge_join_enabled' 'false')
*/
from t1
where rowid in
(select rowid
from
(select rowid,row_number() over
(partition by object_name
order by created desc nulls last) as rn
from t1)
where rn>1
);
加hint的目的是并行扫描大表,然后用Nested Loops(禁用了hash join和merge join,只能选择Nested Loops), 按rowid删除重复记录, 大表全表扫描只做一次. 不会锁表.
2.如果需要删除的重复记录比较多,比如几十万以上,可以用下面方法:
代码语言：javascript复制delete /* enable_parallel_dml parallel(8) */
from t1
where rowid in
(select rowid from
(select rowid,row_number()
over(partition by object_name order by created desc nulls last) rn
from t1
) where rn>1
) ;
其中enable_parallel_dml 这个hint的作用是启用并行dml, 从12c开始支持. 如果没有这个hint,只是在表扫描时使用并行,delete不并行.
用这个hint会锁表, 直到commit或rollback才会释放锁. 如果不想锁表,可以去掉enable_parallel_dml hint.
这个方法对删除少量重复记录也是可用的.
注意:
如果表上索引比较多, 消耗时间会更长,如果业务允许,可以先禁用索引:
alter index xxx unusable;
删除操作完成后再重建索引:
alter index xxx rebuild online parallel;
注意:
需要删除的记录越多, 生成的redo和undo量就越大, 这种大事务的操作要慎重. 下面的方法3会把大事务拆分.
3.可以把大事务拆分, 比如拆分成10次:
把要删除记录的rowid保存到临时分区表, 然后逐个批次执行:
代码语言：javascript复制--创建临时表分区表保存待删除rowid及对应的批次:
--ntile(10) 分析函数负责把记录拆分等10份, 相邻rowid分在一组
--建分区表是为了避免临时表的多次全表扫描
CREATE TABLE tmp_t1_rid parallel 8
PARTITION BY RANGE (batch_id) interval (1)
(
PARTITION p1 VALUES LESS THAN (2)
)as
select ntile(10) over (order by rowid) as batch_id
, rowid as rid
from (select rowid,row_number() over
(partition by object_name order by created desc nulls last) rn
from t1
)
where rn>1;
--指定不同的batch_id=1..10, 分别执行, 共10次:
--batch_id=1
delete from t1 where rowid in
(select rid from tmp_t1_rid where batch_id=1);
commit;
...batch_id=2~9
commit;
--batch_id=10
delete from t1 where rowid in
(select rid from tmp_t1_rid where batch_id=10);
commit;
4.如果需要删除的重复记录非常多,可以考虑创建新表 改名的方法:
代码语言：javascript复制create table t_nodup parallel 8
as
select * from --这个*要替换成字段列表,否则新表会多一个rn字段
(select a.*,row_number() over
(partition by object_name order by created desc nulls last) as rn
from t1 a
)
where rn=1;
--然后改表名:
rename t1 to t1_bak;
rename t_nodup to t1;
最后还要把原表上的约束,索引,grant等相关信息应用在新表上.5. 如果对保留记录没有要求(不要求保留对应最大created的那一条记录,只要不重即可),可以用下面简单写法(大表再把并行加上):
代码语言：javascript复制--并行相关hint可以酌情使用:
delete from t1 where rowid not in
(select max(rowid) from t1 group by object_name);最后的建议:
为了避免生成新的重复记录, 建议在相关字段增加unique约束:
create unique index uidx_t1_object_name on t1(object_name) ;
补充:
rowid是oracle数据库独有的, mysql 和 postgresql 可以用主键字段代替, 思路差不多是相通的, 只不过语法有些差异,可以借鉴.
如有不妥之处,请指正,多谢!
(完)
