1.不含大字段(clob等)的表格:
--例子表格:create table test(a number,b number);
--方法一:通过group by + rowid,效率低
delete from test t
where t.rowid not in (select min(rowid) from test group by a, b);
--方法二:通过 create + rename + distinct ,效率高
create table test_tmp as
select distinct * from test t;
drop table test;
alter table test_tmp rename to test;
2.含大字段(clob等)的表格:
--例子表格:create table test(a number,b clob);
--clob 长度小于4000:
select distinct t.a,to_char(t.b) as b from test t;
--clob 长度大于4000:
select *
from test a
where a.rowid = (select max(b.rowid)
from test b
where b.a = a.a
and nvl(dbms_lob.compare(b.b, a.b), 0) = 0);