All things are difficult before they are easy. 没有软件的裸机是一具僵尸，没有硬件的软件是一个幽灵。2012，专注于Linux和C语言，关注自动化、性能测试，关注开源社区和开源测试工具、方法，尝试测试团队管理！
Oracle分页查询语句
上一篇 /
下一篇 2010-11-06 17:48:02
/ 个人分类：数据库
去年时，对页面上展示出来记录分页，感觉很神奇，后来进行了简单的学习；现在想起来，简单记录一下吧。 目前，很多网站，在页面上所看到的分页，都是通过在代码中写SQL语句就已经实现的。下面看一个实际的例子吧，按公司ID来筛选、按目录ID来分组排序并分页展示的SQL：select t.category_id as CATEGORY_ID, t.product_num as COUNT
from
(
select category_id,count(*) product_num
from product
where company_id = #companyId:DECIMAL#
and status = 'approved'
group by category_id
order by product_num desc
) t
where rownum <=10
------#companyId:DECIMAL# 这个是应用程序在实际运行时会传入的company_id ---------
|
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最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率
要比第一个查询低得多。
上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询或最内层查询包含排序的情况一样有效。附一个MySQL分页查询的实现：mysql中利用select语句的一个特性就可以很方便地实现查询结果的分页，select语句的语法：SELECT [STRAIGHT_JOIN] [SQL_SMALL_RESULT] [SQL_BIG_RESULT] [HIGH_PRIORITY]
[DISTINCT | DISTINCTROW | ALL]
select_expression,...
[INTO OUTFILE 'file_name' export_options]
[FROM table_references
[WHERE where_definition]
[GROUP BY col_name,...]
[HAVING where_definition]
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] ,...]
[LIMIT [offset,] rows]
[PROCEDURE procedure_name] ] |
LIMIT子句可以用来限制由SELECT语句返回过来的数据数量，它有一个或两个参数，如果给出两个参数，第一个参数指定返回的第一行在所有数据中的位置，从0开始（注意不是1），第二个参数指定最多返回行数。例如：select * from table LIMIT 5,10; #返回第6-15行数据select * from table LIMIT 5; #返回前5行select * from table LIMIT 0,5; #返回前5行参考资料：http://yangtingkun.itpub.net/post/468/100278 （该博客有很多关于Oracle的文章 很不错）http://fanqiang.chinaunix.net/db/mysql/2001-07-05/1428.shtml
收藏
举报
TAG:
MySQL
Oracle
oracle
分页查询
