Oracle中查找和刪除重復(fù)記錄方法

1、Oracle中查找和刪除重復(fù)記錄方法平時(shí)工作中可能會(huì)遇到當(dāng)試圖對(duì)庫表中的某一列或幾列創(chuàng)建唯一索引時(shí)，系統(tǒng)提示 ORA-01452 :不能創(chuàng)建唯一索引，發(fā)現(xiàn)重復(fù)記錄。下面總結(jié)一下幾種查找和刪除重復(fù)記錄的方法(以表 CZ為例)：表CZ的結(jié)構(gòu)如下：SQL>descczNameNull?TypeC1NUMBER(10)C10NUMBER(5)C20VARCHAR2(3)刪除重復(fù)記錄的方法原理：(1) .在Oracle中，每一條記錄都有一個(gè) rowid , rowid 在整個(gè)數(shù)據(jù)庫中是唯一的，rowid確定了每條記錄是在 Oracle中的哪一個(gè)數(shù)據(jù)文件、塊、行上。(2) .在重復(fù)的記錄中，可能所有

2、列的內(nèi)容都相同，但rowid不會(huì)相同，所以只要確定出重復(fù)記錄中那些具有最大 rowid的就可以了，其余全部刪除。重復(fù)記錄判斷的標(biāo)準(zhǔn)是：C1,C10和C20這三列的值都相同才算是重復(fù)記錄。經(jīng)查看表CZ總共有16條記錄:SQL>setpagesize100SQL>select*fromcz;C1C10C2012dsf12dsf12dsf12dsf23che12dsf12dsf12dsf12dsf23che23che23che23che34dff34dff34dff45err53dar61wee72zxc 20rowsselected.1.查找重復(fù)記錄的幾種方法:(1) .SQL>

3、select*fromczgroupbyc1,c10,c20havi ngcou nt(*)>1;C1C10C2012dsf23che34dff.SQL>selectdisti nct*fromcz;C1C10C2012dsf23che34dff(3) .SQL>select*fromczawhererowid=(selectmax(rowid)fromczwherec仁a.c1a ndc10=a.c10a ndc20=a.c20);C1C10C2012dsf23che34dff2.刪除重復(fù)記錄的幾種方法:(1).適用于有大量重復(fù)記錄的情況(在C1,C10和C20列上建有索引

4、的時(shí)候，用以下語句效率會(huì)很高): SQL>deleteczwhere(c1,c10,c20)i n( selectc1,c10, c20fromczgroupbyc1,c10,c20hav in gco un t(*)>1)a ndrowid no ti n (select min( rowid)fromczgroupbyc1,c10,c20hav in gco un t(*)>1); SQL>deleteczwhererowid notin(select min( rowid)fromczgroupbyc1,c10,c20);（2）.適用于有少量重復(fù)記錄的情況（注意，

5、對(duì)于有大量重復(fù)記錄的情況，用以下語句效率會(huì)很低）：SQL>deletefromczawherea.rowid!=(selectmax(rowid) fromczbwherea.c仁b.c1a nda.c10=b.c10a nda.c20=b.c20);SQL>deletefromczawherea.rowid<(selectmax(rowid)fromczbwherea.c仁b.c1a nda.c10=b.c10a nda.c20=b.c20);SQL>deletefromczawhererowid<(selectmax(rowid)fromczwherec仁a.

6、c1a ndc10=a.c10a ndc20=a.c20);（3）.適用于有少量重復(fù)記錄的情況（臨時(shí)表法）:SQL>createtabletestasselectdisti nct*fromcz;（建一個(gè)臨時(shí)表test用來存放重復(fù)的記錄）SQL>truncatetablecz;（清空cz表的數(shù)據(jù)，但保留 cz表的結(jié)構(gòu)）SQL>i nserti ntoczselect*fromtest;（再將臨時(shí)表test里的內(nèi)容反插回來）（4）.適用于有大量重復(fù)記錄的情況（Exceptioninto子句法）：采用altertable 命令中的Exceptioninto子句也可以確定出庫表中重

7、復(fù)的記錄。這種 創(chuàng)建該表的 SQL腳本文件為 utlexcpt.sql 。對(duì)于 win2000 系統(tǒng)和UNIX 系統(tǒng)，Oracle存放該文件的位置稍有不同，在win2000系統(tǒng)下，該腳本文件存放在 $ORACLE_HOMEOra90rdbmsadmin 目錄下；而對(duì)于 UNIX 系統(tǒng)，該腳本文件存放在$ORACLE_HOME/rdbms/admi n 目錄下。具體步驟如下：12dsf12dsf12dsf12dsf23che12dsf12dsf12dsf12dsf23che23che23che23che34dff34dff34dff16rowsselected.SQL>selectrow_

8、idfromexcepti ons;ROW_IDAAAHD/AAIAAAADSAAAAAAHD/AAIAAAADSAAB AAAHD/AAIAAAADSAAC AAAHD/AAIAAAADSAAF AAAHD/AAIAAAADSAAHAAAHD/AAIAAAADSAAIAAAHD/AAIAAAADSAAGAAAHD/AAIAAAADSAADAAAHD/AAIAAAADSAAEAAAHD/AAIAAAADSAAJAAAHD/AAIAAAADSAAKAAAHD/AAIAAAADSAALAAAHD/AAIAAAADSAAMAAAHD/AAIAAAADSAANAAAHD/AAIAAAADSAAOAAA

9、HD/AAIAAAADSAAP 16rowsselected. SQL>deletefromczwhererowidin (selectrow_idfromexcepti on s); 16rowsdeleted.SQL> insertin toczselectdisti nct*fromdups;3rowscreated.SQL>select*fromcz;C1C10C2012dsf23che34dff45err53dar61wee72zxc7rowsselected.從結(jié)果里可以看到重復(fù)記錄已經(jīng)刪除。SQL>?/rdbms/admi n/utlexcpt.sqlTablecreated.SQL>descexceptio nsNameNull?TypeROW_IDROWIDOWNERVARCHAR2(30)TABLE_NAMEVARCHAR2(30)CONSTRAINTVARCHAR2(30)SQL>altertableczaddc on stra intcz_uniqueuniq ue(c1,c10,c20)exceptio nsin toexcepti ons;*ERRORatl in e1:ORA-02299:ca nn otvalidate(TEST.C