Oracle开发专题之:删除重复记录

一、测试环境:

假设目前我们有一个表:test,该表的结构如下:


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

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

 

SQL >   desc  test;
 Name                                      
Null ?    Type
 
-- --------------------------------------- -------- ----------------------------
 ID                                                   NUMBER
 SEQ                                                
NUMBER


现在我们向表中插入200W条数据,这200W条数据中有一半是重复的。

create   or   replace   procedure  gen_duplicated_records  as

  i 
number ;
  j 
number ;

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


我们的最终目的就是剔除这一半的重复记录。下面来看一下各种方法的使用及效率区别

二、使用临时表进行删除:

这个是最简单的思路了,创建一张临时表,将原表中的数据拷贝一半过去,再查询出来。

SQL >   set  timing  on ;
SQL
>  
SQL
>   create   table  test_2  as   select   distinct   *   from  test;

Table  created.

Elapsed: 
00 : 00 : 07.09
SQL
>  


该方法耗时7.09秒,测试数据库位于服务器上。考虑到服务器和本机位于同一个局域网内,该时间如果在真正的生产环境中应该至上延长1倍以上。

三、使用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);
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值