ORACLE中刪除重復(fù)記錄

ORACLE 中刪除重復(fù)記錄中刪除重復(fù)記錄 平時(shí)工作中可能會(huì)遇見(jiàn)當(dāng)試圖對(duì)庫(kù)表中的某一列或幾列創(chuàng)建唯一索引時(shí) 系統(tǒng)提示 ora 01452 不能創(chuàng)建唯一索引 發(fā)現(xiàn)重復(fù)記錄 下面總結(jié)一下幾種查找和刪除重復(fù)記錄的方法 以表 cz 為例 表 cz 的結(jié)構(gòu)如下 sql desc cz name null type c1 number 10 c10 number 5 c20 varchar2 3 刪除重復(fù)記錄的方法原理 1 在 oracle 中 每一條記錄都有一個(gè) rowid rowid 在整個(gè)數(shù)據(jù)庫(kù)中是唯一的 rowid 確定了 每條記錄是在 oracle 中的哪一個(gè)數(shù)據(jù)文件 塊 行上 2 在重復(fù)的記錄中 可能所有列的內(nèi)容都相同 但 rowid 不會(huì)相同 所以只要確定出重復(fù)記錄 中那些具有最大 rowid 的就能了 其余全部刪除 重復(fù)記錄判斷的標(biāo)準(zhǔn)是 c1 c10 和 c20 這三列的值都相同才算是重復(fù)記錄 經(jīng)查看表 cz 總共有 16 條記錄 sql set pagesize 100 sql select from cz c1 c10 c20 1 2 dsf 1 2 dsf 1 2 dsf 1 2 dsf 2 3 che 1 2 dsf 1 2 dsf 1 2 dsf 1 2 dsf 2 3 che 2 3 che 2 3 che 2 3 che 3 4 dff 3 4 dff 3 4 dff 4 5 err 5 3 dar 6 1 wee 7 2 zxc 20 rows selected 1 查找重復(fù)記錄的幾種方法 1 sql select from cz group by c1 c10 c20 having count 1 c1 c10 c20 1 2 dsf 2 3 che 3 4 dff 2 sql select distinct from cz c1 c10 c20 1 2 dsf 2 3 che 3 4 dff 3 sql select from cz a where rowid select max rowid from cz where c1 a c1 and c10 a c10 and c20 a c20 c1 c10 c20 1 2 dsf 2 3 che 3 4 dff 2 刪除重復(fù)記錄的幾種方法 1 適用于有大量重復(fù)記錄的情況 在 c1 c10 和 c20 列上建有索引的時(shí)候 用以下語(yǔ)句效率會(huì)非 常高 sql delete cz where c1 c10 c20 in select c1 c10 c20 from cz group by c1 c10 c20 having count 1 and rowid not in select min rowid from cz group by c1 c10 c20 having count 1 sql delete cz where rowid not in select min rowid from cz group by c1 c10 c20 2 適用于有少量重復(fù)記錄的情況 注意 對(duì)于有大量重復(fù)記錄的情況 用以下語(yǔ)句效率會(huì)非常 低 sql delete from cz a where a rowid select max rowid from cz b where a c1 b c1 and a c10 b c10 and a c20 b c20 sql delete from cz a where a rowiddelete from cz a where rowid create table test as select distinct from cz 建一個(gè)臨時(shí)表 test 用來(lái)存放重復(fù)的記錄 sql truncate table cz 清空 cz 表的數(shù)據(jù) 但保留 cz 表的結(jié)構(gòu) sql insert into cz select from test 再將臨時(shí)表 test 里的內(nèi)容反插回來(lái) 4 適用于有大量重復(fù)記錄的情況 exception into 子句法 采用 alter table 命令中的 exception into 子句也能確定出庫(kù)表中重復(fù)的記錄 這種方法稍微麻 煩一些 為了使用 excepeion into 子句 必須首先創(chuàng)建 exceptions 表 創(chuàng)建該表的 sql 腳本文件為 utlexcpt sql 對(duì)于 win2000 系統(tǒng)和 unix 系統(tǒng) oracle 存放該文件的位置稍有 不同 在 win2000 系統(tǒng)下 該腳本文件存放在 oracle home ora90 rdbms admin 目錄下 而對(duì)于 unix 系統(tǒng) 該腳本文件存放在 oracle home rdbms admin 目錄下 具體步驟如下 sql rdbms admin utlexcpt sql table created sql desc exceptions name null type row id rowid owner varchar2 30 table name varchar2 30 constraint varchar2 30 sql alter table cz add constraint cz unique unique c1 c10 c20 exceptions into exceptions error at line 1 ora 02299 cannot validate test cz unique duplicate keys found sql create table dups as select from cz where rowid in select row id from exceptions table created sql select from dups c1 c10 c20 1 2 dsf 1 2 dsf 1 2 dsf 1 2 dsf 2 3 che 1 2 dsf 1 2 dsf 1 2 dsf 1 2 dsf 2 3 che 2 3 che 2 3 che 2 3 che 3 4 dff 3 4 dff 3 4 dff 16 rows selected sql select row id from exceptions row id aaahd aaiaaaadsaaa aaahd aaiaaaadsaab aaahd aaiaaaadsaac aaahd aaiaaaadsaaf aaahd aaiaaaadsaah aaahd aaiaaaadsaai aaahd aaiaaaadsaag aaahd aaiaaaadsaad aaahd aaiaaaadsaae aaahd aaiaaaadsaaj aaahd aaiaaaadsaak aaahd aaiaaaadsaal aaahd aaiaaaadsaam aaahd aaiaaaadsaan aaahd aaiaaaadsaao aaahd aaiaaaadsaap 16 rows selected sql delete from cz where rowid in select row id from exceptions 16 rows deleted sql insert into cz select distinct from dups 3 rows created sql select from cz c1 c10 c20 1 2 dsf 2 3 che 3 4 dff 4 5 err 5 3 dar 6 1 wee 7 2 zxc 7 rows selected 從結(jié)果里能看到重復(fù)記錄已刪除 3 實(shí)例 數(shù)據(jù)庫(kù)中有 deny mobile 表 需要按照 mobile 去重復(fù) 刪除重復(fù)記錄為 Delete from SMS DENY