Oracle分页查询语句(一)
===========================================================
作者: yangtingkun(http://yangtingkun.itpub.net)
发表于: 2006.05.21 23:53
分类: ORACLE
出处: http://yangtingkun.itpub.net/post/468/100278
---------------------------------------------------------------
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
原始未分页查询Sql代码如下:
select ROWNUM rn, t.id ID, o.name YYB,u.name XM, t.MC from tZDYSX
t,tuser u,lborganization o where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1
order by ID
结果如下:
RN ID YYB XM MC
---------- ----------------- ----------------------
3 49 某证券总部 管理员 测试
4 96 某证券总部 管理员 持有上港10000股以上
5 102 某证券总部 管理员 十年规划
14 105 某证券总部 管理员 开发渠道为上海
11 106 某证券总部 管理员 万科A
12 107 某证券总部 管理员 11
13 108 某证券总部 管理员 今天过生日的客户
2 109 某证券总部 管理员 客户状态正常
6 110 某证券总部 管理员 无交易
7 111 某证券总部 管理员 OA
8 112 某证券总部 管理员 幸运客户
9 113 某证券总部 管理员 风险型
10 114 某证券总部 管理员 tst
22 115 白沙网上交易 安昌彪 安客户正常
1 118 某证券总部 管理员 213
18 119 某证券总部 管理员 客户号包含1008
17 120 某证券总部 管理员 aaa
19 123 某证券总部 管理员 ssssssss
20 124 某证券总部 管理员 www
21 126 某证券总部 管理员 123123
15 127 某证券总部 管理员 1212
16 128 某证券总部 管理员 aaaaaa
22 rows selected
最初我使用如下Sql代码查询:
select * from (select ROWNUM rn, t.id ID, o.name YYB,u.name XM, t.MC
from tZDYSX t,tuser u,lborganization o where t.cjr=u.id and u.orgid=o.orgcode and
t.gx = 1 order by t.ID )Where rn>10 and rn<=20;
这种方法能成功分页,结果如下:
RN ID YYB XM MC
---------- ----------------- -------------------
14 105 某证券总部 管理员 开发渠道为上海
11 106 某证券总部 管理员 万科A
12 107 某证券总部 管理员 11
13 108 某证券总部 管理员 今天过生日的客户
18 119 某证券总部 管理员 客户号包含1008
17 120 某证券总部 管理员 aaa
19 123 某证券总部 管理员 ssssssss
20 124 某证券总部 管理员 www
15 127 某证券总部 管理员 1212
16 128 某证券总部 管理员 aaaaaa
10 rows selected
从结果看来,有个问题:此语句Sql代码
order by CJSJ DESC 被执行,但是是在分后的第11到20条记录的结果集中再进行排序,而不是先排序后分页。(本来希望显示ID为112到126,结果变为105到128)
后来变为以下Sql代码查询:
SELECT *
FROM(
SELECT ROWNUM RN,TA.*
FROM(
select t.id ID, o.name YYB,u.name XM, t.MC
from tZDYSX t,tuser u,lborganization o
where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 order by t.ID
)TA WHERE ROWNUM <= 20
)WHERE RN > 10
SELECT *
FROM(
SELECT ROWNUM RN,TA.*
FROM(
select t.id ID, o.name YYB,u.name XM, t.MC
from tZDYSX t,tuser u,lborganization o
where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 order by t.ID
)TA WHERE ROWNUM <= 20
)WHERE RN > 10
结果如下:
RN ID YYB XM MC
---------- ----------------- -------------------
11 112 某证券总部 管理员 幸运客户
12 113 某证券总部 管理员 风险型
13 114 某证券总部 管理员 tst
14 115 白沙网上交易 安昌彪 安客户正常
15 118 某证券总部 管理员 213
16 119 某证券总部 管理员 客户号包含1008
17 120 某证券总部 管理员 aaa
18 123 某证券总部 管理员 ssssssss
19 124 某证券总部 管理员 www
20 126 某证券总部 管理员 123123
10 rows selected
看来结果是正确的。
总结:第二种方法其中最内层的查询Sql代码
select t.id ID, o.name YYB,u.name XM, t.MC
from tZDYSX t,tuser u,lborganization o
where t.cjr=u.id and u.orgid=o.orgcode and t.gx = 1 order by t.ID
表示不进行翻页的原始查询语句。ROWNUM <= 20和RN > 10控制分页查询的每页的范围。
第二种方法在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小,在上面的分页查询语句中,这种考虑主要体现在WHERE ROWNUM <= 20这句上。
选择第11到20条记录存在两种方法,第二种方法正是在查询的第二层通过ROWNUM <= 20来控制最大值,在查询的最外层控制最小值。而第一种方法是去掉查询第二层的WHERE ROWNUM <= 20语句,在查询的最外层控制分页的最小值和最大值。
一般来说,第二个查询的效率比第一个高得多,这是由于CBO 优化模式下,Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率。对于第二个查询语句,第2层的查询条件WHERE ROWNUM <= 20就可以被Oracle推入到内层查询中,这Oracle查询的结果一旦超过了ROWNUM限制条件,就终止查询将结果返回了。
而第一个查询语句,由于查询条件Where rn>10 and rn<=20是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义,因为最内层查询不知道RN代表什么)。因此,对于第一个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成,显然这个效率要比第二个查询低得多。
这种分页对于单表查询、多表查询一样有效。
分享到:
相关推荐
### Oracle分页查询详解 #### 一、分页查询的重要性 在数据库操作中,分页查询是一项非常重要的技术。尤其当处理大量数据时,一次性获取所有数据不仅会消耗大量的网络带宽,还可能导致前端页面加载缓慢,用户体验...
根据所提供的信息,我们可以提炼出以下关于“ORACLE分页查询效率”的相关知识点: ### 一、Oracle中的分页查询 #### 1.1 分页查询的重要性 - **背景介绍**:在实际应用中,数据库查询结果往往非常庞大,一次性加载...
### Oracle 分页查询并返回总记录数据存储过程 在数据库应用开发中,为了提高用户体验以及减少服务器负担,分页查询成为了一种常见的技术手段。Oracle 数据库提供了多种方法来实现分页查询,其中使用存储过程是一种...
Oracle 分页查询优化与排序解决方案 本文主要解决 Oracle 分页查询中排序与效率问题,通过实践和分析,提供了两种解决方案,并对比了两种方法的优缺点。 知识点 1: Oracle 分页查询的基本概念 Oracle 分页查询是...
### 解决Oracle分页查询中排序与效率问题 在Oracle数据库中进行分页查询时,经常会出现性能瓶颈,尤其是在处理大数据量的情况下。本篇文章将详细探讨如何优化Oracle分页查询中的排序与效率问题。 #### 一、理解...
msql和oracle分页查询语句笔记
Oracle 分页查询详解 Oracle 分页查询是指从大型数据表中提取指定范围的记录,以便提高查询效率和减少数据传输量。常用的 Oracle 分页查询方法有三种:使用 ROWNUM、使用 ROW_NUMBER() 和使用子查询。 第一种方法...
Oracle 的分页查询 在 Oracle 中,分页查询是非常常见的需求,但是在使用查询条件时又不能使用大于号(>)。本文将讲解 Oracle 中的分页查询,包括使用 ROWNUM 伪列和 ORDER BY 子句对查询结果进行排序和分页。 一...
Oracle数据库在处理大数据量查询时,分页查询是一种常见的优化策略,目的是限制返回结果集的大小,加快查询速度,提供更好的用户体验。本文将详细探讨Oracle的分页查询语句及其存储过程。 首先,Oracle的分页查询...
首先,理解Oracle分页查询的基本结构。通常,Oracle的分页查询采用嵌套查询的方式实现。以下是一个典型的分页查询模板: ```sql SELECT * FROM ( SELECT A.*, ROWNUM RN FROM ( SELECT * FROM TABLE_NAME ) A ...
2. **构造SQL查询语句**:Oracle数据库支持使用`ROWNUM`进行分页查询。基本的SQL模板为: ```sql SELECT * FROM (SELECT t.*, ROWNUM rnum FROM (YOUR_SELECT_QUERY) t WHERE ROWNUM ) WHERE rnum >= :startRow `...
分页查询是Oracle数据库比较重要的一个知识点!希望这个对你有参照作用。
### Oracle分页(LIMIT方式的运用) 在数据库查询操作中,分页是非常常见的需求之一,尤其是在数据量较大的情况下,为了提高用户体验以及减轻服务器压力,...希望本文能为你理解和掌握Oracle分页查询提供一定的帮助。
总结来说,Oracle和JSP结合实现分页涉及以下几个步骤:在Oracle中构造分页查询,使用JDBC在Servlet中执行查询,然后在JSP页面中展示数据。理解并熟练掌握这些步骤对于开发高效、用户友好的数据驱动网站至关重要。在...
本篇文章将重点讨论如何使用Java JDBC和Oracle数据库实现面向对象的分页查询。 首先,我们需要了解面向对象编程的基本概念。面向对象编程(Object-Oriented Programming, OOP)是一种编程范式,强调用对象来表示...
在Oracle数据库中,SQL分页是一种非常常见的查询技术,它允许我们从大量数据中按需获取一部分结果,而不是一次性加载所有记录。这对于提高用户体验和优化系统性能至关重要,尤其是在处理大数据量的Web应用中。本实践...
在Oracle分页中,Hibernate通过HQL(Hibernate Query Language)或SQL来执行查询,并支持Criteria、Query和Session的Criteria API等方式实现分页。通常,我们会通过设置`setFirstResult`和`setMaxResults`方法来指定...