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

已于 2024-04-07 09:13:48 修改
阅读量2.1k 收藏 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 8858
ORACLE SQL _分页查询 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 开发工具与关键技术：Oracle sql * plus PLSQL Developer 作者：叶 撰写时间：2019年01月01日 ~~~~~~~~~~...
Oracle 分页
diuboqian9544的博客
01-04 1089
--1:无ORDER BY排序的写法。(效率最高) --(经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！) SELECT * FROM (SELECT ROWNUM AS rowno, t.* FROM emp t WHERE hire_date BETWEEN TO_DATE ...
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（深入分析）
伶生的博客
01-15 4055
Oracle常用分页语句如下，但是这种分页写法，会随着查询的范围的扩大，而越来越慢。 有好的方法大家可以提出来交流一下。 select * from ( select row_.*, rownum rn from ( select * from 表名 t where 1=1 order by #{排序字段} ) row_ where rownum <= #{pageEnd} ) where rn>= #{pageStart} 原理分.
SQL oracle数据库中实现分页的简单sql语句
最新发布
weixin_67631325的博客
06-04 167
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 可以互换使用、作用相同；
Oracle的SQL分页实践
07-26
Oracle的SQL分页实践
详解SQLServer和Oracle的分页查询
09-10
分页功能是我们的基本功，必须要牢固掌握。通过对分页算法的整理，希望对实现分页功能更得心应手了，需要的朋友可以参考下
Oracle实现分页查询的SQL语法汇总
12-15
本文实例汇总了Oracle实现分页查询的SQL语法，整理给大家供大家参考之用，详情如下： 1.无ORDER BY排序的写法。(效率最高) 经过测试，此方法成本最低，只嵌套一层，速度最快！即使查询的数据量再大，也几乎不受影响，速度依然！ sql语句如下： SELECT * FROM (Select ROWNUM AS ROWNO, T.* from k_task T where Flight_date between to_date('20060501', 'yyyymmdd') and to_date('20060731', 'yyyymmdd')
Oracle 三种分页方法
logqq的专栏
03-14 2万+
使用ROW_NUMBER() OVER()进行分页查询的优点是灵活性高，可以实现各种不同的分页方式，缺点是语法较为复杂。上述查询语句的意思是查询my_table表中的前30条记录，然后在这30条记录中取出第20到第30条记录作为结果返回。使用ROWNUM进行分页查询的优点是简单易懂，缺点是在查询大量数据时性能较差。Oracle的三层分页指的是在进行分页查询时，使用三种不同的方式来实现分页效果，分别是使用ROWNUM、使用OFFSET和FETCH、使用ROW_NUMBER() OVER()
ORACLE分页查询SQL语句(最有效的分页)
lchmyhua88的博客
11-01 3万+
rownum是Oracle数据库中的一个特有关键字，返回的是一个数字代表记录的行号。这篇文章主要介绍了oracle 使用rownum的二种分页方式,需要的朋友可以参考下 第一种，直接使用起止限定数： SELECT * FROM (SELECT t.*,ROWNUM r FROM TABLE t WHERE ROWNUM <= page*pageSize) WHERE r >= （(page - 1) * pageSize + 1） 第二种：使用BETWEEN： SELECT * ..
Oracle分页三种有效方法
Mistofinsult
12-17 2564
OFFSET 0 代表从0后面的一位开始计算也就是1 ，sql的0 和 10取得的结果就是1~10。where判断条件中的1和10大家可以用占位符传入自己想要展示的数据的区间。对TQ_QUERY表的 TQ_COLUMN列进行排序，取出1~10的数据返回。首先是在原表的数据中加入临时字段ROWNUM，相当于按数据顺序排上编号。场景：更适用于查询大量数据且有重复的数据，当然也可以查其它类型的数据。该方法适用于直接查询某个范围区间的数据。场景：查询主键不重复的所有数据。场景：各种不同的分页场景。
Oracle-sql查询优化（一）
qq_35030421的博客
09-13 672
一、一些sql书写规范 1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建 立索引。 2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进 行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有n...
Oracle SQL 查询优化.Part5
dwc2011的专栏
04-11 1239
一、字符串 1. 字符串截取： 语法：substr(string, start, length)，其中 string 是要截取的字符串，start 是开始的字符下标，length 是截取的长度。同时可以结合使用 instr 函数判断子串的位置，从字串第一次出现的位置开始截取。 -- 字符串截取 select substr(empdesc, instr(empdesc, 'a') + 1,
Oracle分页查询性能优化代码详解
09-09
Oracle数据库在处理大规模数据时，分页查询是常见的操作，特别是在Web应用中，为了提高用户体验，通常会限制一次加载的数据量。然而，不恰当的分页查询方式可能会导致性能问题。本文将深入探讨Oracle分页查询的性能...
oracle分页查询sql
01-15
oracle分页查询语句sql
MYSQL和ORACLE的分页
qq_41482600的博客
11-12 2323
mysql与oracle递归级联查询
SQL分页查询
m0_62508027的博客
11-10 5046
该方法使用了子查询来实现分页功能，内部的子查询使用ROW_NUMBER()函数对查询结果进行排序，并对每行赋值一个行号(RowNum)，然后在外部查询中使用WHERE子句对行号进行筛选，从而实现了分页查询。该方法使用了MySQL中的LIMIT和OFFSET关键字实现分页查询，其中LIMIT指定每页显示的数据量，OFFSET指定当前页的偏移量，通过不断调整OFFSET关键字的值来实现分页查询。两个参数时，因为limit默认下标从0开始，第一个参数是下标，查数据的话是从第一条查，第二个参数限定了几条数据。
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、付费专栏及课程。

余额充值