ROWNUM的工作机制及原理
来源:互联网 发布:杰克奥特曼玩具淘宝 编辑:程序博客网 时间:2024/05/29 11:35
How ROWNUM Works
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;
未选定行
原因如下:
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.
本文来自CSDN博客,转载请标明出处:http://blog.csdn.net/High_Mount/archive/2008/07/22/2688836.aspx
- ROWNUM的工作机制及原理
- ORACLE ROWNUM 工作原理
- spring工作机制及原理
- Struts2的工作机制原理分析及实例
- Rownum产生原理及应用
- 理解IP路由器原理及工作机制
- Oracle的rownum原理和使用(整理几个达人的帖子)及rownum排序注意事项
- 从开发角度了解Zookeeper的工作原理及内部工作机制
- Struts2的工作原理及工作流程
- Struts2的工作原理及工作流程
- Rownum 的原理和使用
- 简述Oracle的rownum原理
- 漫谈Web Service工作原理及.NET平台的实现机制
- 漫谈Web Service工作原理及.NET平台的实现机制
- Framework 工作方式及原理,Activity 是如何生成一个 view 的机制是什么?
- JAVA垃圾回收机制的工作原理
- 垃圾回收机制的工作原理
- Android消息机制---MessageQueue的工作原理
- 获得一个数据在内存中存储的二进制形式
- 2010年上半年5月份软件设计师上午试题答案(分析与解答)之三
- AlphaBlend 技术
- asp读取word 文档内容转换为二进制数据流
- jsp上传文件到数据库和从数据库下载文件
- ROWNUM的工作机制及原理
- Tomcat启动时报 Exception loading sessions from persistent storage 的解决办法
- 2010年上半年5月份软件设计师上午试题答案(分析与解答)之四
- vertical-align 权威图解。
- 修改webconfig 连接数据库文件 session清空解决办法
- Delphi中动态链接库(DLL)的建立和使用
- 今日视点 2010.5.28
- 面向ERP产业未来发展趋势的运营战略
- 2010年上半年5月份软件设计师上午试题答案(分析与解答)之五