转==oracle－快速删除重复的记录

前写过这种SQL，现在有好久不用了，有点遗忘了，今天导数据的时候数据重复了，
找了篇不错的文章，做个记录：对原文作者表示感谢

原文出处：
http://tech.163.com/06/0621/09/2K4NLFRH00091LRE.html

做项目的时候，一位同事导数据的时候，不小心把一个表中的数据全都搞重了，也就是说，这个表里所有的记录都有一条重复的。这个表的数据是千万级的，而且是生产系统。也就是说，不能把所有的记录都删除，而且必须快速的把重复记录删掉。

对此，总结了一下删除重复记录的方法，以及每种方法的优缺点。

为了陈诉方便，假设表名为Tbl，表中有三列col1，col2，col3，其中col1，col2是主键，并且，col1，col2上加了索引。

1、通过创建临时表
可以把数据先导入到一个临时表中，然后删除原表的数据，再把数据导回原表，SQL语句如下：


Sql代码

   
creat table tbl_tmp (select distinct* from tbl);   
truncate table tbl;//清空表记录   
 insert into tbl select * from tbl_tmp;//将临时表中的数据插回来。  

这种方法可以实现需求，但是很明显，对于一个千万级记录的表，这种方法很慢，在生产系统中，这会给系统带来很大的开销，不可行。


2、利用rowid
在oracle中，每一条记录都有一个rowid，rowid在整个数据库中是唯一的，rowid确定了每条记录是oracle中的哪一个数据文件、块、行上。在重复的记录中，可能所有列的内容都相同，但rowid不会相同。SQL语句如下：


Sql代码

delete from tbl where rowid in (select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2)  
delete from tbl where rowid in (select a.rowid from tbl a, tbl b where a.rowid>b.rowid and a.col1=b.col1 and a.col2 = b.col2)

如果已经知道每条记录只有一条重复的，这个sql语句适用。但是如果每条记录的重复记录有N条，这个N是未知的，就要考虑适用下面这种方法了。

3、利用max或min函数

这里也要使用rowid，与上面不同的是结合max或min函数来实现。SQL语句如下


Sql代码

delete from tbl awhere rowid not in (select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//这里max使用min也可以  
delete from tbl awhere rowid not in (select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//这里max使用min也可以

或者用下面的语句


Sql代码

delete from tbl awhere rowid<(select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//这里如果把max换成min的话，前面的where子句中需要把"<"改为">"  
delete from tbl awhere rowid<(select max(b.rowid) from tbl b where a.col1=b.col1 and a.col2 = b.col2);//这里如果把max换成min的话，前面的where子句中需要把"<"改为">"

跟上面的方法思路基本是一样的，不过使用了group by，减少了显性的比较条件，提高效率。SQL语句如下：


Sql代码

deletefrom tbl where rowid not in (select max(rowid) from tbl tgroup by t.col1, t.col2);   
2.  
3.delete from tbl where (col1, col2) in (select col1,col2 from tblgroup bycol1,col2havingcount(*) >1)and rowidnotin(selectnin(rowid)fromtblgroup bycol1,col2havingcount(*) >1)  
deletefrom tbl where rowid not in (select max(rowid) from tbl tgroup by t.col1, t.col2);

delete from tbl where (col1, col2) in (select col1,col2 from tblgroup bycol1,col2havingcount(*) >1)and rowidnotin(selectnin(rowid)fromtblgroup bycol1,col2havingcount(*) >1) :arrow: 

还有一种方法，对于表中有重复记录的记录比较少的，并且有索引的情况，比较适用。假定col1，col2上有索引，并且tbl表中有重复记录的记录比较少，SQL语句如下4、利用group by，提高效率



根据oracle表的数量，去重可划分为单表去重和多表关联去重。
对于去重，一般最容易想到的是用distinct，而distinct只能对完全重复的记录保留一条。distinct使用的是二重循环来去重的，如果数据量非常大的时候，会导致性能急剧下降。
下面是一个单表去重的测试，认为name字段相同的即为重复记录，要查询出一个结果，过滤掉重复，distinct显然不能满足要求。
表SQL：

create　table　A_TEST
（
id　　　　　number，
name　　　varchar2（20），
remark　varchar2（20）
）;
insert　into　A_TEST　（ID，　NAME，　REMARK）
values　（1，　‘a’，　‘ss’）;
insert　into　A_TEST　（ID，　NAME，　REMARK）
values　（2，　‘b’，　‘xxx’）;
insert　into　A_TEST　（ID，　NAME，　REMARK）
values　（3，　‘b’，　‘x’）;
insert　into　A_TEST　（ID，　NAME，　REMARK）
values　（4，　‘b’，　‘asd’）;
insert　into　A_TEST　（ID，　NAME，　REMARK）
values　（5，　‘c’，　‘axxx’）;
insert　into　A_TEST　（ID，　NAME，　REMARK）
values　（6，　‘c’，　‘asdf’）;
去重方式一（低效）：
select　a.*
from　A_TEST　a，　（select　min（id）　as　id　from　A_TEST　t　group　by　name）　b
where　a.id　=　b.id;
去重方式一（高效）：
select　*
from　（select　a.*，　rownum　row_num　from　A_TEST　a）　x
where　x.row_num　in　（select　min（rownum）　from　A_TEST　t　group　by　name）
可见，使用rownum的效率比使用id的效率要高，当表数据量很大的时候，差距会很明显。