`
you_java
  • 浏览: 96289 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

oracle 分页专题

阅读更多

Oracle分页查询语句(一)

 

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

 

Oracle分页查询语句(二)

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

Oracle分页查询语句(一):http://yangtingkun.itpub.net/post/468/100278

 

 


这篇文章用几个例子来说明分页查询的效率。首先构造一个比较大的表作为测试表:

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS, DBA_SEQUENCES;

表已创建。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
457992

首先比较两种分页方法的区别:

SQL> SET AUTOT ON
SQL> COL OBJECT_NAME FORMAT A30
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SELECT OBJECT_ID, OBJECT_NAME 
2 FROM 
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME 
5 FROM 
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 )
9 )
10 WHERE RN BETWEEN 11 AND 20;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
5807 ALL_APPLY_PROGRESS
1769 ALL_ARGUMENTS
2085 ALL_ASSOCIATIONS
4997 ALL_AUDIT_POLICIES
4005 ALL_BASE_TABLE_MVIEWS
5753 ALL_CAPTURE
5757 ALL_CAPTURE_PARAMETERS
5761 ALL_CAPTURE_PREPARED_DATABASE
5765 ALL_CAPTURE_PREPARED_SCHEMAS
5769 ALL_CAPTURE_PREPARED_TABLES

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457992 Bytes=42135264)
1 0 VIEW (Cost=864 Card=457992 Bytes=42135264)
2 1 COUNT
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8979 consistent gets
7422 physical reads
0 redo size
758 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME 
2 FROM 
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME 
5 FROM 
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
5807 ALL_APPLY_PROGRESS
1769 ALL_ARGUMENTS
2085 ALL_ASSOCIATIONS
4997 ALL_AUDIT_POLICIES
4005 ALL_BASE_TABLE_MVIEWS
5753 ALL_CAPTURE
5757 ALL_CAPTURE_PARAMETERS
5761 ALL_CAPTURE_PREPARED_DATABASE
5765 ALL_CAPTURE_PREPARED_SCHEMAS
5769 ALL_CAPTURE_PREPARED_TABLES

已选择10行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=20 Bytes=1840)
1 0 VIEW (Cost=864 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
758 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

二者执行效率相差很大,一个需要8000多逻辑读,而另一个只需要5个逻辑读。观察二者的执行计划可以发现,两个执行计划唯一的区别就是第二个查询在COUNT这步使用了STOPKEY,也就是说,Oracle将ROWNUM <= 20推入到查询内层,当符合查询的条件的记录达到STOPKEY的值,则Oracle结束查询。

因此,可以预见,采用第二种方式,在翻页的开始部分查询速度很快,越到后面,效率越低,当翻到最后一页,效率应该和第一种方式接近。

SQL> SELECT OBJECT_ID, OBJECT_NAME 
2 FROM 
3 (
4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME 
5 FROM 
6 (
7 SELECT OBJECT_ID, OBJECT_NAME FROM T
8 )
9 WHERE ROWNUM <= 457990
10 )
11 WHERE RN >= 457980;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
7128 XCF_I_HANDLE_STATUS
7126 XCF_P
7127 XCF_U1
7142 XDF
7145 XDF_I_DF_KEY
7146 XDF_I_HANDLE_STATUS
7143 XDF_P
7144 XDF_U1
TEST.YANGTINGKUN
TEST4.YANGTINGKUN
YANGTK.YANGTINGKUN

已选择11行。


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457990 Bytes=42135080)
1 0 VIEW (Cost=864 Card=457990 Bytes=42135080)
2 1 COUNT (STOPKEY)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
8979 consistent gets
7423 physical reads
0 redo size
680 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed

 

注:来自http://yangtingkun.itpub.net/post/468/101703;作者:yangtingkun

分享到:
评论

相关推荐

    Oracle教程

    1. "Oracle数据库DBA专题技术精粹.pdf":这本书籍通常会深入探讨Oracle数据库管理员(DBA)所需的核心技能。DBA的工作包括数据库设计、安装配置、性能调优、安全管理和故障排除等。它可能会涵盖如何创建和管理表空间...

    Oracle XML Publisher技巧集锦.docx

    - **分页规则**:设定何时进行分页,如何处理跨页数据。 - **页码编号**:自动或手动设置页码。 ##### 2.5 使用多媒体元素 - **插入图片**:支持多种格式的图像文件。 - **音频视频嵌入**:在报表中嵌入音频或视频...

    Oracle XML Publisher技巧集锦.doc

    在处理多页报表时,经常会遇到诸如页眉页脚、分页、跨页表头等问题。XML Publisher提供了相应的解决策略,确保报表在多页显示时依然保持清晰和整洁。 #### 2.5 使用多媒体元素 多媒体元素,如图片、图表和图形,...

    Solaris常用命令专题

    在IT领域,操作系统是计算机硬件与用户之间的重要桥梁,而Solaris则是Sun Microsystems(现已被Oracle公司收购)开发的一款强大的Unix操作系统。对于系统管理员和开发者来说,熟练掌握Solaris的常用命令是日常工作的...

    asp.net的几个经典代码

    以下是对标题和描述中涉及的知识点的详细解析,以及每个压缩包子文件所涵盖的专题: 1. **GridView使用大全.cs** - GridView是ASP.NET中用于展示数据的控件,它能够方便地绑定到数据源并实现数据的编辑、排序、分页...

    深入理解计算机系统(csapp)

    - **Oracle专题**:涉及Oracle数据库管理系统的管理和维护技巧。 - **其他相关技术**:除了上述主题外,还覆盖了如Python、Java、C语言、OpenStack等编程语言和技术的使用案例,以及MySQL、Apache、Nginx等服务器...

    精品专题(2021-2022年收藏)Java程序设计与JSP考核大纲.doc

    第七章涉及数据库操作,主要以SQL Server 2000为例,学习JDBC的使用,包括数据库连接、查询、更新、添加和删除操作,以及预处理语句和CachedRowSet分页显示技术。此外,还概述了连接Oracle和MySQL数据库的方法。 第...

    大数据功能模块概要设计-V1.1Word版.docx

    - **数据库**:关系型数据库如Oracle、MySQL,以及NoSQL数据库如MongoDB、Hadoop、Hive、HBase、Memcached、Redis,Oracle要求10g以上版本。 - **开发框架**:Java服务端推荐Struts2+Spring3+iBatis或MyBatis,...

    大数据功能模块概要设计_V1.1.pdf

    通用共享数据专题库则用于存储特定主题的数据,报表应用则用于数据可视化和分析,指标管理则是对数据进行度量和评估的工具。应用层的门户系统提供了统一的访问入口,数据交换、整合、加工组件则负责数据的传输、清洗...

    门户网站建设招标方案..doc

    - **站点规模**:包含1个门户主站、80-100个门户子站、3-6个专题网站及3个重点行业网站。 - **权限管理**:由主站管理员统一管理,根据权限、角色、栏目进行细分。 - **样式定制**:提供多种主站风格供用户选择;子...

    PHP ADODB生成下拉列表框功能示例

    在PHP编程中,ADODB(Active Record Database Objects)...如果你对PHP数据库操作有更深入的需求,可以查看相关的专题文章,例如PHP PDO、MySQLi、Oracle、MongoDB、Redis等数据库操作技巧,以进一步提升你的编程技能。

    thinkphp3.2离线文档

    - 支持Mysql、MsSQL、PgSQL、Sqlite、Oracle、Ibase、Mongo及PDO等数据库。 - 需要PHP5.3或更高版本。 - **目录结构**: - **www**: Web部署目录。 - **index.php**: 入口文件。 - **Application**: 应用模块...

Global site tag (gtag.js) - Google Analytics