【Oracle SQL】两百万数据的表中名称存在重复，直接删除方案和借助临时表方案比较（删除比例约三成，前者比后者快）

【实验环境】

Oracle11g

【实验对象表及数据】

create table test05(
    id number(10),
    name nvarchar2(10),
    primary key(id)
)

充值：

insert into test05
select
       rownum,
       dbms_random.string('*',dbms_random.value(1,10))
from dual
connect by level<2000001;

充值过程约耗时18秒。

【需求】

如果名称字段存在重复，则删除重复的记录

【备份数据】

因为要实验至少两次，因此做一个临时表test06存储test05的试验记录。

create table test06 as select * from test05;

【第一方案：直接删除】

以下sql能知道哪些名称存在重复

select name from test05 group by name having count(id)>1;

以下SQL能知道多少个名称存在重复

select count(*) from (select name from test05 group by name having count(id)>1);

执行完发现有59605个name有重复值。

直接开始删除

delete from test05 a where exists (select null from test05 b where b.name=a.name and b.id<a.id)

用时约16秒，删除585105行

再用select count(*) from (select name from test05 group by name having count(id)>1);去查，发现结果为0，此说明重复名称的记录已然删除了。

 

【倒数据，以方便第二次实验】

truncate table test05;
insert into test05 select * from test06;

以上两句用时约16秒

【第二方案：借助临时表test07】

看重复情况：

select count(*) from (select name from test05 group by name having count(id)>1);

还是发现59605个name有重复值。这里确认了实验条件一致。

 

把不存在重复的记录存入临时表

create table test07 as select * from test05 a where not exists (select null from test05 b where b.name=a.name and b.id<a.id)

此句耗时约两秒

再查查test07里的记录数

SQL> select count(*) from test07;

  COUNT(*)
----------
   1414895

已用时间:  00: 00: 00.08

这里已经和方案一最终结果一致了，其正确性可以相互验证。

再把test5的数据清空，将test7的数据倒回去

truncate table test05;
insert into test05 select * from test07;

用时约28秒

 

由以上实验得知，如果重复数据比例低，那么直接删除要快于临时表方案。

END