高效快速删除Oracle表中重复记录

如何高效删除表中重复记录，仅保留一条？

 

网上比较多的方法是如下的sql:

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)
                 )

 

并在 (id,seq)上建立联合index，速度将会更快．

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/195110/viewspace-753052/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/195110/viewspace-753052/