Oracle分页查询格式（十一）

最新推荐文章于 2024-07-26 14:20:58 发布
最新推荐文章于 2024-07-26 14:20:58 发布
阅读量77 收藏
点赞数
文章标签： 数据库 数据结构与算法

Oracle从8i推出了分析函数，9i中分析函数进一步发展，而且已经很稳定了。

利用分析函数的功能，一样可以实现分页的功能。

首先还是构造一张大表，作为分页查询的测试表：

SQL> CREATE TABLE T AS
2 SELECT /*+ NO_MERGE(A) NO_MERGE(B) */ *
3 FROM DBA_SEQUENCES A, DBA_OBJECTS B;

表已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
4584838

SQL> SET TIMING ON
SQL> SET AUTOT 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
8 ORDER BY OBJECT_NAME
9 )
10 WHERE ROWNUM <= 20
11 )
12 WHERE RN >= 11;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
17869 /1005bd30_LnkdConstant
17870 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant

已选择10行。

已用时间: 00: 00: 02.00

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=20 Bytes=1840)
1 0 VIEW (Cost=34093 Card=20 Bytes=1840)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=34093 Card=4584838 Bytes=362202202)
4 3 SORT (ORDER BY STOPKEY) (Cost=34093 Card=4584838 Bytes=132960302)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
96666 consistent gets
56154 physical reads
0 redo size
543 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 OBJECT_NAME, OBJECT_ID,
5 ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
6 FROM T
7 )
8 WHERE RN BETWEEN 11 AND 20;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
17870 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17870 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant

已选择10行。

已用时间: 00: 00: 02.09

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)
1 0 VIEW (Cost=34093 Card=4584838 Bytes=421805096)
2 1 WINDOW (SORT PUSHED RANK) (Cost=34093 Card=4584838 Bytes=132960302)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
96666 consistent gets
56165 physical reads
0 redo size
548 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和利用分析函数的分页SQL，这里面有不少有意思的差别。

首先二者得到的结果是不同的。不过这其实并没有关系，因为这本身就是两个不同的SQL，而且二者的结果都是符合查询条件的。导致这个现象的原因有两个，一个是两个SQL使用的排序算法不同，另一个是排序字段不唯一。解决这个问题其实也很简单，只需要保证排序结果唯一就可以了。

如果从性能上讲，二者没有本质的差别，由于ORDER BY的列上没有索引，因此两个SQL都必须对表进行FULL TABLE SCAN。关键在于这两个SQL在能否体现出分页的特点——STOP KEY。由于用户当前执行的查询可能只是返回前100条记录，那么分页SQL就没有必要对所有的数据进行完全的排序，只需要找到最小或最大的100条记录，就可以返回结果了。在两个SQL中，都能实现这个功能，其中标准分页采用的是：SORT (ORDER BY STOPKEY)；而分析函数使用的是：WINDOW (SORT PUSHED RANK)。只要具有将STOP KEY推入到排序操作内的功能，就基本上满足分页条件。

从SQL的结构上，标准分页需要3层嵌套，而分析函数只需要2层。但是并不意味分析函数减少了一层嵌套，效率就一定高于标准分页，事实上，多次测试显示，标准分页似乎还要略快一点。与错误的分页写法相比，这两个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 ORDER BY OBJECT_NAME
9 )
10 )
11 WHERE RN BETWEEN 11 AND 20;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
17870 /1005bd30_LnkdConstant
17870 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17870 /1005bd30_LnkdConstant
17870 /1005bd30_LnkdConstant
17870 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17869 /1005bd30_LnkdConstant
17870 /1005bd30_LnkdConstant

已选择10行。

已用时间: 00: 00: 13.18

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)
1 0 VIEW (Cost=34093 Card=4584838 Bytes=421805096)
2 1 COUNT
3 2 VIEW (Cost=34093 Card=4584838 Bytes=362202202)
4 3 SORT (ORDER BY) (Cost=34093 Card=4584838 Bytes=132960302)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)


Statistics
----------------------------------------------------------
0 recursive calls
50 db block gets
96666 consistent gets
96806 physical reads
0 redo size
553 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

上面就是采用了错误的分页写法，使得Oracle排序了所有的数据，所需的时间是正确分页写法的6倍以上。

最后将分页设置到最后的部分，检查两个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 ORDER BY OBJECT_NAME
9 )
10 WHERE ROWNUM <= 4584820
11 )
12 WHERE RN >= 4584811;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T

已选择10行。

已用时间: 00: 00: 12.92

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584820 Bytes=421803440)
1 0 VIEW (Cost=34093 Card=4584820 Bytes=421803440)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=34093 Card=4584838 Bytes=362202202)
4 3 SORT (ORDER BY STOPKEY) (Cost=34093 Card=4584838 Bytes=132960302)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)


Statistics
----------------------------------------------------------
0 recursive calls
50 db block gets
96666 consistent gets
96810 physical reads
0 redo size
533 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 OBJECT_NAME, OBJECT_ID,
5 ROW_NUMBER() OVER(ORDER BY OBJECT_NAME) RN
6 FROM T
7 )
8 WHERE RN BETWEEN 4584811 AND 4584820;

OBJECT_ID OBJECT_NAME
---------- ------------------------------
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T
28423 xml-extension-type24_T

已选择10行。

已用时间: 00: 00: 18.78

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT ptimizer=CHOOSE (Cost=34093 Card=4584838 Bytes=421805096)
1 0 VIEW (Cost=34093 Card=4584838 Bytes=421805096)
2 1 WINDOW (SORT PUSHED RANK) (Cost=34093 Card=4584838 Bytes=132960302)
3 2 TABLE ACCESS (FULL) OF 'T' (Cost=9297 Card=4584838 Bytes=132960302)


Statistics
----------------------------------------------------------
0 recursive calls
48 db block gets
96666 consistent gets
76497 physical reads
0 redo size
533 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

由于表中一个只有4584838条记录，因此这个分页基本上是最后一两页了，这可以说是一个极端的例子了，比较二者的效率发现，标准分页效率这次要比分析函数高将近50%。虽然例子比较极端，不过也可以说明一些问题。首先，通过ORDER BY加ROWNUM方式的排序算法，应该和分析函数的不同。其次，分析函数的功能很强大，ORDER BY只是其中一个功能，分析函数还能完成分区和窗口操作等更加复杂的操作，因此效率比单纯的排序要低也无可厚非。当然，为了和前面的测试保持版本的一致性，测试在920上进行，不排除10g或11g对分析函数的分页做了进一步的优化。

最后还需要提一句，采用分析函数的方法，不能没有ORDER BY语句，而标准分页方式可以。当然没有ORDER BY能不能算一个真正的分页，就是见仁见智的事情了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/14270146/viewspace-669366/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/14270146/viewspace-669366/

cuanzhong6087
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
十一、分页查询(一)
荒的博客
09-30 143
之前的功能已经能够跳转到用户的主页面，显示用户列表了，现在处理列表中的分页问题 准备工作：在项目atcrowdfunding-common中的实体类User添加新属性email 在数据库中添加该字段，并且添加测试数据如下： 由于这部分是后台管理，所以代码写在atcrowdfunding-manager项目中 结构如下： 控制器UserController： package...
oracle分页查询语句
wonder1053的专栏
02-17 1539
Oracle分页查询格式： 　　以下是代码： 　　SELECT * FROM 　　( 　　SELECT A.*, ROWNUM RN 　　FROM (SELECT * FROM 表名) A 　　WHERE ROWNUM 　　) 　　WHERE RN >= 21 　　其中最内层的查询SELECT * FROM 表名表示不进行翻页的原始查询语句。ROWNUM = 21控制分页查询的
Oracle分页查询格式
zgmzyr的专栏
02-26 2412
转载于： Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。 这篇介绍分析函数用于分页，且排序字段上包含索引的情况。 Oracle分页查询格式（一）：http://yangtingkun.itpub.net/post/468/100278 Oracle分页查询格式（二）：http://yangtingkun.itpub.net/post/468/101703
Oracle9i 分页存储过程
weixin_34318326的博客
09-16 68
Codecreate or replace package pkg_Pager is -- Author : aweige -- Created : 2008-1-26 19:54:19 -- Purpose : 使用oracle包返回数据集 -- Public type declarations type refcurosr is ref cursor; procedure p...
oracle分页查询sql
01-15
### Oracle分页查询详解 #### 一、分页查询的重要性 在数据库操作中，分页查询是一项非常重要的技术。尤其当处理大量数据时，一次性获取所有数据不仅会消耗大量的网络带宽，还可能导致前端页面加载缓慢，用户体验...
Oracle分页查询性能优化代码详解
09-09
本文将深入探讨Oracle分页查询的性能优化，并提供相关的代码示例。 首先，我们来看两种常见的分页查询方法： 1. **子查询与行号（ROWNUM）结合**： ```sql SELECT * FROM ( SELECT rownum RN, t.* FROM table t...
Oracle分页查询的实例详解
12-15
以下是对Oracle分页查询的实例详解： 1. **Oracle分页查询基础** Oracle分页查询通常涉及ROWNUM伪列的使用。ROWNUM在查询执行过程中为每一行分配一个唯一的数字，从1开始递增。在以下两个示例中，我们可以看到两种...
简单实例解释Oracle分页查询
01-21
什么是分页查询 分页查询就是把query到的结果集按页显示。比如一个结果集有1W行，每页按100条数据库。而你获取了第2页的结果集。 为什么要分页查询 如果一个结果集有几十W行，那么在一个页面上显示肯定显示不完。...
Oracle分页查询语句
jackpk的专栏
03-24 1902
Oracle分页查询语句 url：http://yangtingkun.itpub.net/post/468/100278 Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。分页查询格式：SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME) A WHERE ROWNUM )W
Oracle分页查询格式（十二）
cuanzhong6087的博客
07-25 85
上一篇文章已经介绍了利用分析函数来进行分页的方法，总的来说，除非分页到了最后部分，利用分析函数进行分页的效率还是可以的。 综合来说，除了SQL嵌套可以少写一层外，并没有什么特别的优点来代替标准分页函数的写法。 不过上一篇测试...
oracle分页怎么查询,oracle简单查询之分页查询
weixin_32059007的博客
04-02 2811
分页查询1、为什么需要分页？​ 当数据库的数据过的时候，客服端无法一次性显示所有数据,例如我们数据库表里有十万条数据,如果一下加载,查询的速度慢,用户体验差,而且用户也不可能一次性读完这个十万条数据2、分页技术分类物理分页(推荐)在数据库执行查询时(实现分页查询)，查询需要的数据依赖数据库SQL语句，属于后台分页逻辑分页先查询所有数据到内存，再从内存截取需要数据采用程序内部逻辑，属于前台分页3、O...
Oracle分页查询
温柔一刀
08-29 213
分页查询格式： SELECT * FROM ( SELECT A. * , ROWNUM RN FROM ( 真正的查询 ) A WHERE ROWNUM &lt;= ...
Oracle与MySQL的分页查询sql语句格式
前方一片光明
03-13 8532
Oracle Oracle使用rownum进行分页，rownum是从1开始的： select * from (select rownum as rn, a.* from table_name a where rownum <= pageIndex * pageSize) where rn > (pageIndex - 1) * pageSize 其中，pag...
图数据库查询与操作指南-以Nebula图数据库为例
DZSpace，专注于计算机科学与技术领域的技术博主，致力于分享实用知识与技巧，帮助读者快速掌握技术要点，共同探索计算机世界的无限可能。欢迎访问DZSpace的博客，一起学习进步！
07-26 303
图数据库查询与操作指南-以Nebula图数据库为例
ODBC连接达梦数据库
qq_55187735的博客
07-24 290
用户执行，因为环境变量配置在。
SQL基础入门：解锁数据库管理的钥匙
最新发布
WayneYalejk的博客
07-26 448
在数字化时代，数据已成为企业最宝贵的资产之一。为了高效地存储、查询和管理这些数据，SQL（Structured Query Language，结构化查询语言）应运而生。SQL不仅是数据库管理员的必备技能，也是数据分析师、开发人员等多个领域从业者的重要工具。本文将带您踏入SQL的世界，从基础概念到实际应用，一步步解锁数据库管理的钥匙。强调SQL在数据处理和分析中的重要性，鼓励读者通过实践不断深化对SQL的理解和应用。
sqlalchemy使用json_unquote函数的mysql like查询
ithongchou的博客
07-23 1374
为您自己的数据库连接信息，并根据实际表结构修改模型类。函数查询MySQL JSON字段可以通过使用。下面是一个示例，假设有一个名为。请确保替换示例代码中的数据库连接字符串(在SQLAlchemy中使用。的表，其中包含一个名为。
mysql的存储过程：
m0_71178834的博客
07-25 431
完成特点功能的sql语句的集合。把定义好的sql集合在一个特定的sql的函数当中每次执行调用函数即可，还可以实现传参的调用。
优化Oracle分页查询策略：高效与效率对比
本文将重点讲解Oracle分页查询的基本格式、效率优化以及在不同情况下的使用策略。 首先，Oracle的分页查询通常采用嵌套查询的方式实现，核心格式如下： ```sql SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值