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