Oracle's Pseudocolumn ROWNUM & default Row Order & 基于此的三层嵌套分页查询

Oracle® Database SQL Reference 10g Release 2 (10.2) -> ROWNUM Pseudocolumn:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/pseudocolumns009.htm

引用

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT * FROM employees WHERE ROWNUM < 10;

If an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. The results can vary depending on the way the rows are accessed. For example, if the ORDER BY clause causes Oracle to use an index to access the data, then Oracle may retrieve the rows in a different order than without the index. Therefore, the following statement will not have the same effect as the preceding example:

SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

If you embed the ORDER BY clause in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. For example, the following query returns the employees with the 10 smallest employee numbers. This is sometimes referred to as top-N reporting:

SELECT * FROM
   (SELECT * FROM employees ORDER BY employee_id)
   WHERE ROWNUM < 11;

In the preceding example, the ROWNUM values are those of the top-level SELECT statement, so they are generated after the rows have already been ordered by employee_id in the subquery.

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT * FROM employees
    WHERE ROWNUM > 1;

The first row fetched is assigned a ROWNUM of 1 and makes the condition false. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

You can also use ROWNUM to assign unique values to each row of a table, as in this example:

UPDATE my_table
    SET column1 = ROWNUM;

ROWNUM and ORDER BY:
http://www.dbforums.com/oracle/988716-rownum-order.html

引用

The ROWNUM is applied before the ORDER BY

if Oracle uses an index to access the data, then the data will be selected in the order of the index. Let's do an example:

SQL> create table t (a int, b int); 

Table created. 

SQL> insert into t values (1,7); 

1 row created. 

SQL> insert into t values (2,4); 

1 row created. 

SQL> insert into t values (3,8); 

1 row created. 

SQL> insert into t values (4,1); 

1 row created. 

SQL> select * from t; 

         A          B 
---------- ---------- 
         1          7 
         2          4 
         3          8 
         4          1 

SQL> select * from t where b > 0 and rownum = 1; 

         A          B 
---------- ---------- 
         1          7 

SQL> create index i on t(b); 

Index created. 

SQL> select * from t where b > 0; 

         A          B 
---------- ---------- 
         4          1 
         2          4 
         1          7 
         3          8 

SQL> select * from t where b > 0 and rownum = 1; 

         A          B 
---------- ---------- 
         4          1  

Oracle对一条select语句的解析过程：
1. The FROM/WHERE clause goes first.
2. ROWNUM is assigned and incremented to each output row from the FROM/WHERE clause.
3. SELECT is applied.
4. GROUP BY is applied.
5. HAVING is applied.
6. ORDER BY is applied.
So for query like below

select id from TableX where rownum < 10 order by id 

The oracle will select 10 rows and then applies the order by for that 10 rows.

Default sorting order used by oracle ：
http://forums.oracle.com/forums/thread.jspa?threadID=925465

引用

There is NO default sort order... If you want the data to be in any particular order, use ORDER BY

"Do not ever count on the order of rows from a query unless you have an ORDER BY statement on your query!"

Data in relational tables is stored in heap format. It is not stored in the memory locations physically adjacent to each other, nor it stores the data like that even if you force it. Data is retreived from the memory/disk etc based on different factors. Only explicit ORDER BY guarantees desired order.

To answer your direct question "why the order in the previous example differ".
When you added additional columns to the select list, that changed the query plan generated for the query. This caused Oracle to pick up the rows in a different order. There are a number of reasons this could happen, the new selected columns caused a different index (or no index) to be used for one of the tables in the query, it caused a different table to be be used as the "driving" table (i.e. the first one accessed), it caused a different join method to be used.

Will sql query without order by return same result set on multiple runs?
http://forums.oracle.com/forums/thread.jspa?threadID=2186334&tstart=30

引用

If there is no ORDER BY clause, then there is no guarantee that the order will remain the same. If the table is small, the output probably will be the same, but you can't rely on it.

In CONNECT BY queries, even without an ORDER BY or ORDER SIBLINGS BY clause, the output is partially ordered.

结论：
If you want the output to be in a particular order, then use an ORDER BY clause in the main query.
If you want ROWNUM to reflect a particular order, then use a sub-query that has an ORDER BY clause, and reference ROWNUM in the main query.

ROWNUM for Page selection：
http://dbaspot.com/forums/oracle-server/39171-rownum-page-selection.html

引用

There is no concept of default order in Oracle. The rows are
stored as an unordered set. The only ordering is what's implied from
the data itself (i.e. if you have a DATE column, you can order by that
column)

If you say "SELECT * FROM mytable" there is no guarantee what order
you'll get the rows back. And "SELECT * FROM mytable WHERE rownum <
100" may return different results each time you run it. Now, in
practice the first of these queries will *usually* return the rows in
the order in which they were inserted, and the second query will
*usually* return the first 99 rows that were inserted. But this is not
guaranteed, and you should never ever depend on it.

If you define a create timestamp or use a sequence for a primary key,
you can depend on that ordering. That's your solution, anything else
will break. (including using rowid instead of rownum)

http://blog.csdn.net/liuhl_h/archive/2010/09/29/5913612.aspx

引用

Oracle会根据具体的数据块的存储返回记录.
oracle数据库是没有默认排序的
要排序必须加上order  by
因为oracle是按块进行读取数据的
如果数据按顺序存储，则可能使读取出来的数据是按顺序的，给用户误解为默认排序

Oracle的rownum原理和使用
http://tenn.iteye.com/blog/99339


Oracle三层嵌套分页查询：
方式一：

select *   
from 
(   
    --第二层：给定行号   
    select rownum rn,a.* from (   
      --第一层：排序   
      select * from 表 order by 字段   
    ) a   
    where rownum <= 最大行
)
where rn >= 最小行

方式二：

select *   
from
(   
    --第二层：给定行号   
    select rownum rn,a.* from (   
      --第一层：排序   
      select * from 表 order by 字段   
    ) a   
)
where rn between 最小行 and 最大行   

关于这两种实现方式的比较：
http://yangtingkun.itpub.net/post/468/100278

引用

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

为什么基于ROWNUM的oracle分页实现，要采用三层嵌套的方式？
1 首先，在没有order by clause的情况下，oracle的查询结果的顺序会是不确定的。如上面的例子。所以order by的使用是应该的，以免因为index等的原因导致不确定的results order。
2 其次，在order by 和 ROWNUM同时使用时，oracle默认的策略是先为伪列rownum赋值，再order by。

引用

rownum与order by同时存在的问题
当 where 后面有rownum的判断，并且存在order by时候，rownum的优先级高！
　　oracle会先执行rownum的判断，然后从结果中order by，很明显是错误的结果啦！就好像学校要取成绩最好的前10名同学，结果这种方法一执行，成了取出10名同学，然后按照成绩的高低排序！
　　这点与SQL Server的TOP完全不同，TOP遇上order by，是先执行order by，在分页的；
　　解决办法就是先执行order by，然后嵌套执行rownum-----说白啦就是用()改变函数的优先级！

所以，第二层嵌套的目的就是：让结果先order by，再取rownum!
3 再次，因为rownum不可使用 >(=) 来判断的原因，所以需要最外围的第三层嵌套。