常见问题--oracle删除重复记录

问题:在生产环境中，经常碰到一些表中有重复记录的情况，导致数据不准确，影响用户使用,如何处理呢:

测试:

1.创建一张空表,记录重复数据

10:38:01 SQL> desc dbtest
Name Type   Nullable Default Comments
---- ------ -------- ------- --------
ID   NUMBER Y                        
SEQ  NUMBER Y    

2.生成重复数据

create or replace procedure dbtest_duplicated_records as

i number;
j number;

begin

  for i in 1 .. 2 loop
    for j in 1 .. 1000000 loop
      insert into dbtest values (j,j+10);
    end loop;
    commit;
  end loop;
  commit;
 
end;

3.删除重复数据

方法（1）：通过临时表

 

create table dbtest1 nologging as select distinct * from dbtest ;

dbtest1中就是没有重复数据的，数据为原来的1/2，共1000000条

方法（2）：使用rowid进行删除

我们知道在Oracle中，rowid是用来唯一表示一条记录的伪列，任意两条记录的rowid都是不同的，即便内容看起来一模一样。所以我们的思路是：使用表的自连接，查找那些内容相同但rowid不同的记录，即为重复记录。然后随意选择其中一个rowid代表的记录，删除另一条记录。

我们来看一下其中id=1的记录在自连接后的情况：

SQL >   select  a. * , a.rowid, b. * , b.rowid  from  test a, test b  where  a.id  =  b.id  and  a.seq  =  b.seq  and  a
.id  =   1 ;

        ID        SEQ ROWID                      ID        SEQ ROWID
-- -------- ---------- ------------------ ---------- ---------- ------------------
          1           11  AAAGHIAAJAAAAAKAAA           1           11  AAAGHIAAJAAAAAKAAA
          1           11  AAAGHIAAJAAAAgQAGX           1           11  AAAGHIAAJAAAAAKAAA
          1           11  AAAGHIAAJAAAAAKAAA           1           11  AAAGHIAAJAAAAgQAGX
          1           11  AAAGHIAAJAAAAgQAGX           1           11  AAAGHIAAJAAAAgQAGX

Elapsed:  00 : 00 : 02.08
SQL >  

我们看到自连接后的4条记录中有2条的rowid是不同的，说明这2条记录就是重复记录，所以我们可以通过选择其中rowid较大或较小的记录，来删除剩余的记录。但是这种方法的一个很大的缺点就是由于采用了“自连接”，对于像我这样的测试表中有200W条记录的情况，其自连接后的记录数是一个天文数字(其实本人的测试就因为等待过久而不得不取消)。

我们换另外一种方法：

DELETE   FROM  test t1 
  WHERE  t1.ROWID  NOT   IN  (
      SELECT   MAX (t2.rowid) 
        FROM  test t2 
       WHERE  t1.id  =  t2.id  AND  t1.seq  =  t2.seq);

实践证明，这种方法对大量数据的情况，效率依然是很低的。结果如同上一种方法。假如我们再结合group by呢？

SQL >   DELETE   FROM  test
   2     WHERE  ROWID  NOT   IN  ( SELECT   MAX (ROWID)  FROM  test  GROUP   BY  id, seq);

效果如同前面两个方法一样，大量的连接、排序、分组让依靠rowid来删除重复记录变得很耗时，反而是采用方法1的情况下速度很快(本人测试了2次，都是连接测试服务器进行测试，第一次用时7.09秒，第二次用时14.656秒)。

小结：
在数据量不大的情况下，采用根据rowid或结合group by分组的方式是很快的，但是在海量数据的情况下则反而是方式一最快，因为省去了自连接、排序、分组的时间

 

参考:http://www.blogjava.net/pengpenglin/archive/2008/06/18/208805.html

 

 

 

 

 

重复数据.jpg

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

转载于:http://blog.itpub.net/15720542/viewspace-608588/