你好,游客 登录 注册 搜索
背景:
阅读新闻

使用rowid和rownum获取记录时要注意的问题

[日期:2014-10-20] 来源:Linux社区  作者:aaron8219 [字体: ]
我们知道,rowid和rownum在Oracle中都是可以被当做伪劣使用的,主要用来定位表中特定的记录,但它们是有区别的,rowid是和行记录的物理地址对应的,而rownum则不是,是通过返回的记录集来判断首条记录,即rownum=1的记录,然后再去fetch之后的记录,rownum=2、rownum=3……,以此类推。下面来看实验:
 
SQL> conn zlm/zlm
Connected.
SQL> create table test1 as select object_id,object_name from dba_objects where rownum<11;
 
Table created.
 
注意,创建表的时候,也用到了rownum<11这个伪列上的where条件,获取到10条记录插入到test1表。
 
SQL> set lin 130 pages 130
SQL> col object_name for a30
SQL> select * from test1;
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        20 ICOL$
        44 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
         3 I_OBJ#
        25 PROXY_ROLE_DATA$
        39 I_IND1
        51 I_CDEF2
        26 I_PROXY_ROLE_DATA$_1
 
10 rows selected.
 
用*来表示全部列,此时并不会显示出rowid,rownum这2个伪列,这也就是“伪列”这个名词的由来,是假的,fake的,我们可以使用它,但并不作为数据存储在表中
 
SQL> select rowid,rownum,object_id,object_name from test1;
 
ROWID                  ROWNUM  OBJECT_ID OBJECT_NAME
------------------ ---------- ---------- ------------------------------
AAAM+rAAGAAAACUAAA          1         20 ICOL$
AAAM+rAAGAAAACUAAB          2         44 I_USER1
AAAM+rAAGAAAACUAAC          3         28 CON$
AAAM+rAAGAAAACUAAD          4         15 UNDO$
AAAM+rAAGAAAACUAAE          5         29 C_COBJ#
AAAM+rAAGAAAACUAAF          6          3 I_OBJ#
AAAM+rAAGAAAACUAAG          7         25 PROXY_ROLE_DATA$
AAAM+rAAGAAAACUAAH          8         39 I_IND1
AAAM+rAAGAAAACUAAI          9         51 I_CDEF2
AAAM+rAAGAAAACUAAJ        10         26 I_PROXY_ROLE_DATA$_1
 
10 rows selected.
 
把列名全部显示指定,可以看到,rowid和rownum这两列的内容也都显示了。那我们能不能用rowid列来作为查找条件呢?当然是可以的,但前提是你要知道rowid值是怎么分布的
 
SQL> select rowid,object_id,object_name from test1 where rowid<to_char('AAAM+rAAGAAAACUAAK');
 
ROWID               OBJECT_ID OBJECT_NAME
------------------ ---------- ------------------------------
AAAM+rAAGAAAACUAAA         20 ICOL$
AAAM+rAAGAAAACUAAB         44 I_USER1
AAAM+rAAGAAAACUAAC         28 CON$
AAAM+rAAGAAAACUAAD         15 UNDO$
AAAM+rAAGAAAACUAAE         29 C_COBJ#
AAAM+rAAGAAAACUAAF          3 I_OBJ#
AAAM+rAAGAAAACUAAG         25 PROXY_ROLE_DATA$
AAAM+rAAGAAAACUAAH         39 I_IND1
AAAM+rAAGAAAACUAAI         51 I_CDEF2
AAAM+rAAGAAAACUAAJ         26 I_PROXY_ROLE_DATA$_1
 
10 rows selected.
 
为什么条件是<to_char('AAAM+rAAGAAAACUAAK')?因为通过刚才的观察,我们知道test1表中的第10条记录是到J,那么要获取这10条记录,就是比AAK小的这些记录
 
这里来说明一下rowid中这些字母表示的含义:
 
在Oracle 8以下,rowid(也叫受限rowid)为:FFFF.BBBBBBBB.RRRR,占用6个字节(10bit file#+22bit+16bit),但是,为了扩充的需要,如数据文件的扩充,现在的rowid改为:OOOOOOFFFBBBBBBRRR,占用10个字节(32bit+10bit rfile#+22bit+16bit)。其中,O是Object号,F是File号,B是Block号,R是Row号。由于rowid的组成从file#变成了rfile#,所以数据文件数的限制也从整个库不能超过1023个变成了每个表空间不能超过1023个数据文件这里的object_id,是与段物理存储位置相关的一个信息,因为一个段对象只可能在一个表空间上,object_id能唯一确认ts#,而object_id + rfile#就能最终定位到该rowid在哪个确定的物理数据文件上
 
因此,这里的AAG就表示这是第6个数据文件,要注意的是,AAA从0开始计数,行号也是如此。因此,AAA-AAJ就表示是test1表中的1-10行记录(0->9)
 
SQL> col name for a45
SQL> select file#,name from v$datafile;
 
     FILE# NAME
---------- ---------------------------------------------
         1 /u01/app/oracle/oradata/ora10g/system01.dbf
         2 /u01/app/oracle/oradata/ora10g/undotbs01.dbf
         3 /u01/app/oracle/oradata/ora10g/sysaux01.dbf
         4 /u01/app/oracle/oradata/ora10g/users01.dbf
         5 /u01/app/oracle/oradata/ora10g/example01.dbf
         6 /u01/app/oracle/oradata/ora10g/zlm01.dbf
 
zlm用户默认的表空间就是zlm,其对应的数据文件就是zlm01.dbf,可以看到,file#为6而并不是7
 
我们还可以用Oracle提供的dbms.rowid包来根据rowid的值来获取object_id#,rfile#,block#,row#这些具体的值:
 
SQL> select dbms_rowid.rowid_object('AAAM+rAAGAAAACUAAJ') object_id#,dbms_rowid.rowid_relative_fno('AAAM+rAAGAAAACUAAJ') rfile#,dbms_rowid.rowid_block_number('AAAM+rAAGAAAACUAAJ') block#,dbms_rowid.rowid_row_number('AAAM+rAAGAAAACUAAJ') row# from dual;
 
OBJECT_ID#     RFILE#     BLOCK#       ROW#
--------------- ---------- ---------- ----------
          53163          6        148          9
 
test1表中第10行记录就是第53163个对象、第6号文件、第148个块的第10条记录(0->9)
 
如果我们要获取test1表中的前5条记录,那么可以用如下的方法:
 
SQL> select object_id,object_name from test1 where rownum<=5;
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        20 ICOL$
        44 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
 
或者
 
SQL> select object_id,object_name from test1 where rownum!=6;
 
 OBJECT_ID OBJECT_NAME
---------- ------------------------------
        20 ICOL$
        44 I_USER1
        28 CON$
        15 UNDO$
        29 C_COBJ#
 
对于第一种写法,大家都可以理解,那么为什么rownum!=6这个条件,返回的也是前5条记录呢?是不是觉得不可思议,7-10条记录也满足!=6啊,为什么不会显示呢?
 
来看一下官方关于rowid机制的解释:

1 Oracle executes your query.

2 Oracle fetches the first row and calls it row number 1.

3 Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.

4 Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).

5 Go to step 3.


当你使用rownum作为查询条件是,Oracle的SQL引擎总是会先去找row number 1这条记录,如果没有找到,那么就直接丢弃这些row,直到找到为止,然后才会有row number 2,row number 3……不断地循环这个过程,直到结束不符合条件为止
 
在第2个查询中,由于rownum=1符合了!=6这个条件,那么会依次fetch下去,直到取到了rownum=5,都是符合的记录,然后就会返回一个结果,而由于中间断档了,那么当找到第6条记录的时候,显然不符合!=6这个条件,那么刚才的一轮循环就结束了,而之后的第7条记录,由于其中不再包含row numer 1这个必要条件,因此直接就丢弃了后面的查询,也就是说,8-10条记录也不会再去fetch了,因为没有一条可以获取到rownum=1

Oracle中rownum和rowid的理解 http://www.linuxidc.com/Linux/2012-04/58300.htm

Oracle rowid http://www.linuxidc.com/Linux/2013-03/80755.htm

Oracle入门基础教程:rowid详解 http://www.linuxidc.com/Linux/2012-10/73265.htm

Oracle 中ROWNUM用法总结,ROWNUM 与 rowid 区别 http://www.linuxidc.com/Linux/2012-07/66121.htm

rowid走索引之嫌疑犯抓获 http://www.linuxidc.com/Linux/2012-06/62302.htm

Oracle利用rownum和rowid分页 http://www.linuxidc.com/Linux/2012-04/58301.htm

Oracle 10g 中的ROWID详解  http://www.linuxidc.com/Linux/2014-06/103258.htm

更多详情见请继续阅读下一页的精彩内容http://www.linuxidc.com/Linux/2014-10/108251p2.htm

linux
相关资讯       rowid  Rownum 
本文评论   查看全部评论 (0)
表情: 表情 姓名: 字数

       

评论声明
  • 尊重网上道德,遵守中华人民共和国的各项有关法律法规
  • 承担一切因您的行为而直接或间接导致的民事或刑事法律责任
  • 本站管理人员有权保留或删除其管辖留言中的任意内容
  • 本站有权在网站内转载或引用您的评论
  • 参与本评论即表明您已经阅读并接受上述条款