刪除重複數據的一種高效的方法

http://blog.oracle.com.cn/index.php/243090/viewspace-4490.html

表demo是重複拷貝自dba_objects，有88萬左右，不重複的是27323，沒有索引

CNOUG博客首頁rQ Dq6o]l
方法一：delete from demo a where a.rowid <> (select max(rowid) from demo b where CNOUG博客首頁#|V0LDl"q
b.object_id=a.object_id); CNOUG博客首頁.a8`6F0t tM
耗時：幾個小時以上

CNOUG博客首頁F$Ms d%]
方法二： delete from demo where rowid in CNOUG博客首頁 r#s;cZv
]
(select rid from
@cg%^2Rp(d] Ph0(select rowid rid,row_number() over(partition by object_id order by rowid) rn CNOUG博客首頁;d�p)P"Z GM
from demo)
?ja.H'M w*K$[aAUW0where rn <> 1 ); CNOUG博客首頁kWe2at&D8c)R
耗時：30秒

方法三： create table demo2 as
z3Q+]T*l pB0@�u0select object_id,owner... from
7d/ULq0X7k;i0(select demo.*,row_number() over(partition by object_id order by rowid) rn from demo) CNOUG博客首頁#V%VgYq[
where rn = 1; CNOUG博客首頁1s2u5j&t%`]
truncate table demo; insert into demo select * from demo2; drop table demo2;
2X^fI9J�J#aiLq0共耗時： 10秒，適合大數據量的情況，產生更少回滾量；