【oracle rowid與rownum的使用與區別 】

一、rowid的定義：
 1、rowid是資料庫的一個偽列，建立表的時候資料庫會自動為每個表建立ROWID列,是資料庫中每一條記錄的唯一標識，儲存每條記錄的實際實體地址，對記錄的訪問是基於ROWID。但它實際上不儲存在表中，可以從表中查詢，但不支援插入，更新，刪除它們的值。
 ROWID可以分為物理rowid和邏輯rowid兩種。普通的堆表中的rowid是物理rowid，索引組織表(IOT)的rowid是邏輯rowid。
 兩者的區別可以參照：http://macherater.itpub.net/  發表人:macherater | 發表時間: 2008年十二月27日, 19:08
 
 2、rowid的顯示方式：
 在oracle 8版本以前，rowid又稱限制rowid，由file# block# row#組成，佔用6個bytes的空間，共48位，10 bit 的 file# ，22bit 的 block# ，16 bit 的 row#。
 從oracle 8開始rowid變成了extend rowid又稱擴充套件rowid，由data_object_id# rfile# block# row#組成，基於64位編碼的18個字元顯示，oracle在設計rowid時用A~Z表示0-25,用a~z表示26-51,用0~9表示52-61,用+表示62，用/表示63
 例如：OOOOOO FFF BBBBBB RRR 對應 data_object_id#, rfile#, block#, Rows#
 解釋為什麼引用data_object_id#參照：

 3、rowid的儲存方式是：10byte 個位元組即80bit儲存，其中資料物件編號需要32 位，相關檔案編號需要10 位，塊編號需要22位，行編號需要16 位，
  由此，我們可以得出：
  32bit的object number，每個資料庫最多有4G個物件
  10bit的file number，每個物件最多有1022個檔案（2個檔案預留）
  22bit的block number，每個檔案最多有4M個BLOCK
  16bit的row number，每個BLOCK最多有64K個ROWS
 4、rowid的使用：用於查詢和刪除重複資料
     查詢大量重複記錄
     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 );

以上只是對rowid初步的一點積累，多為網上資料整理，對其中的物理rowid和邏輯rowid的區別，file#與rfile#的區別以及data_object_id#應用，整個rowid號的各個組成部分的具體應用，
local index和global index區別與應用都有待進一步研究

二、rownum：沒有1就永遠不會有2!
 1、rownum也是oracle資料庫的偽列，是對結果集加的一個偽列，即先查到結果集之後再加上去的一個列 (強調：先要有結果集)。簡單的說 rownum 是對符合條件結果的序列號。它總是從1開始步進為1遞增排起的。如果條件符合要求,ROWNUM進入了結果集,接下來ROWNUM才會為2,如果ROWNUM沒有進入結果集,也就是ROWNUM=1時不符合條件,接下來ROWNUM還是為1.所以你選出的結果不可能沒有1，而有其他大於1的值。

 
2、rownum的使用：只能用以上符號(=、<、<=、!=、between 0/1 and )
 (1) rownum 對於等於某值的查詢條件，由於rownum必須從一開始，所以只能rownum=1
  select rownum,id,name from student where rownum=1;

 (2) rownum對於大於某值的查詢條件
 如果想找到從第二行記錄以後的記錄，當使用rownum>2是查不出記錄的，原因是由於rownum是一個總是從1開始的偽列，Oracle 認為rownum> n(n>1的自然數)這種條件依舊不成立，所以查不到記錄。

 查詢到第二行以後的記錄可使用以下的子查詢方法來解決。注意子查詢中的rownum必須要有別名，否則還是不會查出記錄來，這是因為rownum不是某個表的列，如果不起別名的話，無法知道rownum是子查詢的列還是主查詢的列。
 select * from(select rownum no ,id,name from student) where no>2;
 例如要查詢rownum在第二行到第三行之間的資料，包括第二行和第三行資料
  select * from (select rownum no,id,name from student where rownum<=3 ) where no >=2;

 (3)rownum對於小於某值的查詢條件
 rownum對於rownum1的自然數）的條件認為是成立的，所以可以找到記錄。
 select rownum,id,name from student where rownum <10;
 同時
 select rownum,c1 from t1 where rownum between 1 and 10 或者select rownum,c1 from t1 where rownum between 0 and 10
 select rownum,c1 from t1 where rownum != 10
 查詢到的資料和小於號查詢到的資料一樣

 3、rownum與排序的使用：
 Oracle中的rownum的是在取資料的時候產生的序號，如果想對指定排序的資料去指定的rowmun行資料就必須注意了。
 
 select rownum ,id,name from student order by name;

    ROWNUM ID     NAME

---------- ------ ---------------------------------------------------

         3 200003 李三

         2 200002 王二

         1 200001 張一

         4 200004 趙四

可以看出，rownum並不是按照name列來生成的序號，而是先rownum在排序。系統是按照記錄插入時的順序給記錄排的號，rowid也是順序分配的。為了解決這個問題，必須使用子查詢；

SQL> select rownum ,id,name from (select * from student order by name);

    ROWNUM ID     NAME

---------- ------ ---------------------------------------------------

         1 200003 李三

         2 200002 王二

         3 200001 張一

         4 200004 趙四

這樣就成了按name排序，並且用rownum標出正確序號（有小到大）
如果有一上百萬條記錄的表，在jsp頁面中需對該表進行分頁顯示，便考慮用rownum來作，下面是具體方法(每頁顯示20條)：

“select * from tabname where rownum<20 order by name" 但卻發現oracle卻不能按自己的意願來執行，而是先隨便取20條記錄，然後再order by，後經諮詢oracle,說rownum確實就這樣，想用的話，只能用子查詢來實現先排序，後rownum，方法如下：

"select * from (select * from tabname order by name) where rownum<20",但這樣一來，效率會低很多。

如果在order by 的欄位上加主鍵或索引即可讓oracle先按該欄位排序，然後再rownum；方法不變：    “select * from tabname where rownum<20 order by name"

文章轉載於http://blog.163.com/jun_ai_ni_1314/blog/static/137848055201002611117259/