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

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

我们知道，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的理解

Oracle rowid

Oracle入门基础教程：rowid详解

Oracle 中ROWNUM用法总结，ROWNUM 与 rowid 区别

rowid走索引之嫌疑犯抓获

Oracle利用rownum和rowid分页

Oracle 10g 中的ROWID详解 

更多详情见请继续阅读下一页的精彩内容：

1 2 下一页