Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。

分页查询格式：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。

上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。

选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40

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

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。

这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。下面简单讨论一下多表联合的情况。对于最常见的等值表连接查询，CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）。

因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：

SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

我测试过了，很高，

===================================================
11-1关于分页
===================================================

select * from
(
select * from tbemp
where rownum

不知道javadiannet是什么意思，不过就你现在给出的sql片断来说，肯定是错误的。

问题是分页查询的结果是要排序的，但不是按ROWNUM排序

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME order by ID ) A
)
WHERE RN BETWEEN 21 AND 40

ORDER　BY　放在哪重查询内比较合适？

只有放在最内层，才能保证结果正确

你可以接续看看这个专题的其他文章，有几篇专门讨论分页中的排序问题

thx，还真解我燃眉之急了

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM = 21

为什么我试了这个语句报错呢：ORA-00911，在SQLPLUS中试的，错误指在“

错误指在小于号处，去掉最外层就ok了

晕，发现了，把(写成（了。再次感谢yangtingkun的分享

select * from tablename where rownum

楼上的什么意思

建议在查询的时候把*号用具体的字段代替,因为每次编译时是先把*号转成所有的字段名,这样无形中多了一次转换,效率会有所损失

这种写法表示的是一种通用的写法，其实实际上我并不建议开发人员写*，到不是分析时候的解析时间（这个代价几乎可以忽略），而是去掉不需要的字段，提高语句的执行效率。
另一方面，不适用*可以避免表添加字段对于查询语句的影响

请问如果我的查询结果集是超大数据量，
并且是多用户并发操作，
这样将会频繁的访问数据库
那这种分页实现方法还是否可行？
会不会急剧增加oracle server的压力？
如果真是如此，有什么解决方案吗？应该如何进行优化？
希望杨哥讲解
本来我也考虑用这种方法实现，但是被我们经理给否了...

结果集太大本身就与分页查询的原则违背。

对于这种情况，ALL_ROWS优化模式可能是最佳选择。

可以

-------------
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME order by ID ) A
)
WHERE RN BETWEEN 1 AND 99

请教：

分页时遇到一个问题，报ORA-01722：无效数字,数据库版本为10.2.0.1.0，奇怪现象为：
1.把A.*号写具体字段就没问题
2.把99改成100，也没有问题
3.把1改成10，把99改成100，还是有问题，但把99改成110就没有问题
太奇怪了，遇到2次了，怀疑是oracle 10gR2的bug，没有metalink的账号，麻烦帮忙查一下，多谢！

问题已解决，不好意思，原因是TABLE_NAME是个视图，里面某个字段to_number转换的时候，有些数据不合法造成的。不过还是有点纳闷，为什么改动BETWEEN 两边的数字，有时候不报错，看来还是对oracle中的查询理解的不深入，要多研究研究了！

SELECT * FROM
(
SELECT A.*, Ｂ.id,ROWNUM RN
FROM (SELECT * FROM TABLE_NAME order by ID ) A JOIN B ON A.ID=B.ID
)
WHERE RN BETWEEN 1 AND 99
在述多表连接查询时,里面的排序就有些乱套,单表查询就没问题,应该是多表的问题,请指教,急盼回复

排序应该放在连接的外面。

你的分页查询格式很多问题，你在仔细看看后面几篇文章吧

yangtingkun你好：
排序应该放在连接的外面，但RN的顺序全乱了,这个问题应该很有代表性,从后面的文章也没找到办法,麻烦解答一下,我的具体格式是这样的:
select * FROM(SELECT rownum RN,HPZL,HPHM,VEH_CODE.DMSM1,YWYY,CLPP,CLSBDH,SYR,BJRQ from(SELECT * FROM VEH_FLOW WHERE 条件 ORDER BY HPZL,YWLX,ROWID)A JOIN VEH_CODE ON(A.YWLX=VEH_CODE.DMZ)) WHERE RN BETWEEN 1 AND 99
要达到的目的排序输出,本人较菜,请指教,谢谢

你显然没有仔细看后面的文章。

标准的写法为
SELECT * FROM
(SELECT
A.*, ROWNUM RN
FROM
(SELECT RN,HPZL,HPHM,VEH_CODE.DMSM1,YWYY,CLPP,CLSBDH,SYR,BJRQ from(SELECT * FROM VEH_FLOW WHERE 条件)A JOIN VEH_CODE ON(A.YWLX=VEH_CODE.DMZ) ORDER BY HPZL,YWLX,ROWID) a
WHERE ROWNUM = 1

非常感谢yangtingkun的帮助，上述问题己解决．但还想请问一下如果在三表查询时，时间会长达两分钟以上，能有什么方法优化一下，两表只要６秒左右，表１有百万条记录，表２在４０万，表３在６Ｋ条．我是这样写的
SELECT * FROM
(SELECT
A.*, ROWNUM RN
FROM
(SELECT RN,HPZL,HPHM,VEH_CODE.DMSM1,YWYY,CLPP,CLSBDH,SYR,BJRQ from(SELECT * FROM VEH_FLOW WHERE 条件)A JOIN VEH_CODE ON(A.YWLX=VEH_CODE.DMZ)JOIN VEHICLE ON (A.HPHM=VEHICLE.HPHM ADN A.SYR=VEHICLE.SYR)ORDER BY HPZL,YWLX,ROWID) a
WHERE ROWNUM = 1
这条语句还能优化吗?

加上/*+ FIRST_ROWS */提示试试

后面的文章里面有详细描述

写的太好了，谢谢！

SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME where table_name.id='中文') A
WHERE ROWNUM = 21

当这个主健是中文时，查询非常非常慢。

该表就三个字段，id，name，bz。其中id是主健，当id值是中文时，上述sql执行非常慢，换成不是中文，字符的，很快。

在plsql里面连执行计划都出不来，请问：有什么陷阱吗？

你这个SQL有问题啊，ROWNUM = 21还能得到结果？

SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME where table_name.id='中文') A
WHERE ROWNUM = 21

当这个主健是中文时，查询非常非常慢。

该表就三个字段，id，name，bz。其中id是主健，当id值是中文时，上述sql执行非常慢，换成不是中文，字符的，很快。

在plsql里面连执行计划都出不来，请问：有什么陷阱吗？

不好意思，我拷贝错了，就是从上面拷贝下来的。可能拷贝了一个回复的内容。

你的SQL还是有问题。

用sqlplus的explain plan for和select * from table(dbms_xplan.display)来查看执行计划

啊，这个sql不是你文章中的sql吗？

SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM = 21

真奇怪，怎么递交之后，就变这个sql的啊。我就是拷贝你文章中的sql的。

呵呵，我文章中的sql和你贴出来的可是差的很远啊，你这个sql似乎根本没有拷完整，而且这个sql也是有问题的。

如果确实是blog造成的，你不妨将你的问题来论坛上贴出来，这里讨论本来就不是很方便

我直接描述我的问题了，不拷贝了：
现在我们在使用first_rows进行分页查询。在绝大数情况下，性能有了数据量级的提高。但是，时不时的有几个sql比不加提示，慢了很多很多，导致，无法正常使用。
现在观察到的现象是，当条件出现中文值的时候，SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME where table_name.id='中文' 后半段我不拷了，呵呵，你应该明白了。

所以，我想问的是：first_rows的使用，有什么限制吗？

另外：论坛在哪里？

我已经发贴在论坛了，标题是：讨论first_rows的问题。

谢谢！

这种分页方式确实不错，但在数据稍大的时候会出现奇怪的现象：记录会重复出现，例如某条记录在第3页出现了，可能在第5页又出现了一次，不知道楼主遇到过没有，我这里有个表大概300万，出现概率很高。

请问一下，

1, oracle的分页功能，是不是每一页都会往数据库发送一次SQL语句？

2, 但是由于采用了替换变量，所以这个影响不会很大？

1.对，每次分页都是一个新的查询。
2.是的

但是分页查询对分页最后的部分，性能并不好。

最近正在研究这个问题。

发现楼主的方法在没有orderby的情况下确实解决了性能问题。但是如果有orderby，数据就会错误。。

SQL会先按照rownum的限制去查询出一定数量的数据，然后再根据orderby来排序。
（提示，在测试时orderby不使用带索引的字段或主键，如果这些字段没有重复，会选出正确数据，但往往我们的orderby的字段并不是主键，也没有索引）

一直没有找到好的方法解决这个问题，希望有结果的朋友，可以发邮件一起研究一下。
terrycui@oval-tech.com

如果你的SQL排序结果是错的，说明你的SQL本身就问题，多半ORDER BY放到了外层。

你继续看这个系列的后面几篇文章吧，里面有比较详细的描述

最后一页速度慢是很明显的，因为分页优化的目标就是前面的部分，对于最后几页，FIRST_ROWS的效率还不如ALL_ROWS

最简单的方法，对于分页后面的部分，改为ALL_ROWS优化模式。
如果效率仍然不高，可以再考虑一些其他的方法，比如倒排序，或者分页只取ID，之后再关联其他字段

现在用的就是ALL_ROWS模式
慢的有点离谱！可以倒我发的那个帖子看一下，里面很详细

抱歉，我是匿名用户，没有办法在你的帖子里面发言。

如果要问Oracle问题，建议你去ITPUB论坛，比较Oracle方面那里比CSDN专业一些。
到现在为止，还没有看到你SQL的执行计划，就没有办法进一步讨论了，如果你想把问题搞明白，建议去ITPUB发一个帖子，如果愿意，可以将地址发到这里，我帮你看看。

select * from
( select a.*,row_number over( order by a.id ) rn
from a
)a
where a.rn between 1 and 20

这种方式无法避免表的全扫描

分页查询格式：

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM = 21

中的条件改为

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <41 where rn>20

这样速度要快一些。。。

你的第一个SQL根本就是错的。

你的第二个SQL也确实括号。

而且你的结论也是错误的，建议你仔细测试一下

碰到比较奇怪的问题，借用宝地描述一下，请问各位碰到过类似情况吗？
我的内嵌语句比较复杂，但是和以下结构类似
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM = 21

关键是执行内嵌语句非常快（大概1秒）就出结果了，但是只要加上外层的where rn >= 0就变得非常慢，17、8秒才出结果

楼上的SQL本身就有问题，ROWNUM = 21还能出结果？

查询速度有差别的话，看看执行计划是否发生了改变。

杨哥, 类似这样的查询
SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM = 21

写成这样不行吗?
select * from
(
select rownum rn, a.* from table a
where rownum = 21

晕, 这个blog有点问题, 回复和显示的不一样.
我的意思是第一种写成2层的, 内层像这样:
select rownum rn, a.* from table a
where rownum

还是看不懂楼上打算说什么

还是看不懂楼上打算说什么

还有这种现象？试试看
“12343>234”
“232 1234
989 <2344>

嗯，果然有问题

应该可以，就和斜杠一样，就是太麻烦了

SELECT * FROM (
SELECT t.*,row_number() over(ORDER BY o_id) r
FROM t_order t
ORDER BY t.o_id
) t
WHERE r BETWEEN 1 AND 6

分析函数不见得一定高效，目前看二者效率应该相差不多，很快会写一些描述利用分析函数进行分页的文章

我有个问题，想请教下，如果id值不是连续的，比如说21后面23，24被删除了，又要每次都查出来的是20条，怎么办？

看来这个查询是有问题的，谁能保证表里的数据不被删除？

这个查询和ID没有关系，完全是ROWNUM控制的，楼上的自己试试就明白了

怎么动态的传参数啊 ？ 比如点击下一页怎么 传入 pageSize page 还有条件。 希望楼主帮帮忙. QQ ： 452241943 谢谢

这个是靠程序实现，而不是SQL实现的

我有个问题,请帮助,如果每页只有一条数据来分页,我这个语句有点问题,第一页和第二页的数据是一样的.也就是有条数据的rownum是1又是2
select * from
(select aa.*,rownum rownum_ from (select a.* from WS_MON_JOB a inner join
(select g.GROUPNO from AUTH_USER_GROUP ug left join AUTH_GROUP g on ug.groupno=g.groupno where ug.userno='test' order by g.GROUPNO) b on a.GAME_NAME=b.groupno where 1=1 and JOB_FROM='level_cur'
) aa where rownum 0

还有个问题请教,这样的分组查询语句,怎么分页?
select lev,sum(PSN_NUM) from WS_RPT_IMP_ADVT_RSLT where userno='test' group by LEV order by LEV

to think
一条语句的ROWNUM是唯一确定的，倒是有可能你的查询包括重复的数据

对于第二个问题，包括分组的SQL的分页和其他SQL没有什么不同，不过效率会低一些。

第一层：查询出要排序的正确的结果集，如果没有索引的话，建议不要加ROWNO，加了反而会出错的。
第二层：对第一层的数据添加上ROWNO，此时才是最正确的数据顺序。
第三层：联合第二层的数据，筛选出最适合的数据。

大数据量的操作，本来就不建议使用的。就像我公司有7000万数据一样，直接在Oracle里面运行一下 select count(1) from tablename; 这样的语句运行一下，估计要3分钟左右，试着排序一下，还不要命了啊？

我一向不建议在分页时候先执行COUNT(*)，其实知道一共有多少页，在绝大部分情况下都没有意义。

如果是直接分页，配合合适的索引，那么数据量再大，也会在短时间内返回前面几页的结果

两层查询不行吗？
select * from (select a.*,rownum as num from table a where rownum=20

select * from (select a.*,rownum as num from table a where rownum=20

内层：
select a.*,rownum as num from table a where rownum 小于= 40
外层：
select * from 内层 where num 大于=20

这样两层不就可以了吗？

对于不需要排序的情况，两层没有问题。

确实，需要排序的话，两层就会有问题。如果两层的话，会按rownum去后再排序。这样就不对啦，哈哈。

学习了

我现在也遇到了这样的问题，不知道你找到解决方案了没？

不知道楼上提到的解决方案是什么？

按这种方式理解，查询前面几页oracle会很快，如果越查询到后面，速度越慢，特别是千万级的记录，应为第二层能往里面推，即rownum小于多少被推进去，就是说首先执行的sql是SELECT * FROM TABLE_NAME WHERE ROWNUM

是的，这就是分页的特点

select * from [table] where [condition]=? minus

select * from [table] where [condition]=?;

两个问题，首先如果表的数据量大的时候，这个查询的性能会非常的差，不但扫表两次，还要对大量的数据进行排序，效率极低。
其次也是最重要的问题，MINUS会导致重复记录被合并，因此使用这种方式分页得到的结果不是真正的查询结果

select * from
(
select *, rownum rn from table where rn=11;

这样是不是也可以分页阿?

原来两层也是可以的 只是3层能有利于排序
受教了

order by 放在最里层的select 语句里面