Oracle中用Rowid查找和删除重复记录.doc

Oracle中用Rowid查找和删除重复记录平时工作中可能会遇到当试图对库表中的某一列或几列创建唯一索引时，系统提示 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条记录：SQLset pagesize 100SQLselect * 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).SQLselect * from cz group by c1,c10,c20 having count(*) 1; C1 C10 C20 - - - 1 2 dsf 2 3 che 3 4 dff(2).SQLselect distinct * from cz; C1 C10 C20 - - - 1 2 dsf 2 3 che 3 4 dff(3).SQLselect * 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 dsf2 3 che3 4 dff2.删除重复记录的几种方法：(1).适用于有大量重复记录的情况(在C1,C10和C20列上建有索引的时候，用以下语句效率会很高)：SQLdelete 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);SQLdelete cz where rowid not in (select min(rowid) from cz group by c1,c10,c20);(2).适用于有少量重复记录的情况(注意，对于有大量重复记录的情况，用以下语句效率会很低)：SQLdelete 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);SQLdelete from cz a where a.rowiddelete from cz a where rowid create table test as select distinct * from cz; (建一个临时表test用来存放重复的记录）SQLtruncate table cz; (清空cz表的数据，但保留cz表的结构）SQLinsert into cz select * from test; (再将临时表test里的内容反插回来）(4).适用于有大量重复记录的情况(Exception into 子句法)：采用alter table 命令中的 Exception into 子句也可以确定出库表中重复的记录。这种方法稍微麻烦一些，为了使用“excepeion into ”子句，必须首先创建 EXCEPTIONS 表。创建该表的 SQL 脚本文件为 utlexcpt.sql 。对于win2000系统和 UNIX 系统， Oracle 存放该文件的位置稍有不同，在win2000系统下，该脚本文件存放在$ORACLE_HOMEOra90rdbmsadmin 目录下；而对于 UNIX 系统，该脚本文件存放在$ORACLE_HOME/rdbms/admin 目录下体步骤如下：SQL?/rdbms/admin/utlexcpt.sql Table created. SQLdesc exceptions Name Null? Type - ROW_ID ROWID OWNER VARCHAR2(30) TABLE_NAME VARCHAR2(30) CONSTRAINT VARCHAR2(30) SQLalter 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 SQLcreate table dups as select * from cz where rowid in (select row_id from exceptions); Table created. SQLselect * 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. SQLselect 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. SQLdelete from cz where rowid in ( select row_id from exceptions); 16 rows deleted. SQLinsert into cz select distinct * from dups; 3 rows created. SQLselect *from cz; C1 C10 C20 - - -1 2 dsf2 3 che3 4 dff4 5 err5 3 dar6 1 wee7 2 zxc7 rows selected.从结果里可以看到重复记录已经删除。Oracle查询删除表中重复记录最高效删除重复记录的方法，使用为例rowidDELETE FROM EMP E WHERE E.ROWID (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有 rowid最小的记录delete from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )1)注:rowid为oracle自带不用该.3、查找表中多余的重复记录（多个字段）select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)1)5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录select * from vitae awhere (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)1)(二)比方说在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；Select Name,Count(*) From A Group By Name Having Count(*) 1如果还查性别也相同大则如下:Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) 1查询及删除重复记录的SQL语句1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) 1)2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录delete from peoplewhere peopleId in (select peopleId from people group by peopleId having count(peopleId) 1) and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )1)注:rowid为oracle自带不用该.3、查找表中多余的重复记录（多个字段）select * from vitae a where (a.peopleId,a.seq) in(select peopleId,seq from vitae group by peopleId,seq having count(*) 1)4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录delete from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1)and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)1)5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录select * from vitae a where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) 1) and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)1)(二)比方说 在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；Select Name,Count(*) From A Group By Name Having Count(*) 1如果还查性别也相同大则如下:Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) 1declare max integer,id integerdeclare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) 1open cur_rowsfetch cur_rows into id,maxwhile fetch_status=0beginselect max = max -1set rowcount maxdelete from 表名 where 主字段 = idfetch cur_rows into id,maxendclose cur_rowsset rowcount 0 方法二重复记录有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。1、对于第一种重复，比较容易解决，使用select distinct * from tableName就可以得到无重复记录的结果集。如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除select distinct * into #Tmp from tableNamedrop table tableNameselect * into tableName from #Tmpdrop table #Tmp发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集select identity(int,1,1) as autoID, * into #Tmp from tableNameselect min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoIDselect * from #Tmp where autoID in(select autoID from #tmp2)最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）select * from tablename where id in (select id from tablenamegroup by idhaving count(id) 1) Oracle数据库中表的四种连接方式讲解表的连接是指在一个SQL语句中通过表与表之间的关连,从一个或多个表中检索相关的数据,大体上表与表之间的连接主要可分四种,分别为相等连接,外连接,不等连接和自连接,本文将主要从以下几个典型的例子来分析Oracle表的四种不同连接方式:1. 相等连接通过两个表具有相同意义的列，可以建立相等连接条件。只有连接列上在两个表中都出现且值相等的行才会出现在查询结果中。例 查询员工信息以及对应的员工所在的部门信息;SELECT*FROMEMP,DEPT; SELECT*FROMEMP,DEPT WHEREEMP.DEPTNO=DEPT.DEPTNO;REM 显示工资超过2000的员工信息以及对应的员工的部门名称。2. 外连接对于外连接，Oracle中可以使用“(+)”来表示，9i可以使用LEFT/RIGHT/FULL OUTER JOIN，下面将配合实例一一介绍。除了显示匹配相等连接条件的信息之外,还显示无法匹配相等连接条件的某个表的信息。外连接采用(+)来识别。A) 左条件(+) = 右条件;代表除了显示匹配相等连接条件的信息之外,还显示右条件所在的表中无法匹配相等连接条件的信息。此时也称为右外连接.另一种表示方法是:SELECT . FROM 表1 RIGHT OUTER JOIN 表2 ON 连接条件B) 左条件 = 右条件(+);代表除了显示匹配相等连接条件的信息之外,还显示左条件所在的表中无法匹配相等连接条件的信息。此时也称为左外连接.SELECT . FROM 表1 LEFT OUTER JOIN 表2 ON 连接条件例 显示员工信息以及所对应的部门信息-无法显示没有部门的员工信息-无法显示没有员工的部门信息-SELECT*FROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNO;-直接做相等连接:SELECT*FROMEMPJOINDEPTONEMP.DEPTNO=DEPT.DEPTNO;REM 显示员工信息以及所对应的部门信息,显示没有员工的部门信息-SELECT*FROMEMP,DEPTWHEREEMP.DEPTNO(+)=DEPT.DEPTNO; SELECT*FROMEMPRIGHTOUTERJOINDEPTONEMP.DEPTNO=DEPT.DEPTNO;REM 显示员工信息以及所对应的部门信息,显示没有部门的员工信息-SELECT*FROMEMP,DEPTWHEREEMP.DEPTNO=DEPT.DEPTNO(+); SELECT*FROMEMPLEFTOUTERJOINDEPTONEMP.DEPTNO=DEPT.DEPTNO;3. 不等连接两个表中的相关的两列进行不等连接,比较符号一般为,.,BETWEEN. AND.REMSALGRADE -DESCSALGRADE; -SELECT*FROMSALGRADE;REM 显示员工的编号,姓名,工资,以及工资所对应的级别。SELECTEMPNO,ENAME,SAL,SALGRADE.*FROMSALGRADE,EMP WHEREEMP.SALBETWEENLOSALANDHISAL;REM 显示雇员的编号,姓名,工资,工资级别,所在部门的名称;SELECTEMPNO,ENAME,SAL,GRADE,DNAMEFROMEMP,DEPT,SALGRADE WHEREEMP.DEPTNO=DEPT.DEPTNOANDEMP.SALBETWEENLOSALANDHISAL;4. 自连接自连接是数据库中经常要用的连接方式，使用自连接可以将自身表的一个镜像当作另一个表来对待，从而能够得到一些特殊的数据。下面介绍一下自连接的方法:将原表复制一份作为另一个表,两表做笛卡儿相等连接。例 显示雇员的编号,名称,以及该雇员的经理名称SELECT WORKER.ENAME,WORKER.MGR,MANAGER.EMPNO,MANAGER.ENAME FROM EMP WORKER,EMP MANAGERWHERE WORKER.MGR = MANAGER.EMPNO;Oracle导入和导出工具使用Oracle 提供两个工具imp.exe 和exp.exe分别用于导入和导出数据。这两个工具位于Oracle_home/bin目录下。导出数据exp1 将数据库ATSTestDB完全导出，用户名system 密码123456 导出到c：export.dmp中exp system/123456ATSTestDB file=c：export.dmp full=y其中ATSTestDB为数据库名称，system为该数据库里的账户，123456为其密码。2 将数据库中system用户与sys用户的所有相关资源导出（表，存储过程，方法，视图等等）exp system/123456ATSTestDB file= c：export.dmp owner=（system，sys）3 将数据库中的表sys.table1、owbsys.table2导出exp system/123456ATSTestDB file= c：export.dmp tables=（ sys.table1， owbsys.table2）注意，需要加上表的schema名称，如果没有加的话默认是导当前connected用户的表，当然你连接上去的账户要对相应的表有权限。4 将数据库中的表table1中的字段Title以GangGe打头的数据导出exp system/123456ATSTestDB file= c：export.dmp tables=（table1） query= where Title like GangGe%斜杠 后面跟冒号是为了转义字符冒号 用的，因为后面是一条条件查询语句。Query参数只能指定一个，如果Query要为多张表，所以同样，tables里面也只能有一张表，或者多张表，然后query里面的条件在这些表上面都可以运行。否则只好多写几条exp语句了。导出后，或许发现数据比较大，我们可以用一些压缩工具对数据进行二次压缩，例如用winzip， winrar， 7zip等第三方工具。同样，exp支持一个参数用户直接对数据进行压缩：comPRess = y， 这个参数直接加到命令的后面即可实现导出的同时压缩数据。导入数据imp我们知道怎么提取数据，那么还原的时候，就需要使用imp命令把导出的数据加载进去。