几种删除重复记录的方法

对于下面一张表:

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秒,效率最高.