ROWNUM的概念
ROWNUM是一个虚假的列。它将被分配为 1,2,3,4,...N,N 是行的数量。一个ROWNUM值不是被永久的分配给一行 (这是最容易被误解的)。表中的某一行并没有标号;你不可以查询ROWNUM值为5的行——根本没有这个概念。另一个容易搞糊涂的问题是ROWNUM值是何时被分配的。ROWNUM值的分配是在查询的谓词解析之后,任何排序和聚合之前进行的。ROWNUM值只有当被分配之后才会增长。这就是为什么下面的查询永远都不会返回结果:
select * from countries where rownum>1;
ROWNUM > 1对于第一行来说并不是真值,ROWNUM没有增长到 2。所以,没有比1大的ROWNUM.永远都不要使用’ROWNUM>?‘和’ROWNUM=2…N’这样的条件。
用rownum显示排序结果的前N条记录
FROM/WHERE子句先被执行.
根据FROM/WHERE子句输出的行, ROWNUM被分配给他们并自增长.
SELECT 被应用.
GROUP BY 被应用.
HAVING is 被应用.
ORDER BY 被应用.
这就是为什么下面的SQL几乎总是错误的:
select rownum,job_title,min_salary
from jobs where rownum<3 order by min_salary;
正确的写法:
select rownum,tmp.* from (
select job_title,min_salary
from jobs order by min_salary) tmp
where rownum<=3;
用ROWNUM实现分页
select * from
(select /*+ FIRST_ROWS(n) */ a.*,
ROWNUM rnum
from (your_query_goes_here, with order by) a
where ROWNUM <=
:MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
FIRST_ROWS(N)使优化器考虑最短时间获得前N条记录.
:MAX_ROW_TO_FETCH 某一页中结果集的最后一行。如果你每页显示10行,要显示第6页,那么此值取60。
:MIN_ROW_TO_FETCH 某一页中结果集的第一行。如果你每页显示10行,要显示第6页,那么此值取50。
ROWNUM对性能的影响
ROWNUM可以避免oracle在磁盘上进行排序。rownum无法避免全表扫描的发生,但是它可以避免对整个表数据的排序操作,在指定了rownum后,排序操作在内存中可以轻松完成。
=================================================
对于 Oracle 的 rownum 问题,很多资料都说不支持>,>=,=,between...and,只能用以上符号(<、<=、!=),并非说用>,>=,=,between..and 时会提示SQL语法错误,而是经常是查不出一条记录来,还会出现似乎是莫名其妙的结果来,其实您只要理解好了这个 rownum 伪列的意义就不应该感到惊奇,同样是伪列,rownum 与 rowid 可有些不一样,下面以例子说明
假设某个表 t1(c1) 有 20 条记录
如果用 select rownum,c1 from t1 where rownum < 10, 只要是用小于号,查出来的结果很容易地与一般理解在概念上能达成一致,应该不会有任何疑问的。
可如果用 select rownum,c1 from t1 where rownum > 10 (如果写下这样的查询语句,这时候在您的头脑中应该是想得到表中后面10条记录),你就会发现,显示出来的结果要让您失望了,也许您还会怀疑是不谁删了一些记录,然后查看记录数,仍然是 20 条啊?那问题是出在哪呢?
先好好理解 rownum 的意义吧。因为ROWNUM是对结果集加的一个伪列,即先查到结果集之后再加上去的一个列 (强调:先要有结果集)。简单的说 rownum 是对符合条件结果的序列号。它总是从1开始排起的。所以你选出的结果不可能没有1,而有其他大于1的值。所以您没办法期望得到下面的结果集:
11 aaaaaaaa
12 bbbbbbb
13 ccccccc
.................
rownum >10 没有记录,因为第一条不满足去掉的话,第二条的ROWNUM又成了1,所以永远没有满足条件的记录。或者可以这样理解:
ROWNUM是一个序列,是oracle数据库从数据文件或缓冲区中读取数据的顺序。它取得第一条记录则rownum值为1,第二条为2,依次类推。如果你用>,>=,=,between...and这些条件,因为从缓冲区或数据文件中得到的第一条记录的rownum为1,则被删除,接着取下条,可是它的rownum还是1,又被删除,依次类推,便没有了数据。
有了以上从不同方面建立起来的对 rownum 的概念,那我们可以来认识使用 rownum 的几种现像
1. select rownum,c1 from t1 where rownum != 10 为何是返回前9条数据呢?它与 select rownum,c1 from tablename where rownum < 10 返回的结果集是一样的呢?
因为是在查询到结果集后,显示完第 9 条记录后,之后的记录也都是 != 10,或者 >=10,所以只显示前面9条记录。也可以这样理解,rownum 为9后的记录的 rownum为10,因条件为 !=10,所以去掉,其后记录补上,rownum又是10,也去掉,如果下去也就只会显示前面9条记录了
2. 为什么 rownum >1 时查不到一条记录,而 rownum >0 或 rownum >=1 却总显示所以的记录
因为 rownum 是在查询到的结果集后加上去的,它总是从1开始
3. 为什么 between 1 and 10 或者 between 0 and 10 能查到结果,而用 between 2 and 10 却得不到结果
原因同上一样,因为 rownum 总是从 1 开始
从上可以看出,任何时候想把 rownum = 1 这条记录抛弃是不对的,它在结果集中是不可或缺的,少了rownum=1 就像空中楼阁一般不能存在,所以你的 rownum 条件要包含到 1
但如果就是想要用 rownum > 10 这种条件的话话就要用嵌套语句,把 rownum 先生成,然后对他进行查询。
select *
from (selet rownum as rn,t1.* from a where ...)
where rn >10
一般代码中对结果集进行分页就是这么干的。
另外:rowid 与 rownum 虽都被称为伪列,但它们的存在方式是不一样的,rowid 可以说是物理存在的,表示记录在表空间中的唯一位置ID,在DB中唯一。只要记录没被搬动过,rowid是不变的。rowid 相对于表来说又像表中的一般列,所以以 rowid 为条件就不会有 rownum那些情况发生。
另外还要注意:rownum不能以任何基表的名称作为前缀。
http://www.cnblogs.com/chinhr/archive/2007/09/30/911685.html
http://blog.csdn.net/songsenkeji/archive/2009/08/11/4432942.aspx
分享到:
相关推荐
oracle 分页查询 使用ROWNUM技巧及陷阱
Oracle中rownum的使用
关于oracle的rownum关于oracle的rownum关于oracle的rownum关于oracle的rownum关于oracle的rownum
oracle rownum 使用小技巧 里面包含分页
注意:对 rownum(伪列) 只能使用 < 或 <=, 而用 =, >, >= 都将不能返回任何数据 例:查询工资前20名的员工姓名,工资 ,工资由高到低 Select rownum,first_name,salary from (Select first_name, salary ...
仅仅使用select语句就可以完成分页,学习一下rownum 会有用处的
有了以上从不同方面建立起来的对 rownum 的概念,那我们可以来认识使用 rownum 的几种现像 1. select rownum,c1 from t1 where rownum != 10 为何是返回前9条数据呢?它与 select rownum,c1 from tablename where ...
oracle rownum和distinct
oracle 的rownum和咱们平时用的sqlserver的区别,其中好多的不一样!
oracle 数据库 rowid与rownum的使用
ORACLE 中ROWNUM用法总结 整理版本,详细明了
oracle_SQL中rowid与rownum的使用
ORACLE 中ROWNUM用法总结!数据库管理员及程序开发员必看。
oracle_SQL中ROWID与ROWNUM的使用 很有用哦
举例介绍在oracle中rownum和rowId的不同以及使用方法
ROWNUM 是一种伪列,它会根据返回记录生成一个序列化的数字。利用ROWNUM,我们可以生产一些原先难以实现的结果输出,但因为它是伪列的这个特殊性,我们在使用时也需要注意一些事项,不要掉入“陷阱”。
详细解析了oracle的rownum,举例说明rownum的使用方法和技巧及要注意的地方。研究在想要查找前N个数据时,如何高效地使用rownum。
Oracle Rownum的使用与JSP分页显示的实现.pdf
内涵oracle rownum的详细讲解。
oracle rownum