测试环境
DROP TABLE t purge ;
CREATE TABLE t AS SELECT * FROM dba_objects WHERE rownum<=10;
UPDATE t SET object_id=rownum;
UPDATE t SET object_id=3 WHERE object_id<=3;
UPDATE t SET object_id=4 WHERE object_id>=4 AND object_id<=6;
COMMIT;
以下例子来看看分析函数和非分析函数写法的性能差异
开启set autotrace on
SET autotrace ON
SET linesize 2000
SET pagesize 2000
保留rowid最大一条数据
SQL> delete from t
where rowid <
(select max(rowid) from t t2
where t.object_id = t2.object_id
);
执行计划：
SQL> rollback;
Rollback complete.
方法二：（使用分析函数）下面是保留最新的记录，写法不完全等价，但效果也是符合要求，其中row_number必须要有order BY 关键字
SQL> delete from t
where rowid in (select rid
from (select rowid rid,
row_number() over(partition by object_id ORDER by created desc) rn
from t)
where rn > 1);
执行计划：
虽然目前来看,第一种方法效率也还可以，不过如果是多个字段重复，第一种方法就有些麻烦了,而且如果需求真的是要根据日期保留重复记录中最新的一个，也非常的繁琐了。
不过这类需求要记住，要是表记录非常大，比如有几千万，而重复记录的数据又特别巨大，这时候可以考虑直接把不重复的数据单独建出来，也是一个灵活的好办法。
分解过程：
SQL> set autotrace off
SQL> SELECT object_id,rowid rid,
row_number() over(partition by object_id ORDER by created desc) rn
from t;
OBJECT_ID RID RN
---------- ------------------ ----------
3 AAASJZAABAAAXqRAAF 1
15 AAASJZAABAAAXqRAAD 2
20 AAASJZAABAAAXqRAAA 3
25 AAASJZAABAAAXqRAAG 1
28 AAASJZAABAAAXqRAAC 2
29 AAASJZAABAAAXqRAAE 3
40 AAASJZAABAAAXqRAAJ 1
41 AAASJZAABAAAXqRAAH 1
46 AAASJZAABAAAXqRAAB 1
54 AAASJZAABAAAXqRAAI 1
10 rows selected.
SQL> SELECT object_id, rid,rn
FROM (select object_id,rowid rid,
row_number() over(partition by object_id ORDER by created desc) rn
from t)
where rn > 1;
OBJECT_ID RID RN
---------- ------------------ ----------
3 AAAbW0AALAABRADAAB 2
3 AAAbW0AALAABRADAAC 3
4 AAAbW0AALAABRADAAE 2
4 AAAbW0AALAABRADAAF 3
然后就可以根据这些RID,删除了，就留下一条了。
SQL> delete from t
where rowid in (select rid
from (select rowid rid,
row_number() over(partition by object_id ORDER by created desc) rn
from t)
where rn > 1);
