ORACLE中删除重复记录

ORACLE 中删除重复记录中删除重复记录 平时工作中可能会遇见当试图对库表中的某一列或几列创建唯一索引时 系统提示 ora 01452 不能创建唯一索引 发现重复记录 下面总结一下几种查找和删除重复记录的方法 以表 cz 为例 表 cz 的结构如下 sql desc cz name null type c1 number 10 c10 number 5 c20 varchar2 3 删除重复记录的方法原理 1 在 oracle 中 每一条记录都有一个 rowid rowid 在整个数据库中是唯一的 rowid 确定了 每条记录是在 oracle 中的哪一个数据文件 块 行上 2 在重复的记录中 可能所有列的内容都相同 但 rowid 不会相同 所以只要确定出重复记录 中那些具有最大 rowid 的就能了 其余全部删除 重复记录判断的标准是 c1 c10 和 c20 这三列的值都相同才算是重复记录 经查看表 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 查找重复记录的几种方法 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 删除重复记录的几种方法 1 适用于有大量重复记录的情况 在 c1 c10 和 c20 列上建有索引的时候 用以下语句效率会非 常高 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 适用于有少量重复记录的情况 注意 对于有大量重复记录的情况 用以下语句效率会非常 低 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 建一个临时表 test 用来存放重复的记录 sql truncate table cz 清空 cz 表的数据 但保留 cz 表的结构 sql insert into cz select from test 再将临时表 test 里的内容反插回来 4 适用于有大量重复记录的情况 exception into 子句法 采用 alter table 命令中的 exception into 子句也能确定出库表中重复的记录 这种方法稍微麻 烦一些 为了使用 excepeion into 子句 必须首先创建 exceptions 表 创建该表的 sql 脚本文件为 utlexcpt sql 对于 win2000 系统和 unix 系统 oracle 存放该文件的位置稍有 不同 在 win2000 系统下 该脚本文件存放在 oracle home ora90 rdbms admin 目录下 而对于 unix 系统 该脚本文件存放在 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 从结果里能看到重复记录已删除 3 实例 数据库中有 deny mobile 表 需要按照 mobile 去重复 删除重复记录为 Delete from SMS DENY