源地址：https://zhidao.baidu.com/question/66722841.html
1.不含大字段(clob等)的表格:
|
1
2
3
4
5
6
7
8
9
|
--例子表格: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等)的表格:
|
1
2
3
4
5
6
7
8
9
10
|
--例子表格: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);
|