什么是伪列RowID? 1.首先是一种数据类型,唯一标识一条记录物理位置的一个id,基于64位编码的18个字符显示 2.未存储在表中,可以从表中查询,但不支持插入,更新,删除它们的值 rowid 的用途: 1.在开发中使用频率应该是挺多的,特别在一些update语句中使用更加频繁。 所以oracle ERP中大部份的视图都会加入rowid这个字段。 在一些cursor定义时也少不了加入rowid。但往往我们在开发过程中,由于连接的表很多, 再加上程序的复制,有时忽略了rowid对应的是那一个表中rowid,所以有时过程出错, 往往发上很多时间去查错,最后查出来既然是update时带的rowid并非此表的rowid,所以在发现很多次的错误时, 重视rowid起来了,开发中一定要注意rowid的匹配 2.能以最快的方式访问表中的一行 3.能显示表的行是如何存储的 4.作为表中唯一标识 RowID的组成 rowid确定了每条记录是在Oracle中的哪一个数据对象,数据文件、块、行上。 ROWID 的格式如下: 数据对象编号 文件编号 块编号 行编号 OOOOOO FFF BBBBBB RRR 由 data_object_id# + rfile# + block# + row# 组成,占用10个bytes的空间, 32bit的 data_object_id#, 10 bit 的 rfile#, 22bit 的 block#, 16 bit 的 row#. 所以每个表空间不能超过1023个 数据文件 RowID的应用: 1.查找和删除重复记录 当试图对库表中的某一列或几列创建唯一索引时, 系统提示 ORA-01452 :不能创建唯一索引,发现重复记录。 /*conn scott/tiger Create table empa as select * from emp;插入重复记录 insert into empa select * from emp where empno = 7369; insert into empa select * from emp where empno = 7839; insert into empa select * from emp where empno = 7934; */ 查找重复记录的几种方法: 查找大量重复记录 select empno from empa group by empno having count(*) >1; Select * From empa Where ROWID Not In(Select Min(ROWID) From empa Group By empno); 查找少量重复记录 select * from empa a where rowid<>(select max(rowid) from empa where empno=a.empno ); 删除重复记录的几种方法 (1).适用于有大量重复记录的情况(列上建有索引的时候,用以下语句效率会很高): Delete empa Where empno In (Select empno From empa Group By empno Having Count(*) > 1) And ROWID Not In (Select Min(ROWID) From empa Group By empno Having Count(*) > 1); Delete empa Where ROWID Not In(Select Min(ROWID) From empa Group By empno); 2.Rownum的定义: 对于rownum来说它是oracle系统顺序分配为从查询返回的行的编号, 返回的第一行分配的是1,第二行是2,依此类推, 这个伪字段可以用于限制查询返回的总行数,且rownum不能以任何表的名称作为前缀。 ps: 另外:rowid 与 rownum 虽都被称为伪列,但它们的存在方式是不一样的, rowid 可以说是物理存在的,表示记录在表空间中的唯一位置ID,在DB中唯一。只要记录没被搬动过,rowid是不变的 rowid 相对于表来说又像表中的一般列,所以以 rowid 为条件就不会有 rownum那些情况发生。 另外还要注意:rownum不能以任何基表的名称作为前缀。 rownum限制: 只能用以上符号(<、<=、!=), 假设某个表 t1(c1) 有 20 条记录如果用 select rownum,c1 from t1 where rownum < 10, 只要是用小于号,查出来的结果很容易地与一般理解在概念上能达成一致,应该不会有任何疑问的。 可如果用 select rownum,c1 from t1 where rownum > 10 未选定行 先好好理解 rownum 的意义。 因为ROWNUM是对结果集加的一个伪列,即先查到结果集之后再加上去的一个列 (强调:先要有结果集)。 简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。 所以你选出的结果不可能没有1,而有其他大于1的值。 所以您没办法期望得到下面的结果集: 11 aaaaaaaa 12 bbbbbbb 13 ccccccc rownum 对于等于某值的查询条件 如果希望找到学生表中第一条学生的信息,可以使用rownum=1作为条件。 但是想找到学生表中第二条学生的信息,使用rownum=2结果查不到数据。 因为rownum都是从1开始,但是1以上的自然数在rownum做等于判断是时认为都是false条件, 所以无法查到rownum = n(n>1的自然数)。 SQL> select rownum,id,name from student where rownum=1 √ SQL> select rownum,id,name from student where rownum =2; × 答案:Select * from (select rownum as rn ,student.* from student) where rn=2 rownum对于大于某值的查询条件: 如果想找到从第二行记录以后的记录,当使用rownum>2是查不出记录的, 原因是由于rownum是一个总是从1开始的伪列,Oracle 认为rownum> n(n>1的自然数)这种条件依旧不成立,所以查不到记录。 查找到第二行以后的记录可使用以下的子查询方法来解决。注意子查询中的rownum必须要有别名,否则还是不会查出记录来 这是因为rownum不是某个表的列,如果不起别名的话,无法知道rownum是子查询的列还是主查询的列。 SQL>select * from(select rownum no ,id,name from student) where no>2; rownum对于小于某值的查询条件: rownum对于rownum<n((n>1的自然数)的条件认为是成立的,所以可以找到记录 SQL> select rownum,id,name from student where rownum <3; rownum和排序: 系统是按照记录插入时的顺序给记录(rownum)排的号,rowid也是顺序分配的。 SQL> select rownum ,id,name from (select * from student order by name); 但这样一来,效率会低很多. 我们可以在order by 的字段上加主键或索引: 即可让oracle先按该字段排序,然后再rownum;方法不变 select * from tabname where rownum<20 order by name select empno,sal from(select rownum as rn,empno,sal from (select * from emp order by sal desc ) )where rn=3; 另外,这个方法更快: select * from ( select rownum r,a from yourtable where r <= 9 order by name ) where r > 4 Oracle实现top n功能 : 由于oracle不支持select top语句,所以在oracle中经常是用order by跟rownum的组合来实现select top n的查询。 简单地说,实现方法如下所示: select 列名1...列名n from(select 列名1...列名n from 表名 order by 列名1...列名n) where rownum<=n(抽出记录数) order by rownum asc 现实emp表中工资前三位高的员工信息: select * from(select * from emp order by sal desc) where rownum<=3;