使用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 130SQL> col object_name for a30SQL> 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_USER1AAAM+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_IND1AAAM+rAAGAAAACUAAI          9         51 I_CDEF2AAAM+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_USER1AAAM+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_IND1AAAM+rAAGAAAACUAAI         51 I_CDEF2AAAM+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 a45SQL> 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 下一頁