（Oracle）SQL优化技巧（一）：分页查询

已于 2024-04-07 09:13:48 修改
阅读量2k 收藏 19
点赞数 31
分类专栏： Oracle之SQL优化 文章标签： oracle sql dba 数据库
于 2024-04-06 22:51:49 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_58872140/article/details/137423591
版权

目录

分页查询框架

分页查询注意事项 

有序/无序分页

事务带来的影响

分页查询与索引

排序字段索引实验

组合索引实验

利用ROWNUM进行分页查询的方法在各版本都是适用的，11g,12c,19c都可以使用该方法哦。在分享分页查询方法之前，需要先聊下ROWNUM，这玩意儿要是聊不清楚，那这个方法就不太好理解。

  • 伪列

ROWNUM是Oracle的一个伪列，并不真实存在于表结构中。

  • 行号

ROWNUM作用记录是返回结果集中的每一行的行号，是在查询结果返回之后才计算的。

在了解ROWNUM以上两个特性之后，可以开始分享根据ROWNUM进行的分页查询方法了。 

分页查询框架

SELECT *
FROM(SELECT *  
      FROM  (SELECT 
            sp.*,
            ROWNUM rn
            FROM (/*需要分页的SQL*/) sp)
            WHERE ROWNUM <= x)
WHERE rn >=y

在上述代码中

x表示查询的结束行

y表示查询的起始行

分页查询注意事项 

有序/无序分页

Attention Please！！！

下面将是一大段文字描述，因为我在刚接触分页查询的时候吃了不少亏，也有许多不理解的地方，现在我把我的浅薄理解写出来，供大家参考！

如果您时间宝贵与紧张，可以不看下面的描述，只需要记住一点：

进行分页查询优化的目标SQL需要根据实际场景看是否进行排序！

分页查询，顾名思义即为将表中的数据分成若干页，且指定每页行数进行展示；目的就是为了避免目标表中的数据量太大，而一次性查询全部引起的查询效率低下。大家可以想象一下，我们在阅读一本新书的时候，是用什么样的方式阅读呢？正常人肯定是从第1页开始，一页一页的往后按照顺序进行阅读。Oracle如果拟人化肯定也是个正常人，因为它做的一切都是合乎理性的；它也会从第一页开始按照顺序往后阅读。

那么重点就来了：“顺序”。

在做分页查询的时候，是需要保证进行分页查询的目标SQL要有一个合理的排序。前文已经叙述过ROWNUM是在查询返回后计算的一个行号，如果查询的结果集本身是排序是混乱的，那么具体每页展示的数据就不会是我们期待的一个结果。

用我们在学习Oracle时的一个老朋友scott用户举个例子，scott用户下有张EMP表，表里有各个职员的薪水。在对 “SELECT * FROM EMP” 这个SQL进行分页查询优化时，如果按照薪水从高到低的需要去查看这些数据，那么理想分页情况就应该是第1页展示薪水排前N名的职工信息，第2页展示薪水排第N+1~2N名的职工信息，以此类推。但如果不对salary字段进行降序查询的话，是达不到期待效果的。

例如要查询公司薪水排名6~10的员工信息，以scott.emp表为例子进行查询，那么分页查询SQL代码如下。

SELECT *
FROM(SELECT *  
      FROM  (SELECT 
            sp.*,
            ROWNUM rn
            FROM (SELECT * FROM emp ORDER BY sal DESC) sp)
            WHERE ROWNUM <= 10)
WHERE rn >=6

当然了，如果您觉得无序分页对您的查询没有什么影响的话，也就没有必要进行排序查询了；这个肯定还是要根据实际场景来决定。

事务带来的影响

想象一下，您在阅读的是一本电子书，您已经阅读完当前页了，就开始往后翻，但是这个叼电子书系统突然抽风把您已经阅读过的前面页数的内容更改了，这个时候您读到的信息就不一定是准确的了。

Oracle也一样，可能每时每刻都在发生着事务；这些事务都会对正在进行分页查询的SQL结果集造成影响，所以在进行分页查询时需要考虑数据的一致性。有些分页查询的场景是不需要考虑事务带来的数据变化；但有的场景是需要的，就比如说做ETL的，在同步数据到数据仓库的时候，就需要考虑这些事务带来的影响。

分页查询与索引

这里所指的分页查询是有序分页。

如果您的查询SQL有进行排序的话，那么需要在进行排序的字段上建立索引哦。为什么呢？因为索引是已经进行过排序的，可以利用索引的这个特性来进一步优化分页语句。

下面做个小实验哦。（我下面对实验分个三级标题哦，可以让整篇文章看着更清晰些。同时也感觉我的排版能力菜的一批！）

排序字段索引实验

  • 先建立一张测试表
create table HR.spage_0406 as select * from dba_objects
  • 进行分析查询改写
SELECT *
FROM(SELECT *  
      FROM  (SELECT 
            sp.*,
            ROWNUM rn
            FROM (select * from HR.spage_0406 order by object_id) sp)
            WHERE ROWNUM <= 10)
WHERE rn >=1
  • 查看当前分页查询执行计划

可以发现现在走的是全表扫描，且A-ROWS是72695

SQL_ID  9xkcnduur1d6p, child number 0
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM 
HR.SPAGE_0406 ORDER BY OBJECT_ID) SP) WHERE ROWNUM <= 10) WHERE RN >=1
 
Plan hash value: 2601037360
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |      1 |        |       |       |  2541 (100)|          |     10 |00:00:00.07 |    1416 |       |       |          |
|*  1 |  VIEW                   |            |      1 |     10 |  4940 |       |  2541   (1)| 00:00:01 |     10 |00:00:00.07 |    1416 |       |       |          |
|*  2 |   COUNT STOPKEY         |            |      1 |        |       |       |            |          |     10 |00:00:00.07 |    1416 |       |       |          |
|   3 |    VIEW                 |            |      1 |  72695 |    34M|       |  2541   (1)| 00:00:01 |     10 |00:00:00.07 |    1416 |       |       |          |
|   4 |     COUNT               |            |      1 |        |       |       |            |          |     10 |00:00:00.07 |    1416 |       |       |          |
|   5 |      VIEW               |            |      1 |  72695 |    33M|       |  2541   (1)| 00:00:01 |     10 |00:00:00.07 |    1416 |       |       |          |
|   6 |       SORT ORDER BY     |            |      1 |  72695 |  9370K|    13M|  2541   (1)| 00:00:01 |     10 |00:00:00.07 |    1416 |    14M|  1431K|   12M (0)|
|   7 |        TABLE ACCESS FULL| SPAGE_0406 |      1 |  72695 |  9370K|       |   395   (1)| 00:00:01 |  72695 |00:00:00.01 |    1416 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
  • 给排序字段添加索引
create index IDX_SPAGE_OBJECTID on HR.spage_0406(object_id,0)
  • 再次查看分页查询执行计划

可以发现现在走的是索引全扫描，且A-ROWS是10。现在这张表还是不够大，还是体现不出来这种优化方式的优势，越大的表越能实际感受的到它的优势。

SQL_ID  9xkcnduur1d6p, child number 0
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM 
HR.SPAGE_0406 ORDER BY OBJECT_ID) SP) WHERE ROWNUM <= 10) WHERE RN >=1
 
Plan hash value: 1210249890
 
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name               | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                    |      1 |        |       |  1762 (100)|          |     10 |00:00:00.01 |       3 |
|*  1 |  VIEW                            |                    |      1 |     10 |  4940 |  1762   (1)| 00:00:01 |     10 |00:00:00.01 |       3 |
|*  2 |   COUNT STOPKEY                  |                    |      1 |        |       |            |          |     10 |00:00:00.01 |       3 |
|   3 |    VIEW                          |                    |      1 |  72695 |    34M|  1762   (1)| 00:00:01 |     10 |00:00:00.01 |       3 |
|   4 |     COUNT                        |                    |      1 |        |       |            |          |     10 |00:00:00.01 |       3 |
|   5 |      VIEW                        |                    |      1 |  72695 |    33M|  1762   (1)| 00:00:01 |     10 |00:00:00.01 |       3 |
|   6 |       TABLE ACCESS BY INDEX ROWID| SPAGE_0406         |      1 |  72695 |  9370K|  1762   (1)| 00:00:01 |     10 |00:00:00.01 |       3 |
|   7 |        INDEX FULL SCAN           | IDX_SPAGE_OBJECTID |      1 |  72695 |       |   182   (0)| 00:00:01 |     10 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------------------------------------

组合索引实验

那么分页查询与索引的故事到这里就结束了吗？当然不是啦，还有还有呢。请大家耐心看下面的叙述哦！

上面的查询是没有谓词过滤的，也就是WHERE条件。如果查询中有谓词条件，大家是可以考虑创建联合索引；将谓词字段与排序字段放在一起创建组合索引，且尽量将排序字段作为组合索引的前导列，也就是创建组合索引时的一个字段。

例如下面这个分页查询，加进了谓词过滤，执行计划立马就变差了。

SELECT *
FROM(SELECT *  
      FROM  (SELECT 
            sp.*,
            ROWNUM rn
            FROM (select * from HR.spage_0406 where owner='SYS' order by object_id) sp)
            WHERE ROWNUM <= 10)
WHERE rn >=1
SQL_ID  67hjvw5r90c9g, child number 1
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM 
HR.SPAGE_0406 WHERE OWNER='SYS' ORDER BY OBJECT_ID) SP) WHERE ROWNUM <= 
10) WHERE RN >=1
 
Plan hash value: 2601037360
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name       | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |      1 |        |       |       |   483 (100)|          |     10 |00:00:00.04 |    1416 |       |       |          |
|*  1 |  VIEW                   |            |      1 |     10 |  4940 |       |   483   (1)| 00:00:01 |     10 |00:00:00.04 |    1416 |       |       |          |
|*  2 |   COUNT STOPKEY         |            |      1 |        |       |       |            |          |     10 |00:00:00.04 |    1416 |       |       |          |
|   3 |    VIEW                 |            |      1 |   2908 |  1402K|       |   483   (1)| 00:00:01 |     10 |00:00:00.04 |    1416 |       |       |          |
|   4 |     COUNT               |            |      1 |        |       |       |            |          |     10 |00:00:00.04 |    1416 |       |       |          |
|   5 |      VIEW               |            |      1 |   2908 |  1365K|       |   483   (1)| 00:00:01 |     10 |00:00:00.04 |    1416 |       |       |          |
|   6 |       SORT ORDER BY     |            |      1 |   2908 |   374K|   552K|   483   (1)| 00:00:01 |     10 |00:00:00.04 |    1416 |    10M|  1258K| 9559K (0)|
|*  7 |        TABLE ACCESS FULL| SPAGE_0406 |      1 |   2908 |   374K|       |   394   (1)| 00:00:01 |  52493 |00:00:00.01 |    1416 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------

那么我们可以创建组合索引，代码如下：

create index IDX_SPAGE_OWID on HR.spage_0406(object_id,owner)

然后再看该分页查询的执行计划，欸，变好了！

SQL_ID  67hjvw5r90c9g, child number 0
-------------------------------------
SELECT * FROM(SELECT * FROM (SELECT SP.*, ROWNUM RN FROM (SELECT * FROM 
HR.SPAGE_0406 WHERE OWNER='SYS' ORDER BY OBJECT_ID) SP) WHERE ROWNUM <= 
10) WHERE RN >=1
 
Plan hash value: 961832651
 
------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name           | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                |      1 |        |       |   275 (100)|          |     10 |00:00:00.01 |       3 |      1 |
|*  1 |  VIEW                            |                |      1 |     10 |  4940 |   275   (1)| 00:00:01 |     10 |00:00:00.01 |       3 |      1 |
|*  2 |   COUNT STOPKEY                  |                |      1 |        |       |            |          |     10 |00:00:00.01 |       3 |      1 |
|   3 |    VIEW                          |                |      1 |   2908 |  1402K|   275   (1)| 00:00:01 |     10 |00:00:00.01 |       3 |      1 |
|   4 |     COUNT                        |                |      1 |        |       |            |          |     10 |00:00:00.01 |       3 |      1 |
|   5 |      VIEW                        |                |      1 |   2908 |  1365K|   275   (1)| 00:00:01 |     10 |00:00:00.01 |       3 |      1 |
|   6 |       TABLE ACCESS BY INDEX ROWID| SPAGE_0406     |      1 |   2908 |   374K|   275   (1)| 00:00:01 |     10 |00:00:00.01 |       3 |      1 |
|*  7 |        INDEX FULL SCAN           | IDX_SPAGE_OWID |      1 |   2908 |       |   211   (1)| 00:00:01 |     10 |00:00:00.01 |       2 |      1 |
------------------------------------------------------------------------------------------------------------------------------------------------------

上面罗里吧嗦了一堆内容，但对于有序分页查询来讲，还有下面两点内容需要提醒大家：

1、索引字段或组合索引先导列字段中的值能否最大程度最充分的完成排序；

2、索引的排序方式和SQL的实际排序方式是否一致，别一个是升序一个是降序；

扯些闲话：

最后我给自己再留一个作业吧，上面所有的叙述其实都是讲的单表分页查询；多表分页查询和单表分页查询的框架是一致的，但再进一步优化上是有区别的。今天受限于时间，下次我再对多表关联分页查询进行分享，或者有时间了我直接在本篇文章基础上进行编辑了。

最后就是，明天清明假期就结束了，要开始上班了，但我不想上班💔💔💔

姜豆豆耶
关注
  • 31
    点赞
  • 踩
  • 19
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
ORACLE SQL_分页查询
weixin_44564948的博客
04-16 8856
ORACLE SQL _分页查询 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 开发工具与关键技术：Oracle sql * plus PLSQL Developer 作者：叶 撰写时间：2019年01月01日 ~~~~~~~~~~...
Oracle分页查询性能优化代码详解
09-09
Oracle数据库在处理大规模数据时，分页查询是常见的操作，特别是在Web应用中，为了提高用户体验，通常会限制一次加载的数据量。然而，不恰当的分页查询方式可能会导致性能问题。本文将深入探讨Oracle分页查询的性能...
Oracle分页查询
热门推荐
blood_Z的博客
03-16 5万+
Oracle分页查询 方法一 -- 40为pageCurrent * pageSize，31 应为为(pageCurrent - 1) * pageSize SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE_NAME WHERE 1 = 1 -- 条件 ORDER BY CREATETIME DESC -- 排序 ) A WHERE ROWNUM <= 40
Oracle的SQL分页实践
07-26
Oracle的SQL分页实践
SQL oracle数据库中实现分页的简单sql语句
最新发布
weixin_67631325的博客
06-04 160
OFFSET '20' ROWS表示跳过前20行，FETCH NEXT '100' ROWS ONLY表示获取接下来的100行。上述sql中，OVER(ORDER BY '表中某字段' DESC|ASC)，可用DESC倒序 或者 ASC正序。简单来说就是将查询结果中新加了一个行号字段，字段名为rn，通过行号来实现不同的操作当然一般用作分页。注意：OFFSET和FETCH实现分页查询需要在oracle12c及其之后才可以使用。上述sql中： ROW和ROWS 可以互换使用、作用相同；
详解SQLServer和Oracle的分页查询
09-10
分页功能是我们的基本功，必须要牢固掌握。通过对分页算法的整理，希望对实现分页功能更得心应手了，需要的朋友可以参考下
oracle分页SQL（深入分析）
伶生的博客
01-15 4046
Oracle常用分页语句如下，但是这种分页写法，会随着查询的范围的扩大，而越来越慢。 有好的方法大家可以提出来交流一下。 select * from ( select row_.*, rownum rn from ( select * from 表名 t where 1=1 order by #{排序字段} ) row_ where rownum <= #{pageEnd} ) where rn>= #{pageStart} 原理分.
oracle做分页的sql,Oracle 中的sql函数以及分页
weixin_39664456的博客
04-13 626
SELECT LPAD('Page 1',,'*.') "LPAD example" FROM DUAL;1.分页查询(1)方法一：使用 between and来实现分页select * from(select emp.*,rownum rn from emp)where rn between and(2)方法二：使用rownum来实现分页select * from(select e...
Oracle分页查询：优化大数据集的结果集返回
renkejia1988的博客
08-19 1328
在本文中，我们介绍了Oracle的分页查询功能，并提供了使用ROWNUM、ROW_NUMBER()和FETCH FIRST/NEXT进行分页查询的示例。通过这些技术，您可以更高效地处理大数据集，并改善应用程序的性能和用户体验。
面试：数据量很大，分页查询很慢，有什么优化方案？
程序猿DD
09-19 903
点击上方蓝色“程序猿DD”，选择“设为星标”回复“资源”获取独家整理的学习资料！来源 |cnblogs.com/youyoui/p/7851007.html当需要从数据库查询的表有上万...
oracle 亿级数据分页,oracle分页查询千万级优化-Oracle
weixin_42561910的博客
04-13 2367
oracle分页查询千万级优化，在做比对项目时，有表是3千万多的数据，在页面做分页查询时很卡40s,反正差不多一分钟的时间，后来更了sql，优化了代码就好了很多，4s就可以了。代码里优化是把总条数存起来，点下一页时再传进来，不用每次都查sql是取条数时不要用and,between这些优化后的sql:———select * from (select C.*,a.*,rownum rn from TT...
oracle 分页_优化体系--Oracle分页查询的3种实现方案，值得收藏
weixin_39665847的博客
12-06 292
概述今天主要介绍一下Oracle的分页的一个实现方式，也是最近在做部门培训时介绍的一个新特性，下面介绍下测试的一个实验。1、环境准备准备测试数据267万CREATE TABLE t AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;insert into t select * from t;//.....2、需求需求：按照对象ID排序，取第21条到30条记录。...
Oracle实现分页查询的SQL语法汇总
12-15
在Oracle数据库中，分页查询是获取大量数据集的一部分，以提高性能并提供更好的用户体验，尤其是在Web应用中。本文将详细介绍Oracle实现分页查询的几种SQL语法。 1. **无ORDER BY排序的写法** 这种方法适用于无需...
oracle分页查询sql
01-15
oracle分页查询语句sql
MYSQL和ORACLE的分页
qq_41482600的博客
11-12 2313
mysql与oracle递归级联查询
MySQL、Oracle分页查询的SQL语句
Alun的博客
06-13 895
1.MySQL分页查询的SQL语句： 关键字：”limit”select * from SYS_USER order by user_id limit 0,5;"0"：第一条数据的位置，MySQL是从0开始的(例如第二页的话就是"limit 5,5") "5"：每一页展示数据的条数 开始的位置：int beginNum = (页码-1)* 每页条数; 2.Oracle分页查询的SQL语句：
oracle分页sql
Sicily_winner的博客
03-12 802
SELECT * FROM ( SELECT * FROM ( SELECT row_number() OVER (ORDER BY t1.OBJECTID) AS sort, t1.* FROM T_SJCZRZ t1 WHERE t1.OBJECTID BETWEEN 100 AND 600 ) t2 WHERE t2.sort &lt; 10 * 2 ) where ...
如何实现Oracle中的分页查询
振藩兄的博客
07-08 4182
在java中如何实现动态分页查询
oracle sql分页查询
07-29
在 Oracle SQL 中，可以使用 `ROWNUM` 函数和子查询来实现分页查询。以下是一个示例： ```sql SELECT * FROM ( SELECT t.*, ROWNUM AS rn FROM your_table t WHERE your_conditions ORDER BY your_order_column ) WHERE rn BETWEEN start_row AND end_row; ``` 在上面的查询中，你需要将 `your_table` 替换为你要查询的表名，`your_conditions` 替换为你的查询条件，`your_order_column` 替换为用于排序的列名，`start_row` 和 `end_row` 分别替换为你想要显示的起始行和结束行的行号。 请注意，`ROWNUM` 是 Oracle 特定的伪列，它表示返回结果集中的行号。通过使用子查询和 `ROWNUM`，我们可以创建一个带有行号的结果集，并在外部查询中筛选出特定行范围的结果。 希望这可以帮助你实现 Oracle SQL 的分页查询！如果有任何进一步的问题，请随时提问。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

姜豆豆耶

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值