（Oracle）SQL优化案例：组合索引优化

项目场景

项目上的ETL模型里有如下SQL语句。执行速度非常慢，每次只查询200条数据，但却需要20多秒的时间。再加上该SQL查询出的数据同步频率很高，这个速度是完全不能忍受的。

因为项目隐私，所以对表及字段做了改写。

SELECT 
IDO.OD_SN
FROM IDO
LEFT JOIN IMO ON IMO.OD_SN= IDO.OD_SN
WHERE IMO.OD_TYPE IN ('X','Y')
AND IMO.SOURCE_ID IS NULL
AND IMO.MODIFY_TIME >= '20240423000000'
AND (IMO.YZ = 'N' OR IDO.YZ = 'N')
AND ROWNUM <= 200

IMO表的数据量：18134780行

IDO表的数据量：2908979行

上述SQL的结果集数量也很明显：200行 

问题分析

上面的SQL对于我等凡人来说，没办法一眼看出哪里有问题；所以还是需要拉一下执行计划（获取执行计划方法文章链接：获取执行计划）。

下面是问题SQL的执行计划，是已经将无关的信息删除。这里是获取的内存中shard_pool的执行计划，是真实的执行计划。

  • Plan hash value: 1275918432
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                           |      1 |        |       |  2051 (100)|          |    100 |00:00:29.08 |    4057K|      1 |
|*  1 |  COUNT STOPKEY                |                           |      1 |        |       |            |          |    100 |00:00:29.08 |    4057K|      1 |
|   2 |   NESTED LOOPS                |                           |      1 |    102 |  6936 |  2051   (1)| 00:00:01 |    100 |00:00:29.08 |    4057K|      1 |
|   3 |    NESTED LOOPS               |                           |      1 |    102 |  6936 |  2051   (1)| 00:00:01 |    100 |00:00:29.08 |    4056K|      1 |
|*  4 |     TABLE ACCESS FULL         | IMO                       |      1 |  21724 |   954K|  1846   (1)| 00:00:01 |    100 |00:00:29.08 |    4056K|      0 |
|*  5 |     INDEX UNIQUE SCAN         | UK_20230901211220_1065023 |    100 |      1 |       |     1   (0)| 00:00:01 |    100 |00:00:00.01 |     202 |      1 |
|*  6 |    TABLE ACCESS BY INDEX ROWID| IDO                       |    100 |      1 |    23 |     2   (0)| 00:00:01 |    100 |00:00:00.01 |     100 |      0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=100)
   4 - filter(("IMO"."MODIFY_TIME">='20240401000000' AND INTERNAL_FUNCTION("IMO"."OD_TYPE") AND "IMO"."SOURCE_ID" IS NULL))
   5 - access("IMO"."OD_SN"="IDO"."OD_SN")
   6 - filter((DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00279$",5)),NULL,NVL("IMO"."YZ",'N'),'0',NVL("IMO"."YZ",'N'),'1',"IMO"."
              YZ")='N' OR DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00155$",0)),NULL,NVL("IDO"."YZ",'N'),'0',NVL("IDO"."YZ",'N'),'1'
              ,"IDO"."YZ")='N'))

从上面的执行计划中，我们可以逐步分析：

  • 连接方式 （id=2、id=3）

IDO表和IMO表的连接方式是 NESTED LOOPS ，因为只返回少量数据（200行），所以走嵌套循环连接完全没问题。

  • 访问路径（id=4）

IMO表的访问路径是TABLE ACCESS FULL；上文已经提过，IMO表有18134780行数据，走全表扫描，还是返回少量数据；走全表扫描肯定是错误的

  • 访问路径（id=6）

IMO表的访问路径是TABLE ACCESS BY INDEX ROWID，索引ROWID扫描，没有问题。

  • 谓词信息

从谓词信息或者SQL语句中，我们可以发现IMO表中的MODIFY_TIME、SOURCE_ID、OD_TYPE字段中发生了谓词过滤。

从上面的信息，我们可以得出以下优化结论了：

Ⅰ：需要让IMO表走索引扫描；

Ⅱ：可以在IMO表上建立MODIFY_TIME、SOURCE_ID、OD_TYPE三个字段的组合索引；其中MODIFY_TIME的选择性最大，OD_TYPE的选择性其次，SOURCE_ID的选择性最差。所以选择MODIFY_TIME作为组合索引的先导列。

优化方案

创建组合索引

CREATE INDEX idx_mtime_type_source ON IMO (MODIFY_TIME,OD_TYPE,SOURCE_ID) ONLINE;

再次执行SQL，发现只需要0.1秒就可以执行完成。

我们此时再来看下执行计划，发现IMO表已经走了索引扫描；组合索引已经起到效果。

Plan hash value: 1019133023
 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name                      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads  |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                           |      1 |        |       | 19528 (100)|          |    100 |00:00:00.02 |     401 |     49 |
|*  1 |  COUNT STOPKEY                           |                           |      1 |        |       |            |          |    100 |00:00:00.02 |     401 |     49 |
|   2 |   VIEW                                   | VW_ORE_7D109085           |      1 |    103 |  2060 | 19528   (1)| 00:00:01 |    100 |00:00:00.02 |     401 |     49 |
|   3 |    UNION-ALL                             |                           |      1 |        |       |            |          |    100 |00:00:00.02 |     401 |     49 |
|   4 |     NESTED LOOPS                         |                           |      1 |    102 |  6732 |   195   (0)| 00:00:01 |    100 |00:00:00.02 |     401 |     49 |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED | IMO                       |      1 |  21723 |   954K|    93   (0)| 00:00:01 |    100 |00:00:00.01 |     203 |     36 |
|*  6 |       INDEX RANGE SCAN                   | IDX_MTIME_TYPE_SOURCE     |      1 |  21744 |       |     5   (0)| 00:00:01 |    102 |00:00:00.01 |      10 |      9 |
|*  7 |      INDEX UNIQUE SCAN                   | UK_20230901211220_1065023 |    100 |      1 |    21 |     1   (0)| 00:00:01 |    100 |00:00:00.01 |     198 |     13 |
|   8 |     NESTED LOOPS                         |                           |      0 |      1 |    68 | 19332   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|   9 |      NESTED LOOPS                        |                           |      0 |      1 |    68 | 19332   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|* 10 |       TABLE ACCESS BY INDEX ROWID BATCHED| IMO                       |      0 |      1 |    45 | 19330   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|* 11 |        INDEX RANGE SCAN                  | IDX_MTIME_TYPE_SOURCE     |      0 |  21744 |       |   608   (1)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|* 12 |       INDEX UNIQUE SCAN                  | UK_20230901211220_1065023 |      0 |      1 |       |     1   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
|* 13 |      TABLE ACCESS BY INDEX ROWID         | IDO                       |      0 |      1 |    23 |     2   (0)| 00:00:01 |      0 |00:00:00.01 |       0 |      0 |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(ROWNUM<=100)
   5 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00279$",5)),NULL,NVL("IMO"."YZ",'N'),'0',NVL("IMO"."YZ",'N'),'1',"IMO"."YZ
              ")='N')
   6 - access("IMO"."MODIFY_TIME">='20240401000000' AND "IMO"."SOURCE_ID" IS NULL AND "IMO"."MODIFY_TIME" IS NOT NULL)
       filter((INTERNAL_FUNCTION("IMO"."OD_TYPE") AND "IMO"."SOURCE_ID" IS NULL))
   7 - access("IMO"."OD_SN"="IDO"."OD_SN")
  10 - filter(LNNVL(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00279$",5)),NULL,NVL("IMO"."YZ",'N'),'0',NVL("IMO"."YZ",'N'),'1',"IMO"."YZ
              ")='N'))
  11 - access("IMO"."MODIFY_TIME">='20240401000000' AND "IMO"."SOURCE_ID" IS NULL AND "IMO"."MODIFY_TIME" IS NOT NULL)
       filter((INTERNAL_FUNCTION("IMO"."OD_TYPE") AND "IMO"."SOURCE_ID" IS NULL))
  12 - access("IMO"."OD_SN"="IDO"."OD_SN")
  13 - filter(DECODE(TO_CHAR(SYS_OP_VECBIT("SYS_NC00155$",0)),NULL,NVL("IDO"."YZ",'N'),'0',NVL("IDO"."YZ",'N'),'1',"IDO"."YZ
              ")='N')

 

 

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

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

相关文章

SVN小乌龟汉化问题

SVN小乌龟汉化问题

1.首先确认中文语言包和SVN版本需要一致&#xff08;点击右键 选择最后一个选项即可查看&#xff09; 官网链接 点击这个官网链接可以下载对应版本的中文包 2.下载好之后直接无脑下一步安装即可 3.如果还是没有中文&#xff0c;找到这个文件夹&#xff0c;把里面的内容全部删…
阅读更多...
SpaceX的核心Fact Sheet

SpaceX的核心Fact Sheet

首先给大家分享一组SpaceX的关键数据&#xff0c;让大家对这个神秘公司有个定量认知&#xff1a; 2024年SpaceX预计收入可达130亿美金&#xff0c;同比增长54%&#xff0c;预计2035年可达1000亿美金 SpaceX目前已经处于盈利状态&#xff0c;具体利润规模未知 SpaceX的发射成本…
阅读更多...
Kotlin语法入门-类与对象(6)

Kotlin语法入门-类与对象(6)

Kotlin语法入门-类与对象(6) 文章目录 Kotlin语法入门-类与对象(6)六、类与对象1、声明和调用2、get和set3、init函数初始化4、constructor构造函数4.1、主构造函数4.2、二级构造函数4.3、多个构造函数4.4、省略主构造函数并写了次构造函数 5、类的继承与重写5.1、继承5.2、继承…
阅读更多...
每天五分钟计算机视觉：基于YOLO算法精确分类定位图片中的对象

每天五分钟计算机视觉：基于YOLO算法精确分类定位图片中的对象

滑动窗口的卷积的问题 滑动窗口的卷积实现效率很高，但是它依然不能够输出最精准的边界框，比如下面所示： 我们可以看到蓝色框不论在什么位置都不能很好的确定车的位置，有一个算法是YOLO 算法它能够帮助我们解决这个问题。 YOLO 算法 比如我们的输入图像是100*100，我们会…
阅读更多...
TCP相关问题总结

TCP相关问题总结

文章目录 TCP连接建立过程1. TCP三次握手2. TCP四次挥手3. TCP为什么是三次握手4. TCP为什么是四次挥手 TCP流量控制TCP拥塞控制1. 为什么需要拥塞控制2. 控制手段 TCP连接建立过程中出现丢包 TCP连接建立过程 1. TCP三次握手 首先client端发出连接请求&#xff0c;并且请求同…
阅读更多...
在 VSCode 中运行 C#

在 VSCode 中运行 C#

文章目录 1.为何选择VSCode而不是VS2.操作步骤2.1 安装.NET2.2 安装扩展插件2.2.1 C#2.2.2 Code Runner 3.新建工程HelloCsharp 1.为何选择VSCode而不是VS VS实在是太“重”了&#xff0c;如果只是写一些简单控制台程序进行调试&#xff0c;则完全没必要 2.操作步骤 2.1 安装…
阅读更多...
线性代数 --- 矩阵的对角化以及矩阵的n次幂

线性代数 --- 矩阵的对角化以及矩阵的n次幂

矩阵的对角化以及矩阵的n次幂 &#xff08;特征向量与特征值的应用&#xff09; 前言&#xff1a; 在上一篇文章中&#xff0c;我记录了学习矩阵的特征向量和特征值的学习笔记&#xff0c;所关注的是那些矩阵A作用于向量x后&#xff0c;方向不发生改变的x(仅有尺度的缩放)。线…
阅读更多...
iOS - 多线程-GCD-队列组

iOS - 多线程-GCD-队列组

文章目录 iOS - 多线程-GCD-队列组1. 队列组1.1 基本使用步骤 iOS - 多线程-GCD-队列组 开发过程中&#xff0c;有时候想实现这样的效果 多个任务并发执行所有任务执行完成后&#xff0c;进行下一步处理&#xff08;比如回到主线程刷新UI&#xff09; 1. 队列组 可以使用GC…
阅读更多...
探索亚马逊云科技「生成式 AI 精英速成计划」

探索亚马逊云科技「生成式 AI 精英速成计划」

目录 前言「生成式 AI 精英速成计划」技术开发课程学习课程学习 总结 前言 亚马逊云科技&#xff08;Amazon Web Services&#xff0c;简称AWS&#xff09;作为全球领先的云计算服务提供商&#xff0c;一直以来在推动人工智能&#xff08;AI&#xff09;领域的发展中扮演着重要…
阅读更多...
调度问题变形的贪心算法分析与实现

调度问题变形的贪心算法分析与实现

调度问题变形的贪心算法分析与实现 一、问题背景与算法描述二、算法正确性证明三、算法实现与分析四、结论 一、问题背景与算法描述 带截止时间和惩罚的单位时间任务调度问题是一个典型的贪心算法应用场景。该问题的目标是最小化超过截止时间导致的惩罚总和。给定一组单位时间…
阅读更多...
element plus：tree拖动节点交换位置和改变层级

element plus：tree拖动节点交换位置和改变层级

图层list里有各种组件&#xff0c;用element plus的tree来渲染&#xff0c;可以把图片等组件到面板里&#xff0c;面板是容器&#xff0c;非容器组件&#xff0c;比如图片、文本等&#xff0c;就不能让其他组件拖进来。 主要在于allow-drop属性的回调函数编写&#xff0c;要理清…
阅读更多...
毕业撒花 流感服务小程序的设计与实现

毕业撒花 流感服务小程序的设计与实现

目录 1.1 总体页面设计 1.1.1 用户首页 1.1.2 新闻页面 1.1.3 我的页面 1.1.5 管理员登陆页面 1.1.6 管理员首页 1.2 用户模块 1.2.1 体检预约功能 1.2.2 体检报告功能 1.2.4 流感数据可视化功能 1.2.5 知识科普功能 1.2.6 疾病判断功能 1.2.7 出示个人就诊码功能 …
阅读更多...
java实现解析html获取图片或视频url

java实现解析html获取图片或视频url

一、前言 有时在实际项目中&#xff0c;比如发布某篇文章&#xff0c;需要取文章中的某张图片作为封面&#xff0c;那么此时需要文章内容&#xff0c;获取html内容中的图片地址作为封面&#xff0c;下面讲下如何获取html中的图片或视频地址。 二、实现 1.先定义一个工具类&…
阅读更多...
Elasticsearch集群部署（Linux）

Elasticsearch集群部署（Linux）

1. 准备环境 这里准备三台Linux虚拟机&#xff0c;用于配置Elasticsearch集群和部署可视化工具Kibana。 角色IP域名集群名称节点名称版本操作系统ES192.168.243.100linux100cluster-eses-node-1007.12.0CentOS 7192.168.243.101linux101cluster-eses-node-101192.168.243.102…
阅读更多...
IDEA 常规设置，让工作便利化

IDEA 常规设置，让工作便利化

1、自动提示&#xff0c;不区分大小写 File-->Settings-->Editor-->Code completion 然后把Match Case前面的勾选去掉&#xff0c;点击OK保存 2.快速生成main方法设置 idea快速生成main方法的快捷键是psvm (public static void main(String[] args) {}) &#xff1b;…
阅读更多...
C语言入门课程学习笔记1

C语言入门课程学习笔记1

C语言入门课程学习笔记1 第1课 - 概论第2课 -helloworld第3课 -数据输出第4课 -数据类型与变量第5课 - 深入数据类型与变量第6课 - 类型与变量编程练习第7课 - 程序中的数据输入 本文学习自狄泰软件学院 唐佐林老师的 C语言入门课程&#xff0c;图片全部来源于课程PPT&#xff…
阅读更多...
分割链表和回文链表习题

分割链表和回文链表习题

感谢大佬的光临各位&#xff0c;希望和大家一起进步&#xff0c;望得到你的三连&#xff0c;互三支持&#xff0c;一起进步 个人主页&#xff1a;LaNzikinh-CSDN博客 收入专栏:初阶数据结构_LaNzikinh篮子的博客-CSDN博客 文章目录 一.回文链表LCR 027. 回文链表 - 力扣&#x…
阅读更多...
BUUCTF---[SWPU2019]神奇的二维码

BUUCTF---[SWPU2019]神奇的二维码

1、下载附件是一张二维码&#xff0c;拿去扫描得到了flag 2、拿去提交是错的&#xff08;不会这么简单哈哈哈&#xff09;&#xff0c;常规操作在kali中分析 3、分离发现图片里面有东西 4、查看txt&#xff0c;发现里面有一串字符&#xff0c;解码后为 5、查看文档&#xff0c…
阅读更多...
比特币之路：技术突破、创新思维与领军人物

比特币之路：技术突破、创新思维与领军人物

比特币的兴起是一段充满技术突破、创新思维和领军人物的传奇之路。在这篇文章中&#xff0c;我们将探讨比特币发展的历程&#xff0c;以及那些在这一过程中发挥重要作用的关键人物。 技术突破与前奏 比特币的诞生并非凭空而来&#xff0c;而是建立在先前的技术储备之上。在密码…
阅读更多...
小程序 rich-text 解析富文本 图片过大时如何自适应？

小程序 rich-text 解析富文本 图片过大时如何自适应？

在微信小程序中&#xff0c;用rich-text 解析后端返回的数据&#xff0c;当图片尺寸太大时&#xff0c;会溢出屏幕&#xff0c;导致横向出现滚动 查看富文本代码 图片是用 <img 标签&#xff0c;所以写个正则匹配一下图片标签&#xff0c;手动加上样式即可 // content 为后…
阅读更多...
最新文章

Copyright @ 2022~2023

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