做項目的時候一位同事導數據的時候不小心把一個表中的數據全都搞重了也就是說這個表裡所有的記錄都有一條重復的這個表的數據是千萬級的而且是生產系統也就是說不能把所有的記錄都刪除而且必須快速的把重復記錄刪掉對此總結了一下刪除重復記錄的方法以及每種方法的優缺點
為了陳訴方便假設表名為Tbl表中有三列colcolcol其中colcol是主鍵並且colcol上加了索引
通過創建臨時表
可以把數據先導入到一個臨時表中然後刪除原表的數據再把數據導回原表SQL語句如下
creat table tbl_tmp (select distinct* from tbl);
truncate table tbl;//清空表記錄
insert into tbl select * from tbl_tmp;//將臨時表中的數據插回來
這種方法可以實現需求但是很明顯對於一個千萬級記錄的表這種方法很慢在生產系統中這會給系統帶來很大的開銷不可行
利用rowid
在oracle中每一條記錄都有一個rowidrowid在整個數據庫中是唯一的rowid確定了每條記錄是oracle中的哪一個數據文件塊行上在重復的記錄中可能所有列的內容都相同但rowid不會相同SQL語句如下
delete from tbl where rowid in (
select arowid from tbl a tbl b where arowid>browid and l=l and l = l)
如果已經知道每條記錄只有一條重復的這個sql語句適用但是如果每條記錄的重復記錄有N條這個N是未知的就要考慮適用下面這種方法了
利用max或min函數
這裡也要使用rowid與上面不同的是結合max或min函數來實現SQL語句如下
delete from tbl awhere rowid not in (
select max(browid)
from tbl b where l=l and l = l);//這裡max使用min也可以
或者用下面的語句
delete from tbl awhere rowid<(select max(browid)
from tbl b where l=l and l = l);
//這裡如果把max換成min的話前面的where子句中需要把<改為>
跟上面的方法思路基本是一樣的不過使用了group by減少了顯性的比較條件提高效率SQL語句如下
deletefrom tbl where rowid not in (select max(rowid)
from tbl tgroup by l l);
delete from tbl where (col col) in (select colcol from tblgroup bycolcolhavingcount(*) >)
and rowidnotin(selectnin(rowid)fromtblgroup bycolcolhavingcount(*) >)
還有一種方法對於表中有重復記錄的記錄比較少的並且有索引的情況比較適用假定colcol上有索引並且tbl表中有重復記錄的記錄比較少SQL語句如下利用group by提高效率
Group By方法（我經常所用的一種方法）
一查數據
Select count(Num) max(Name) from student 列出重復記錄並列出屬性
Group By Name
Having count(Num) > 按Name分組後找出表中Num列重復即出現次數大於
二刪數據
Delete from student Group By Name Having count(Num) >
From:http://tw.wingwit.com/Article/program/Oracle/201311/17877.html
