Oracle中删除重复的记录

1 建示例表

SQL> create table tt(name varchar2(10), kecheng varchar2(10), score number);

Table created

2  插入数据

SQL> insert into tt values(1,2,3);

 1 row inserted

SQL> insert into tt values(1,2,3);

 1 row inserted

SQL> insert into tt values(1,2,4);

 1 row inserted

SQL> insert into tt values(1,2,4);

 1 row inserted

SQL> insert into tt values(1,2,4);

 1 row inserted

SQL> insert into tt values(1,2,4);

 1 row inserted

SQL> insert into tt values(1,2,4);

 1 row inserted

SQL> insert into tt values(1,2,4);

 1 row inserted

SQL> insert into tt values(1,2,5);

 1 row inserted

SQL> insert into tt values(1,2,5);

 1 row inserted

SQL> insert into tt select * from tt;

 10 rows inserted

SQL> insert into tt select * from tt;

 20 rows inserted

SQL> insert into tt select * from tt;

 40 rows inserted

SQL> insert into tt select * from tt;

 80 rows inserted

SQL> insert into tt select * from tt;

 160 rows inserted

SQL> insert into tt select * from tt;

 320 rows inserted

SQL> insert into tt select * from tt;

 640 rows inserted

SQL> insert into tt select * from tt;

 1280 rows inserted

SQL> insert into tt select * from tt;

 2560 rows inserted

SQL> insert into tt select * from tt;

 5120 rows inserted

SQL> insert into tt select * from tt;

 10240 rows inserted

SQL> insert into tt select * from tt;

 20480 rows inserted

SQL> insert into tt select * from tt;

 40960 rows inserted

 SQL> select count(*) from tt;

   COUNT(*)

----------

     81920

3 删除方法1

SQL> delete from tt a

  2   where a.rowid != (select max(b.rowid)

  3                       from tt b

  4                      where b.name = a.name

  5                        and b.kecheng = a.kecheng

  6                        and b.score = a.score);

 

81917 rows deleted

Executed in 2.25 seconds

4 删除方法2

SQL> delete from tt

  2   where rowid in (select rid

  3                     from (select rowid as rid,

  4                                  row_number() over(partition by name, kecheng, score order by rowid) as rn

  5                             from tt) a

  6                    where a.rn > 1);

 

81917 rows deleted

 

Executed in 1.875 seconds

81917 rows deleted

5 比较

1. 方法1消耗io较少, cost比较大(14526), 但是更消耗cpu

2. 方法2消耗io较多, 但是cost较小(1007), 消耗cpu也较少