Oracle中删除重复的记录

csqtidfefo

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也较少