[Database] Oracle ROWNUM and ROWID

[ROWNUM]

* Definition
- 執行結果資料的列數
- Is a pseudo-column that returns a row's position in a result set.
- Is evaluated AFTER records are selected from the database and BEFORE the execution of ORDER BY clause.
- Only exists for a row once it is retrieved from a query. It represents the sequential order in which Oracle has retrieved the row. Therefore it will always exist, be at least 1, and be unique (among the rows returned by the query).


* 運作方式:

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 > (condition) 是不合理的，因為在第三步時會不符合條件而不在結果內，第四步查出來的 ROWNUM 仍然是1，所以永遠也不會達成條件。
- 同樣道理，ROWNUM 如果單獨用 = ，也只有在 ROWNUM=1 時才有用。

* 因此，如果需要查詢 ROWNUM 在某區間的資料時，就必須使用子查詢的方式，但數據量大時會影響速度。
- ex: select * from(select rownum no ,id,name from student) where no>2;。
- 注意子查詢中的 rownum 必須要有别名，否則還是會查不到。因為 ROWNUM 不是真正屬於某個表的列，若沒有別名會無法判斷是子查詢還是主查詢的 ROWNUM。

* 還有，若是和 ORDER BY 同時使用的話，也必須使用子查詢，因為 ROWNUM 會比 ORDER BY 先被執行而指定，指定完列數後才會被 ORDER BY，這樣一來，最後的 ROWNUM 也亂掉了。


[ROWID]

* ROWID actually represents the physical location of the record/row in the database. That being the case, it is (according to Oracle documentation) the fastest way to retrieve a particular row. Faster than an index, even.

* Also both referred to as pseudo-columns. That is, they are not "real" columns that will show up when you DESC a table. They don't actually exist anywhere in the database. But they're available for you to use.



* Reference
- ROWNUM - Oracle FAQ
- OracleBlog: ROWNUM and ROWID
- Oracle的rownum原理和使用 - DCTM + SAP - ITeye技术网站 **