oracle中删除一天记录,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

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

3from tt b

4where b.name = a.name

5and b.kecheng = a.kecheng

6and b.score = a.score);

81917 rows deleted

Executed in 2.25 seconds

4删除方法2

SQL> delete from tt

2where rowid in (select rid

3from (select rowid as rid,

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

5from tt) a

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