- 浏览: 5719 次
最新评论
【转载】 yangtingkun: Oracle分页查询语句(七) 2011-01-12 16:47:35
分类: Oracle
在文章的最后看一下ORDER BY STOPKEY和ORDER BY在翻页查询的最后几页的性能差异:
SQL> CREATE TABLE T AS SELECT A.* FROM DBA_OBJECTS A, DBA_USERS B, TAB;
表已创建。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
458064
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
SQL> SET AUTOT TRACE
SQL> SET TIMING ON
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 ORDER BY TIMESTAMP
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;
已选择10行。
已用时间: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=20 Bytes=1840)
1 0 VIEW (Cost=13888 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5579 consistent gets
0 physical reads
0 redo size
694 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 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 ORDER BY TIMESTAMP
8 )
9 )
10 WHERE RN BETWEEN 11 AND 20;
已选择10行。
已用时间: 00: 00: 09.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)
2 1 COUNT
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)
Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7935 physical reads
0 redo size
689 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)
1 sorts (disk)
10 rows processed
对于翻页查询的前几页,采用ORDER BY STOPKEY的方式比ORDER BY性能上有很大的优势,那么对于分页查询的最后几页,ORDER BY STOPKEY是否和其他分页查询技术一样,性能比普通方式还要低很多:
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 ORDER BY TIMESTAMP
8 )
9 WHERE ROWNUM <= 458060
10 )
11 WHERE RN >= 458051;
已选择10行。
已用时间: 00: 00: 09.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458060 Bytes=42141520)
1 0 VIEW (Cost=13888 Card=458060 Bytes=42141520)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)
Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7933 physical reads
0 redo size
667 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)
1 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 ORDER BY TIMESTAMP
8 )
9 )
10 WHERE RN BETWEEN 458051 AND 458060;
已选择10行。
已用时间: 00: 00: 10.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)
2 1 COUNT
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)
Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7935 physical reads
0 redo size
649 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)
1 sorts (disk)
10 rows processed
出乎意料的是,虽然ORDER BY STOPKEY的方式在分页查询的最后几页性能也有明显的下降,但是在和普通的ORDER BY相比,无论从逻辑读、物理读还是从执行时间上看,二者都属于一个数量级上的。
看来ORDER BY STOPKEY排序方式,在STOPKEY接近排序总量的时候也不会有明显的性能下降。
[@more@]
分类: Oracle
在文章的最后看一下ORDER BY STOPKEY和ORDER BY在翻页查询的最后几页的性能差异:
SQL> CREATE TABLE T AS SELECT A.* FROM DBA_OBJECTS A, DBA_USERS B, TAB;
表已创建。
SQL> SELECT COUNT(*) FROM T;
COUNT(*)
----------
458064
SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')
PL/SQL 过程已成功完成。
SQL> SET AUTOT TRACE
SQL> SET TIMING ON
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 ORDER BY TIMESTAMP
8 )
9 WHERE ROWNUM <= 20
10 )
11 WHERE RN >= 11;
已选择10行。
已用时间: 00: 00: 00.03
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=20 Bytes=1840)
1 0 VIEW (Cost=13888 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5579 consistent gets
0 physical reads
0 redo size
694 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 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 ORDER BY TIMESTAMP
8 )
9 )
10 WHERE RN BETWEEN 11 AND 20;
已选择10行。
已用时间: 00: 00: 09.05
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)
2 1 COUNT
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)
Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7935 physical reads
0 redo size
689 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)
1 sorts (disk)
10 rows processed
对于翻页查询的前几页,采用ORDER BY STOPKEY的方式比ORDER BY性能上有很大的优势,那么对于分页查询的最后几页,ORDER BY STOPKEY是否和其他分页查询技术一样,性能比普通方式还要低很多:
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 ORDER BY TIMESTAMP
8 )
9 WHERE ROWNUM <= 458060
10 )
11 WHERE RN >= 458051;
已选择10行。
已用时间: 00: 00: 09.07
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458060 Bytes=42141520)
1 0 VIEW (Cost=13888 Card=458060 Bytes=42141520)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)
Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7933 physical reads
0 redo size
667 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)
1 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 ORDER BY TIMESTAMP
8 )
9 )
10 WHERE RN BETWEEN 458051 AND 458060;
已选择10行。
已用时间: 00: 00: 10.01
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)
1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)
2 1 COUNT
3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)
4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)
Statistics
----------------------------------------------------------
0 recursive calls
41 db block gets
5579 consistent gets
7935 physical reads
0 redo size
649 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)
1 sorts (disk)
10 rows processed
出乎意料的是,虽然ORDER BY STOPKEY的方式在分页查询的最后几页性能也有明显的下降,但是在和普通的ORDER BY相比,无论从逻辑读、物理读还是从执行时间上看,二者都属于一个数量级上的。
看来ORDER BY STOPKEY排序方式,在STOPKEY接近排序总量的时候也不会有明显的性能下降。
[@more@]
发表评论
-
ORCALE 密码修改
2015-09-24 09:43 285运行 cmd sqlplus/nolog c ... -
oracle证书
2015-09-24 09:41 0https://localhost:1158/em/con ... -
orcale个人总结
2015-07-02 10:08 0orcale 密码找回方法----------------- ... -
orcale配置中遇到的问题
2015-07-02 09:54 01 oracle登录错误:ORA-28000: ... -
orcale安装
2015-07-02 09:17 0Oracle11g----Win7 32位安装图例 h ... -
Oracle分页查询格式(十一)
2015-07-24 12:11 315Oracle分页查询格式(十一) 2009-06-05 22: ... -
Oracle分页查询格式(八)
2015-07-31 10:19 316Oracle分页查询格式(八) 2006-10-28 00:0 ... -
Oracle分页查询语句(六)
2015-07-31 10:18 419Oracle分页查询语句(六) 2006-06-03 00:0 ... -
Oracle分页查询语句(五)
2015-07-31 10:18 262【转载】 yangtingkun: Oracle分页查询语句( ... -
Oracle分页查询语句(四)
2015-07-31 10:18 349【转载】 yangtingkun: Oracle分页查询语句( ... -
Oracle分页查询语句(三)
2015-07-24 12:12 270Oracle分页查询语句(三) 2006-05-27 00:0 ... -
Oracle分页查询语句(二)
2015-07-24 12:12 318Oracle分页查询语句(二) 2006-05-23 00:0 ... -
Oracle分页查询语句(一)
2015-07-24 12:12 338【转载】 yangtingkun: Oracl ...
相关推荐
msql和oracle分页查询语句笔记
Oracle的分页查询语句以及Oracle分页的存储过程
Oracle分页查询语句的学习和示例演练
oracle分页查询语句sql
Oracle的分页查询语句
一些常用到的在oracle中的sql语句,也是我在网上找到的,分享给大家
网上搜集的oracle分页查询语句和性能分析
select * from (select a.*,rownum rn from (select * from tablename) a where rownum) where rn>2
用ORACLE的SQL语句实现多栏分页输出.RTF
oracle认证详解Oracle的几种分页查询语句! 值得下载看看!资源免费,大家分享!!
Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用
NULL 博文链接:https://yan578351314.iteye.com/blog/911307
Oracle中实现分页查询的SQL命令 //curPage是当前页面,pageCount是每页显示行数 //rownum是伪列,相当于表中每一列的标识列(可以理解为行号),需要显式的提取出来并取一个别名
高效多条件 Oracle 分页存储过程 ,快速分页
几条常见的数据库分页SQL 语句,针对oracle,sqlserver,mysql三种常见数据库的分页显示。
环境: Oracle 9i 本例子使用简单的SQL语句实现Oracle数据库的分页动作。它没有使用PL/SQL语句(比如什么程序包、游标等),只是使用通用的、简单的SQL实现了...分页公式注释非常详细,是非常实用的Oracle分页语句!!
对于数据库中表的数据的 Web 显示,如果没有展示顺序的需要,而且因为满足条件的记录如此之多,就不得不对数据进行分页处理。常常用户并不是对所有数据都感兴趣的,或者大部分情况下,他们...看似相似的分页语句,在响
写好oracle的连接字符串和查询语句,调用程序中的方法可以很方便的实现分页功能。该方法中,将参数连接字符串,查询的sql语句,指定每页显示多少行,调用成功后,会返回页数,行数,还有查询的结果数据集。 使用...
Oracle 10g 数据库海量数据分页查询优化 本文主要介绍 Oracle 10g 数据库中的一种优化的海量数据分页查询解决方案。该方案通过分析传统分页查询技术的不足,融合了多种技术,包括数据库优化策略、SQL 语句优化、...
mysql和oracle的分页语句