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

目录

分页查询框架

分页查询注意事项 

有序/无序分页

事务带来的影响

分页查询与索引

排序字段索引实验

组合索引实验

利用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的实际排序方式是否一致，别一个是升序一个是降序；

扯些闲话：

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

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

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/533750.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

2024/4/2—力扣—连续数列

2024/4/2—力扣—连续数列

代码实现&#xff1a; 思路&#xff1a;最大子数组和 解法一&#xff1a;动态规划 #define max(a, b) ((a) > (b) ? (a) : (b))int maxSubArray(int* nums, int numsSize) {if (numsSize 0) { // 特殊情况return 0;}int dp[numsSize];dp[0] nums[0];int result dp[0];fo…
阅读更多...
阿里云云效CI/CD配置

阿里云云效CI/CD配置

1.NODEJS项目流水线配置(vue举例) nodejs构建配置 官方教程 注意:下图的dist是vue项目打包目录名称,根据实际名称配置 # input your command here cnpm cache clean --force cnpm install cnpm run build 主机部署配置 rm -rf /home/vipcardmall/frontend/ mkdir -p /home/…
阅读更多...
海山数据库（He3DB）原理剖析：浅析OLAP数据库计算引擎中的统计信息

海山数据库（He3DB）原理剖析：浅析OLAP数据库计算引擎中的统计信息

背景&#xff1a; 统计信息在计算引擎的优化器模块中经常被提及&#xff0c;尤其是在基于成本成本优化&#xff08;CBO&#xff09;框架中统计信息发挥着至关重要的作用。CBO旨在通过评估执行查询的可能方法&#xff0c;并选择最有效的执行计划来提高查询性能。而统计信息则提…
阅读更多...
传统企业如何实现数字化转型？

传统企业如何实现数字化转型？

传统企业实现数字化转型是一个系统性工程&#xff0c;涉及到企业战略、技术应用、组织结构、业务流程、人才培养等多个方面。以下是一些关键步骤和策略&#xff1a; 1、明确转型目标和战略&#xff1a;首先&#xff0c;企业需要明确数字化转型的目标&#xff0c;这通常是为了提…
阅读更多...
48-基于腾讯云EKS的容器化部署实战

48-基于腾讯云EKS的容器化部署实战

准备工作 在部署IAM应用之前&#xff0c;我们需要做以下准备工作&#xff1a; 开通腾讯云容器服务镜像仓库。安装并配置Docker。准备一个Kubernetes集群。 开通腾讯云容器服务镜像仓库 在Kubernetes集群中部署IAM应用&#xff0c;需要从镜像仓库下载指定的IAM镜像&#xff…
阅读更多...
MES车间管理有哪些方面

MES车间管理有哪些方面

一、MES车间管理概述 MES车间管理是以MES系统为基础&#xff0c;对车间生产过程进行全方位、实时性的管理和控制。它涵盖了生产计划、生产调度、物料管理、设备维护、质量控制等多个方面&#xff0c;确保生产过程的顺利进行&#xff0c;提高生产效率和质量。 二、生产计划与调…
阅读更多...
【重磅福利】数字化转型大数据数据治理平台建设精品PPT合集共25份（免费下载）

【重磅福利】数字化转型大数据数据治理平台建设精品PPT合集共25份（免费下载）

【1】关注本公众号 【2】私信发送 数字化转型 【3】获取本方案合集的下载链接&#xff0c;直接下载即可。 如需下载更多PPT原格式方案文档&#xff0c;请加入微信扫描以下方案驿站知识星球&#xff0c;获取上万份PPT解决方案&#xff01;&#xff01;&#xff01;感谢支持&am…
阅读更多...
AI大模型探索之路-应用篇8：Langchain框架LangServe模块-专注于AI模型的部署

AI大模型探索之路-应用篇8：Langchain框架LangServe模块-专注于AI模型的部署

目录 前言 一、概述 二、功能特性 三、REST API 开发 四、Postman调用测试 五、Client调用测试 总结 前言 随着AI大语言模型&#xff08;LLM&#xff09;的技术的不断演进&#xff0c;AI应用的开发和部署变得越来越复杂。在这样的背景下&#xff0c;LangServe应运而生—…
阅读更多...
Unity 中画线

Unity 中画线

前言&#xff1a; 在Unity项目中&#xff0c;调试和可视化是开发过程中不可或缺的部分。其中&#xff0c;绘制线条是一种常见的手段&#xff0c;可以用于在Scene场景和Game视图中进行调试和展示。本篇博客将为你介绍多种不同的绘制线条方法&#xff0c;帮助你轻松应对各种调试…
阅读更多...
第三资本：铸就辉煌非凡的资历

第三资本：铸就辉煌非凡的资历

第三资本香港有限公司在在金融投资领域一直以专业精神和不懈追求获得良好名声,近几年在国际资本市场上更是写下了辉煌的章节。针对第三资本而言,专业是基本,也是成功的唯一途径。投资总监刘国海解释道:“金融从业者务必深入把握专业能力,对行业现状敏感,重视风险管控,才能在这个…
阅读更多...
炫我桌面云流系统，不一样的云桌面系统

炫我桌面云流系统，不一样的云桌面系统

云桌面&#xff0c;又称桌面虚拟化、云电脑&#xff0c;是替代传统电脑的一种新模式。它采用虚拟化技术&#xff0c;将物理服务器划分为多个虚拟机&#xff0c;以提供多个独立的虚拟桌面环境。每个虚拟机都具有自己的操作系统、计算资源和应用程序等&#xff0c;可以实现多用户…
阅读更多...
vue快速入门（十四）reduce求和

vue快速入门（十四）reduce求和

注释很详细&#xff0c;直接上代码 上一篇 新增内容 非嵌套情况求和嵌套情况求和 源码 <!DOCTYPE html> <html lang"en"> <head><meta charset"UTF-8"><meta name"viewport" content"widthdevice-width, initia…
阅读更多...
ZYNQ7000 PL与PS交互总结

ZYNQ7000 PL与PS交互总结

这里写目录标题 一、ZYNQ7000整体框架二、AXI总线与接口2.1 AXI总线协议分类2.1.1 AXI_Lite协议2.1.2 AXI_Full协议2.1.3 AXI_Stream协议 2.2 AXI接口分类 三、PS与PL交互方式总结3.1 PL中断3.2 AXI_Lite协议配合AXI GP接口进行寄存器访问3.3 AXI_Full协议配合AXI HP接口进行内…
阅读更多...
Flutter第七弹 网格列表GridView

Flutter第七弹 网格列表GridView

1) Flutter提供了网格列表&#xff0c;怎么设置列数&#xff1f; 2&#xff09;怎么初始化每个列表项Item&#xff1f; 一、GridView简介 Flutter也存在网格列表组建GridView&#xff0c;用于展示多行多列的列表。 1.1 GridView构建 采用GridView.count() 进行构建 1.2 Gr…
阅读更多...
模型训练基本概念-赛博炼丹

模型训练基本概念-赛博炼丹

文章目录 拓展知识基础名词解释&#xff08;语义库更新&#xff09;机器学习任务流程模型训练基本流程模型训练详细流程正向传播与反向传播正向传播-求误差值反向传播-求参数值 学习率激活函数激活函数-为什么希望激活函数输出均值为0&#xff1f;激活函数 — softmax & ta…
阅读更多...
Pytorch张量的数学运算：向量基础运算

Pytorch张量的数学运算：向量基础运算

文章目录 一、简单运算二、广播运算1.广播的基本规则2.广播操作的例子 三、运算函数 参考&#xff1a;与凤行 张量的数学运算是深度学习和科学计算中的基础。张量可以被视为一个多维数组&#xff0c;其在数学和物理学中有广泛的应用。这些运算包括但不限于加法、减法、乘法、除…
阅读更多...
312_C++_QT表格的剪切、拷贝、粘贴，轻量化操作

312_C++_QT表格的剪切、拷贝、粘贴，轻量化操作

：拷贝 + 粘贴 ：剪切 + 粘贴 void CustomTableWidget::cut() {copy();// 获取所有选定的单元格项QList<QTableWidgetItem*> selectedItemsList
阅读更多...
若依安装过程

若依安装过程

文章目录 参考博客环境准备下载redisjdk1.8下载nacos 后端mysqlnacos运行npm 参考博客 https://blog.csdn.net/qq_31536117/article/details/134603862 环境准备 下载redis 参考https://redis.com.cn/redis-installation.html jdk1.8下载 参考 https://zhuanlan.zhihu.co…
阅读更多...
新质生产力与数字化转型：驱动经济转型升级的双引擎

新质生产力与数字化转型：驱动经济转型升级的双引擎

引言 随着时代的发展和科技的进步&#xff0c;新质生产力与数字化转型已成为当今经济转型升级的双引擎。新质生产力代表着经济发展的内在动力&#xff0c;数字化转型则为其提供了技术支撑和路径指引。本文将探讨新质生产力与数字化转型的概念&#xff0c;并分析它们对经济发展的…
阅读更多...
读所罗门的密码笔记16_直通心智

读所罗门的密码笔记16_直通心智

1. 直通心智 1.1. 如今&#xff0c;科学家已经可以诱发触觉、压觉、痛觉和大约250种其他感觉 1.1.1. DARPA支持的触觉技术第一次让一位受伤的人能够用假肢和手指感知到被触碰的物体 1.1.2. 可以建立人工系统&#xff0c;来替换和弥补受损大脑的部分区域 1.1.3. 神经科学家能…
阅读更多...
最新文章

Copyright @ 2022~2023