高效快速刪除Oracle表中重複記錄

 

delete from 表 a
 where (a.Id, a.seq) in
       (select Id, seq from 表 group by Id, seq having count(*) > 1)
   and rowid not in
       (select min(rowid) from 表 group by Id, seq having count(*) > 1);

 

沒有在(id,seq)上建立聯合index的時候，將會對錶進行三次全表掃描，如果表很大的話，並不能高效刪除重複記錄．

 

如果改造為下面的SQL

 

delete 表
 where rowid in (with t_save as (select Id, seq, min(rowid) min_rowid
                    from 表
                   group by Id, seq
                  having count(*) > 1)
                  select rowid
                    from 表 a
                   where exists (select 1
                            from t_save b
                           where b.id = a.id
                             and b.seq = a.seq
                             and b.min_rowid <> a.rowid)
                 )
將對錶只有兩次全表掃描．

 

如果重複的記錄比較少，我們可以增加如下的hint:

 

delete 表
 where rowid in (with t_save as (select Id, seq, min(rowid) min_rowid
                    from 表
                   group by Id, seq
                  having count(*) > 1)
                  select /*+ leading(b) use_nl(b a) */ rowid
                    from 表 a
                   where exists (select 1
                            from t_save b
                           where b.id = a.id
                             and b.seq = a.seq
                             and b.min_rowid <> a.rowid)
                 )