Oracle Rownum 解析

现象：

SQL> select rownum,emp.* from emp;

    ROWNUM          A
---------- ----------
         1          1
         2          2
         3          1
         4          1

SQL> select rownum,emp.* from emp where rownum<3;

    ROWNUM          A
---------- ----------
         1          1
         2          2

SQL> select rownum,emp.* from emp where rownum=2;

未选定行

解释（google到的，不过自己后面也理解了）：

How ROWNUM Works

ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N, where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table—there is no such thing.

！ROWNUM是在什么时候被赋予每条记录的!
Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

select *
from t
where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure:

select ..., ROWNUM
from t
where <where clause>
group by <columns>
having <having clause>
order by <columns>;

Think of it as being processed in this order:

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.

That is why a query in the following form is almost certainly an error:

select *
from emp
where ROWNUM <= 5
order by sal desc;

The intention was most likely to get the five highest-paid people—a top-N query. What the query will return is five random records (the first five the query happens to hit), sorted by salary. The procedural pseudocode for this query is as follows:

ROWNUM = 1
for x in
( select * from emp )
loop
exit when NOT(ROWNUM <= 5)
OUTPUT record to temp
ROWNUM = ROWNUM+1
end loop
SORT TEMP

It gets the first five records and then sorts them. A query with WHERE ROWNUM = 5 or WHERE ROWNUM > 5 doesn't make sense. This is because a ROWNUM value is assigned to a row during the predicate evaluation and gets incremented only after a row passes the WHERE clause.

Here is the correct version of this query:

select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;

This version will sort EMP by salary descending and then return the first five records it encounters (the top-five records). As you'll see in the top-N discussion coming up shortly, Oracle Database doesn't really sort the entire result set—it is smarter than that—but conceptually that is what takes place.

posted on 2008-07-18 11:00 calmzeal 阅读(429) 评论(1) 编辑收藏举报