ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 高效快速删除Oracle表中重复记录

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

Linux操作系统 作者:yilan900607 时间:2014-02-14 15:47:03 0 删除 编辑
如何高效删除表中重复记录,仅保留一条?
 
网上比较多的方法是如下的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/29455589/viewspace-1081497/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2014-01-22

  • 博文量
    11
  • 访问量
    32659