sql删除重复记录效率比较

来源：互联网发布：linux 安装ping命令编辑：程序博客网时间：2024/05/01 20:27

SQL> desc test
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
NAME                                               VARCHAR2(20)
CHENFENG                                           DATE
ID                                                 NUMBER(10)

SQL> select name,count(*) from test group by name;

NAME                   COUNT(*)
-------------------- ----------
msp                           1
msptest                       1
tsptest                  100001

name列有100001条记录,其中有100000条数据是重复的,以下是删除重复记录的三种方法,我们可以对比看一下哪个效率最高:

SQL> DELETE FROM test WHERE rowid NOT IN(SELECT MAX(ROWID) FROM test GROUP BY name);

100000 rows deleted.

Elapsed: 00:01:26.67

用了1分26秒,效率最低.

SQL>delete FROM test WHERE ROWID!=(SELECT MAX(ROWID) FROM test D WHERE test.name=D.name AND test.id=D.id);

100000 rows deleted.

Elapsed: 00:00:29.80

用了29秒.

SQL>delete from test where rowid in(select rid from
(select rowid rid,row_number() over(partition by id,name order by id desc) rn from test)
where rn > 1);

100000 rows deleted.

Elapsed: 00:00:22.60

用了22秒,效率最高.

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/cn_chenfeng/archive/2007/08/13/1740502.aspx