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

目录

分页查询框架

分页查询注意事项 

有序/无序分页

事务带来的影响

分页查询与索引

排序字段索引实验

组合索引实验

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

  • 伪列

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

  • 行号

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

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

分页查询框架

SELECT *
FROM(SELECT *  FROM  (SELECT sp.*,ROWNUM rnFROM (/*需要分页的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 rnFROM (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 rnFROM (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 >=1Plan 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 >=1Plan 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 rnFROM (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 >=1Plan 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 >=1Plan 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的实际排序方式是否一致，别一个是升序一个是降序；

扯些闲话：

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

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

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

如若内容造成侵权/违法违规/事实不符，请联系长河编程网进行投诉反馈email:809451989@qq.com，一经查实，立即删除！

相关文章

基于“PLUS模型+”生态系统服务多情景模拟预测

基于“PLUS模型+”生态系统服务多情景模拟预测

工业革命以来&#xff0c;社会生产力迅速提高&#xff0c;人类活动频繁&#xff0c;此外人口与日俱增对土地的需求与改造更加强烈&#xff0c;人-地关系日益紧张。此外&#xff0c;土地资源的不合理开发利用更是造成了水土流失、植被退化、水资源短缺、区域气候变化、生物多样性…
阅读更多...
HTTP 摘要认证

HTTP 摘要认证

文章目录 一、什么是摘要认证二、工作流程三、实例演示 一、什么是摘要认证 摘要认证&#xff0c;即 Digest Access Authentication&#xff0c;是一种HTTP身份验证机制&#xff0c;用于验证用户的身份。相较于基本认证&#xff08;Basic Authentication&#xff09;使用用户名…
阅读更多...
【随笔】Git 高级篇 -- 相对引用2 HEAD~n（十三）

【随笔】Git 高级篇 -- 相对引用2 HEAD~n（十三）

&#x1f48c; 所属专栏&#xff1a;【Git】 &#x1f600; 作  者&#xff1a;我是夜阑的狗&#x1f436; &#x1f680; 个人简介&#xff1a;一个正在努力学技术的CV工程师&#xff0c;专注基础和实战分享 &#xff0c;欢迎咨询&#xff01; &#x1f496; 欢迎大…
阅读更多...
Mongodb入门--头歌实验MongoDB 数据库基本操作

Mongodb入门--头歌实验MongoDB 数据库基本操作

一、数据库创建 任务描述 本关任务&#xff1a;创建数据库。 相关知识 本关评测是在 Linux 环境下进行的&#xff0c;MongoDB 的安装与配置测评系统均已默认完成。 为了完成本关任务&#xff0c;你需要掌握&#xff1a; 1.如何连接数据库&#xff1b; 2.如何创建数据库。 连接数…
阅读更多...
sqlmap（四）案例

sqlmap（四）案例

一、注入DB2 http://124.70.71.251:49431/new_list.php?id1 这是墨者学院里的靶机&#xff0c;地址&#xff1a;https://www.mozhe.cn/ 1.1 测试数据库类型 python sqlmap.py -u "http://124.70.71.251:49431/new_list.php?id1" 1.2 测试用户权限类型 查询选…
阅读更多...
Vue3 ts环境下的PropType

Vue3 ts环境下的PropType

简介 在Typscript中&#xff0c;我们可以使用PropType进行类型的推断与验证。在日常的开发中我们常常会遇到下面这样的场景&#xff1a; 我们通过request请求从服务端获取了一条数据&#xff0c;数据是个Array的格式&#xff0c;Array中的每个元素又是一个对象&#xff0c;像下…
阅读更多...
Web前端—属性描述符

Web前端—属性描述符

属性描述符 假设有一个对象obj var obj {a:1 }观察这个对象&#xff0c;我们如何来描述属性a&#xff1a; 值为1可以重写可以遍历 我们可以通过Object.getOwnPropertyDescriptor得到它的属性描述符 var desc Object.getOwnPropertyDescriptor(obj, a); console.log(desc);我…
阅读更多...
Python-VBA函数之旅-bytearray函数

Python-VBA函数之旅-bytearray函数

目录 1、bytearray函数&#xff1a; 1-1、Python&#xff1a; 1-2、VBA&#xff1a; 2、相关文章&#xff1a; 个人主页&#xff1a;非风V非雨-CSDN博客 bytearray函数在Python中提供了一种可变字节序列的表示方式&#xff0c;这在实际编程中有多种应用场景。常见的应用场…
阅读更多...
RabbitMQ3.13.x之九_Docker中安装RabbitMQ

RabbitMQ3.13.x之九_Docker中安装RabbitMQ

RabbitMQ3.13.x之_Docker中安装RabbitMQ 文章目录 RabbitMQ3.13.x之_Docker中安装RabbitMQ1. 官网2. 安装1 .拉取镜像2. 运行容器 3. 访问 1. 官网 rabbitmq - Official Image | Docker Hub 2. 安装 1 .拉取镜像 docker pull rabbitmq:3.13.0-management2. 运行容器 # lates…
阅读更多...
蓝桥杯-数组分割

蓝桥杯-数组分割

问题描述 小蓝有一个长度为 N 的数组 A 「Ao,A1,…,A~-1]。现在小蓝想要从 A 对应的数组下标所构成的集合I 0,1,2,… N-1 中找出一个子集 民1&#xff0c;那么 民」在I中的补集为Rz。记S∑reR 4&#xff0c;S2∑rERA,&#xff0c;我们要求S、和 S,均为偶数&#xff0c;请问在这…
阅读更多...
Spring6-单元测试：JUnit

Spring6-单元测试：JUnit

1. 概念 在进行单元测试时&#xff0c;特别是针对使用了Spring框架的应用程序&#xff0c;我们通常需要与Spring容器交互以获取被测试对象及其依赖。传统做法是在每个测试方法中手动创建Spring容器并从中获取所需的Bean。以下面的两行常见代码为例&#xff1a; ApplicationCo…
阅读更多...
【教程】混淆Dart 代码

【教程】混淆Dart 代码

什么是代码混淆&#xff1f; 代码混淆是一种将应用程序二进制文件转换为功能上等价&#xff0c;但人类难于阅读和理解的行为。在编译 Dart 代码时&#xff0c;混淆会隐藏函数和类的名称&#xff0c;并用其他符号替代每个符号&#xff0c;从而使攻击者难以进行逆向工程。 Flut…
阅读更多...
基于SpringBoot的“垃圾分类网站”的设计与实现（源码+数据库+文档+PPT)

基于SpringBoot的“垃圾分类网站”的设计与实现（源码+数据库+文档+PPT)

基于SpringBoot的“垃圾分类网站”的设计与实现&#xff08;源码数据库文档PPT) 开发语言&#xff1a;Java 数据库&#xff1a;MySQL 技术&#xff1a;SpringBoot 工具&#xff1a;IDEA/Ecilpse、Navicat、Maven 系统展示 系统功能结构图 系统功能界面图 用户登录、用户注…
阅读更多...
SpirngBoot开发常用知识

SpirngBoot开发常用知识

springboot开发常用知识 命令行打包SpringBoot打包插件window端口命令临时属性设置热部署启动热部署热部署范围 常用计量单位数据校验加载测试的专用属性Web环境模拟测试如何发送虚拟请求业务层测试回滚随机产生测试用例内置数据源 命令行打包 对SpringBoot项目进行打包命令行…
阅读更多...
适用于 Windows 10 的 10 大免费数据恢复软件

适用于 Windows 10 的 10 大免费数据恢复软件

数据丢失可能是一场噩梦&#xff0c;尤其是在涉及重要文件和文档时。无论是由于意外删除、系统崩溃还是病毒攻击&#xff0c;找到适合 Windows 10 的文件夹恢复软件都可以在恢复丢失的数据方面发挥重要作用。在本指南中&#xff0c;我们将探索适用于 Windows 10 用户的 10 大免…
阅读更多...
[STL-list]介绍、与vector的对比、模拟实现的迭代器问题

[STL-list]介绍、与vector的对比、模拟实现的迭代器问题

一、list使用介绍 list的底层是带头双向链表结构&#xff0c;双向链表中每个元素存储在互不相关的独立节点中&#xff0c;在节点中通过指针指向其前一个元素和后一个元素。与其他的序列式容器相比(array&#xff0c;vector&#xff0c;deque)&#xff0c;list通常在任意位置进行…
阅读更多...
[Apple Vision Pro]开源项目 Beautiful Things App Template

[Apple Vision Pro]开源项目 Beautiful Things App Template

1. 技术框架概述&#xff1a; - Beautiful Things App Template是一个为visionOS设计的免费开源软件&#xff08;FOSS&#xff09;&#xff0c;用于展示3D模型画廊。 2. 定位&#xff1a; - 该模板作为Beautiful Things网站的延伸&#xff0c;旨在为Apple Vision Pro用户…
阅读更多...
CNAS软件测试公司有什么好处?如何选择靠谱的软件测试公司?

CNAS软件测试公司有什么好处?如何选择靠谱的软件测试公司?

CNAS认可是中国合格评定国家认可委员会的英文缩写&#xff0c;由国家认证认可监督管理委员会批准设立并授权的国家认可机构&#xff0c;统一负责对认证机构、实验室和检验机构等相关机构的认可工作。 在软件测试行业&#xff0c;CNAS认可具有重要意义。它标志着一个软件测试公…
阅读更多...
C# 如何修改项目名称

C# 如何修改项目名称

目录 背景具体步骤1、Visual Studio中修改项目名和程序集名称以及命名空间2、修改项目文件夹名3、修改解决方案里项目的路径4、再次打开解决方案&#xff0c;问题解决步骤总结 名词解释解决方案&#xff08;Solution&#xff09;项目&#xff08;Project&#xff09;程序集&…
阅读更多...
浏览器工作原理与实践--虚拟DOM：虚拟DOM和实际的DOM有何不同

浏览器工作原理与实践--虚拟DOM：虚拟DOM和实际的DOM有何不同

虚拟DOM是最近非常火的技术&#xff0c;两大著名前端框架React和Vue都使用了虚拟DOM&#xff0c;所以我觉得非常有必要结合浏览器的工作机制对虚拟DOM进行一次分析。当然了&#xff0c;React和Vue框架本身所蕴含的知识点非常多&#xff0c;而且也不是我们专栏的重点&#xff0c…
阅读更多...
最新文章
推荐文章

Copyright @ 2022~2023

友情链接： 我的编程经验分享 一条长河网 尧图网站开发