104oracle大表删除重复记录的几种方法(转)

表上某个字段(或某几个字段)有重复值,有需求要把重复记录删除,只保留一条.

如果是小表,随便怎么折腾都行; 如果是大表(至少1千万条记录以上,或者占用10G以上空间), 我们可能需要想办法加快这个速度 , 这时可以参考下面方法:

要求:

  删除t1表 object_name字段上的重复记录,只保留其对应created字段最大的那一条.

先查表的总记录数和需要删除的重复记录数,dup_cnt就是需要删除的重复记录数:

--如果是多个字段去重,一起写到 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.如果需要删除的重复记录比较少,比如几万条以下,可以用下面方法:

--如果是多个字段去重, 一起写到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.如果需要删除的重复记录比较多,比如几十万以上,可以用下面方法:

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保存到临时分区表, 然后逐个批次执行:

--创建临时表分区表保存待删除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.如果需要删除的重复记录非常多,可以考虑创建新表+改名的方法:

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的那一条记录,只要不重即可),可以用下面简单写法(大表再把并行加上):

--并行相关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 可以用主键字段代替, 思路差不多是相通的, 只不过语法有些差异,可以借鉴.

如有不妥之处,请指正,多谢!