一个改善含有ROWNUM条件语句性能的案例

最新推荐文章于 2021-10-21 09:30:07 发布
最新推荐文章于 2021-10-21 09:30:07 发布
阅读量708 收藏
点赞数
分类专栏： SQL优化案例 oracle 大数据+机器学习+oracle

数据库版本: 10.2.0.3；

案例背景：我们有一个Java程序会从一个配置文件中读取并执行以下一条SQL语句，由查询结果确认是否存在满足条件的数据记录，然后据此再执行后续代码。
最初的语句为：

SQL代码
  1. Select count(1) as rowcount   
  2. from B2B_BIZ_KEY B  
  3. WHERE EXISTS  
  4. (SELECT ID FROM B2B_TRANSACTION T  
  5.   WHERE T.ID=B.TRANSACTION_ID AND T.MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-07-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')));  

程序的中判断条件为rowcount是否大于0.

表B2B_BIZ_KEY是表B2B_TRANSACTION子表，它有一个外键（已建索引）在字段(TRANSACTION_ID)上，指向B2B_TRANSACTION的主键 (ID)。尽管在B2B_TRANSACTION上还存在一个索引(MSG_REQ_ID, ID)，但由于这两张表中的数据量很大，因此语句的性能很差。以下是其执行后的性能统计数据。

SQL代码
  1. Elapsed: 00:02:12.48  
  2.   
  3. Execution Plan  
  4. ----------------------------------------------------------  
  5.    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=165253 Card=1 Bytes=71)  
  6.    1    0   SORT (AGGREGATE)  
  7.    2    1     HASH JOIN (RIGHT SEMI) (Cost=165253 Card=35644057 Bytes=2530728047)  
  8.    3    2       INDEX (FAST FULL SCAN) OF 'B2B_TRANSACTION_UN' (INDEX(UNIQUE)) (Cost=15035 Card=8184629 Bytes=384677563)  
  9.    4    2       INDEX (FAST FULL SCAN) OF 'B2B_BIZ_KEY_UN' (INDEX (UNIQUE)) (Cost=65567 Card=36056379 Bytes=865353096)  
  10.   
  11. Statistics  
  12. ----------------------------------------------------------  
  13.         943  recursive calls  
  14.           0  db block gets  
  15.      363128  consistent gets  
  16.      416008  physical reads  
  17.           0  redo size  
  18.         353  bytes sent via SQL*Net to client  
  19.         435  bytes received via SQL*Net from client  
  20.           2  SQL*Net roundtrips to/from client  
  21.           0  sorts (memory)  
  22.           0  sorts (disk)  
  23.           1  rows processed  

通过逻辑分析，我们知道实际上根本需要获取满足条件的所有记录数。因此，我们可以给语句加上“ROWNUM<=1”条件。
修改后的语句是这样的：

SQL代码
  1. Select count(1) as rowcount   
  2. from B2B_BIZ_KEY B  
  3. WHERE EXISTS  
  4. (SELECT ID FROM B2B_TRANSACTION T  
  5.   WHERE T.ID=B.TRANSACTION_ID AND T.MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-07-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')))  
  6. AND ROWNUM <= 1;  

但是我们发现，尽管语句的性能确实得到很大地提高，但并未达到我们期望的效果。运行结果如下：

SQL代码
  1. Elapsed: 00:00:19.76  
  2.   
  3. Execution Plan  
  4. ----------------------------------------------------------  
  5.    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=165253 Card=1 Bytes=71)  
  6.    1    0   SORT (AGGREGATE)  
  7.    2    1     COUNT (STOPKEY)  
  8.    3    2       HASH JOIN (RIGHT SEMI) (Cost=165253 Card=35644057 Bytes=2530728047)  
  9.    4    3         INDEX (FAST FULL SCAN) OF 'B2B_TRANSACTION_UN' (INDEX (UNIQUE)) (Cost=15035 Card=8184629 Bytes=384677563)  
  10.    5    3         INDEX (FAST FULL SCAN) OF 'B2B_BIZ_KEY_UN' (INDEX (UNIQUE)) (Cost=65567 Card=36056379 Bytes=865353096)  
  11.   
  12. Statistics  
  13. ----------------------------------------------------------  
  14.         249  recursive calls  
  15.           0  db block gets  
  16.       66098  consistent gets  
  17.       13694  physical reads  
  18.           0  redo size  
  19.         350  bytes sent via SQL*Net to client  
  20.         435  bytes received via SQL*Net from client  
  21.           2  SQL*Net roundtrips to/from client  
  22.           0  sorts (memory)  
  23.           0  sorts (disk)  
  24.           1  rows processed  

我们假设"ROWNUM <= 1"的目的是为了使语句避免扫描没必要的记录，同时让优化器能基于这样的目的对执行计划进行优化————这种优化模式也就是我们所知的取前K条记录（First K rows）的优化模式。然而，由于COUNT(1)的存在，优化器知道服务端与客户端之间只会交互一次，因此认为取前K条记录的优化模式对于这条语句是没有意义的。如果将COUNT移除，运行结果如下：

SQL代码
  1. HELLODBA.COM> Select 1 as rowcount  
  2.   2  from B2B_BIZ_KEY B  
  3.   3  WHERE EXISTS  
  4.   4  (SELECT ID FROM B2B_TRANSACTION T  
  5.   5    WHERE T.ID=B.TRANSACTION_ID AND T.MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-07-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')))  
  6.   6    AND ROWNUM<=1;  
  7.   
  8. Elapsed: 00:00:00.43  
  9.   
  10. Execution Plan  
  11. ----------------------------------------------------------  
  12.    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=8 Card=1 Bytes=71)  
  13.    1    0   COUNT (STOPKEY)  
  14.    2    1     NESTED LOOPS (SEMI) (Cost=8 Card=3 Bytes=213)  
  15.    3    2       TABLE ACCESS (FULL) OF 'B2B_BIZ_KEY' (TABLE) (Cost=2 Card=22996310 Bytes=551911440)  
  16.    4    2       INDEX (RANGE SCAN) OF 'B2B_TRANSACTION_IX2' (INDEX) (Cost=2 Card=5456419 Bytes=256451693)  
  17.   
  18. Statistics  
  19. ----------------------------------------------------------  
  20.           1  recursive calls  
  21.           0  db block gets  
  22.           7  consistent gets  
  23.           0  physical reads  
  24.           0  redo size  
  25.         350  bytes sent via SQL*Net to client  
  26.         435  bytes received via SQL*Net from client  
  27.           2  SQL*Net roundtrips to/from client  
  28.           0  sorts (memory)  
  29.           0  sorts (disk)  
  30.           1  rows processed  

这个结果正式我们期望的。不过，如果仅仅将语句修改成这样，那么会导致程序缺陷。因为在我们的Java代码中并没有处理NO_DATA_FOUND的异常。我们也不想再修改Java代码。那还有其他办法吗？我曾经考虑过，如果能将上述结果写入一张临时表，然后再对临时表计数，应该可以解决上述问题。因此，我尝试将语句写出以下形式：

SQL代码
  1. HELLODBA.COM> with V as  
  2.   2  (Select /*+ MATERIALIZE qb_name(wv)*/1  
  3.   3  from B2B_BIZ_KEY B  
  4.   4  WHERE EXISTS  
  5.   5  (SELECT ID FROM B2B_TRANSACTION T  
  6.   6    WHERE T.ID=B.TRANSACTION_ID AND T.MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-07-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')))  
  7.   7    AND ROWNUM<=1)  
  8.   8  select count(1) from v;  
  9.   
  10. Elapsed: 00:00:15.84  
  11.   
  12. Execution Plan  
  13. ----------------------------------------------------------  
  14.    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=165255 Card=1)  
  15.    1    0   TEMP TABLE TRANSFORMATION  
  16.    2    1     LOAD AS SELECT  
  17.    3    2       COUNT (STOPKEY)  
  18.    4    3         HASH JOIN (RIGHT SEMI) (Cost=165253 Card=35644057 Bytes=2530728047)  
  19.    5    4           INDEX (FAST FULL SCAN) OF 'B2B_TRANSACTION_UN' (INDEX (UNIQUE)) (Cost=15035 Card=8184629 Bytes=384677563)  
  20.    6    4           INDEX (FAST FULL SCAN) OF 'B2B_BIZ_KEY_UN' (INDEX(UNIQUE)) (Cost=65567 Card=36056379 Bytes=865353096)  
  21.    7    1     SORT (AGGREGATE)  
  22.    8    7       VIEW (Cost=2 Card=1)  
  23.    9    8         TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9FC8C0_51579C6B'(TABLE (TEMP)) (Cost=2 Card=1 Bytes=13)  
  24.   
  25. Statistics  
  26. ----------------------------------------------------------  
  27.         520  recursive calls  
  28.          10  db block gets  
  29.       66112  consistent gets  
  30.       11595  physical reads  
  31.        1460  redo size  
  32.         350  bytes sent via SQL*Net to client  
  33.         435  bytes received via SQL*Net from client  
  34.           2  SQL*Net roundtrips to/from client  
  35.           0  sorts (memory)  
  36.           0  sorts (disk)  
  37.           1  rows processed  

单从执行计划看，似乎与我的想法比较吻合：SQL引擎先进子查询结果写入一直系统临时表，然后再扫描临时表计数。但是，这个两个过程并不是分开的，而属于同一执行计划————COUNT还是导致优化器没有采纳取前K条记录的优化模式。好吧，那就使用SQL技巧将COUNT消除掉吧：

SQL代码
  1. HELLODBA.COM> with V as  
  2.   2  (Select 1  
  3.   3  from B2B_BIZ_KEY B  
  4.   4  WHERE EXISTS  
  5.   5  (SELECT ID FROM B2B_TRANSACTION T  
  6.   6    WHERE T.ID=B.TRANSACTION_ID AND T.MSG_REQ_ID <= RPAD('EDI', 11, TO_CHAR(TO_TIMESTAMP('2012-07-31 00:00:00','YYYY-MM-DD HH24:MI:SS.FF'), 'YYYYMMDD')))  
  7.   7    AND ROWNUM<=1)  
  8.   8  select nvl2((select 1 from v),1,0) as aaa from dual;  
  9.   
  10. Elapsed: 00:00:00.21  
  11.   
  12. Execution Plan  
  13. ----------------------------------------------------------  
  14.    0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1)  
  15.    1    0   VIEW (Cost=8 Card=1)  
  16.    2    1     COUNT (STOPKEY)  
  17.    3    2       NESTED LOOPS (SEMI) (Cost=8 Card=3 Bytes=213)  
  18.    4    3         TABLE ACCESS (FULL) OF 'B2B_BIZ_KEY' (TABLE) (Cost=2Card=22996310 Bytes=551911440)  
  19.    5    3         INDEX (RANGE SCAN) OF 'B2B_TRANSACTION_IX2' (INDEX)(Cost=2 Card=5456419 Bytes=256451693)  
  20.    6    0   FAST DUAL (Cost=2 Card=1)  
  21.   
  22. Statistics  
  23. ----------------------------------------------------------  
  24.           0  recursive calls  
  25.           0  db block gets  
  26.           7  consistent gets  
  27.           0  physical reads  
  28.           0  redo size  
  29.         345  bytes sent via SQL*Net to client  
  30.         435  bytes received via SQL*Net from client  
  31.           2  SQL*Net roundtrips to/from client  
  32.           0  sorts (memory)  
  33.           0  sorts (disk)  
  34.           1  rows processed  

Ok. 性能满足要求，也不存在 NO_DATA_FOUND 异常的问题了。

--- Fuyuncat ---

蘑菇丁
关注
专栏目录
数据库性能优化详解
逍遥飞鹤的专栏
07-23 1万+
一、数据库访问优化法则简介 要正确的优化SQL，我们需要快速定位能性的瓶颈点，也就是说快速找到我们SQL主要的开销在哪里？而大多数情况性能最慢的设备会是瓶颈点，如下载时网络速度可能会是瓶颈点，本地复制文件时硬盘可能会是瓶颈点，为什么这些一般的工作我们能快速确认瓶颈点呢，因为我们对这些慢速设备的性能数据有一些基本的认识，如网络带宽是2Mbps，硬盘是每分钟7200转等等。因此，为了快速找到SQ
Java初级面试题
虚空掠夺者~
08-14 1383
Java基础部分 基础部分的顺序：基本语法，类相关的语法，内部类的语法，继承相关的语法，异常的语法，线程的语法，集合的语法，io的语法，虚拟机方面的语法。 1、一个".java"源文件中是否可以包括多个类（不是内部类）？有什么限制？ 可以有多个类，但只能有一个public的类，并且public的类名必须与文件名相一致。 2、Java有没有goto? java中的保留字，现在没有在java中使用。 3、说说&和&&的区别。 &和&&都可以用作逻辑与的运算符，.
oracle关于rownum的【整理】
码农的田园
12-25 1396
关于rownum是怎么产生的（网上有不少的文章，下面是摘录）： rownum是在where条件过滤之后，在任何排序（order by）或聚集（aggregation）之前赋给行的。同时，只有当rownum被分配给行后才会递增。rownum的初始值为1。rownum在查询中产生后就不再变化： select * from emp where ROWNUM 该语句的目的是想返回top 5薪水
关于oracle中rownum的使用探讨
issacp的专栏
06-15 1492
关于oracle中rownum的使用探讨一、rownum的使用比对下面两个语句的异同SELECT *FROM (SELECT  a.*            FROM torderdetail a        ORDER BY order_date DESC)WHERE ROWNUM 和SELECT  a.*    FROM torde
面向程序员的数据库访问性能优化法则
nick许赟钦的专栏
11-06 3026
<br />从蒙牛与伊利的731事件到现在QQ与360的干架，“同行是冤家”果真不错，根本原因是自身的利益。为自己获取利益是常理，但有一个前提条件就是不损害其他人利益情况下，即使总体资源有限，此多彼少，那得必须凭借自身实力通过正常手段来获取。所谓自私，个人认为自私本身并不是什么贬义，它是天性使然，亚当斯密认为人的所有决策的最终依据就是自私。大到国家政策，小到普通百姓个人行为，无一例外。 需要注意的是，因为自私产生的行为在实际行为中必须要有一定的外在约束力，其中前面提到的凭借自身实力通过正常手段为条件就是约束
oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串
05-06
2. oracle11G自带一个卸载批处理\app\Administrator\product\11.2.0\dbhome_1\deinstall\deinstall.bat 3. 运行该批处理程序将自动完成oracle卸载工作，最后手动删除\app文件夹（可能需要重启才能删除） 4. 运行...
java面试题，看我这篇就够了，前端后台应有尽有，包你通过面试
cencong863251的博客
04-03 3079
HTML&CSS部分 1、HTML中定义表格的宽度用80px和80%的区别是什么？ PX标识像素，%标识整个父标签宽度百分比 2、CSS样式定义优先级顺序是？ 内联样式最高优先权，然后是内部样式，然后才是外部样式 3、div和span的区别？ 　　DIV 和 SPAN 元素最大的特点是默认都没有对元素内的对象进行任何格式化渲染。主要用于应用样式表（共同点）。 　　两者最明显的区别在于DIV...
常见面试题
m0_59176504的博客
10-21 3021
Java基础 1.Java中内存管理及各区域内存储数据的生命周期? JVM会将申请到的内存从逻辑上划分为三个区域：堆、栈、方法区。这三个区域分别用于存储不同的数据。 堆：用于存储使用new关键字所创建的对象以及对象的属性成员变量。 栈：用于存储程序运行时在方法中声明的所有的局部变量。 方法区：用于存放类的各种信息（包括方法）都在方法区存储。 2.JVM堆内存的分代管理以及垃圾回收流程? ...
JAVA基础面试题
jinku7662_的博客
09-25 486
Java基础部分 基础部分的顺序：基本语法，类相关的语法，内部类的语法，继承相关的语法，异常的语法，线程的语法，集合的语法，io 的语法，虚拟机方面的语法。 1、一个".java"源文件中是否可以包括多个类（不是内部类）？有什么限制？ 可以有多个类，但只能有一个public的类，并且public的类名必须与文件名相一致。 2、Java有没有goto? java中的保留字，现在没有在java中使用...
Java面试-北大青鸟合肥科海学院
qq_40731742的博客
05-19 1497
内部资料，请勿外传                                                              目录一、         Java基础部分... 111、           一个".java"源文件中是否可以包括多个类（不是内部类）？有什么限制？.. 112、           说说&amp;和&amp;&amp;的区别。.. 113、      ...
数据分析高级培训：客户体验分析-课件
11-10
课程目标： 本课程旨在让学员了解什么是客户体验，客户服务体验的重要性以及常见指标，掌握客户体验的分析方法，学会客户问题的识别以及针对问题制定有效的解决方案。 课程大纲： 客户体验的概述 客户体验的分析方法 客户问题解决的方法论
【微电网潮流】分布式电源微电网潮流【Matlab仿真 7357期】.zip
11-10
CSDN Matlab武动乾坤上传的资料均有对应的代码，代码均可运行，亲测可用，适合小白； 1、代码压缩包内容 主函数：main.m； 调用函数：其他m文件；无需运行 运行结果效果图； 2、代码运行版本 Matlab 2019b；若运行有误，根据提示修改；若不会，私信博主； 3、运行操作步骤 步骤一：将所有文件放到Matlab的当前文件夹中； 步骤二：双击打开main.m文件； 步骤三：点击运行，等程序运行完得到结果； 4、仿真咨询 如需其他服务，可私信博主或扫描博客文章底部QQ名片； 4.1 博客或资源的完整代码提供 4.2 期刊或参考文献复现 4.3 Matlab程序定制 4.4 科研合作
Unity3d-lesson_EVAC-CITY.zip
11-10
Unity3d实例工程，供学习参考
2023-04-06-项目笔记 - 第三百一十三阶段 - 4.4.2.311全局变量的作用域-311 -2025.11.10
最新发布
11-10
2023-04-06-项目笔记-第三百一十三阶段-课前小分享_小分享1.坚持提交gitee 小分享2.作业中提交代码 小分享3.写代码注意代码风格 4.3.1变量的使用 4.4变量的作用域与生命周期 4.4.1局部变量的作用域 4.4.2全局变量的作用域 4.4.2.1全局变量的作用域_1 4.4.2.311局变量的作用域_311- 2024-11-10
【电机控制】基于matlab PID控制器BLDC电机控制【Matlab仿真 7412期】.zip
11-10
CSDN Matlab武动乾坤上传的资料均有对应的代码，代码均可运行，亲测可用，适合小白； 1、代码压缩包内容 主函数：main.m； 调用函数：其他m文件；无需运行 运行结果效果图； 2、代码运行版本 Matlab 2019b；若运行有误，根据提示修改；若不会，私信博主； 3、运行操作步骤 步骤一：将所有文件放到Matlab的当前文件夹中； 步骤二：双击打开main.m文件； 步骤三：点击运行，等程序运行完得到结果； 4、仿真咨询 如需其他服务，可私信博主或扫描博客文章底部QQ名片； 4.1 博客或资源的完整代码提供 4.2 期刊或参考文献复现 4.3 Matlab程序定制 4.4 科研合作
【误码率仿真】基于matlab阿拉姆提空间时间块编码STBC（含误码率）【Matlab仿真 7425期】.zip
11-10
CSDN Matlab武动乾坤上传的资料均有对应的代码，代码均可运行，亲测可用，适合小白； 1、代码压缩包内容 主函数：main.m； 调用函数：其他m文件；无需运行 运行结果效果图； 2、代码运行版本 Matlab 2019b；若运行有误，根据提示修改；若不会，私信博主； 3、运行操作步骤 步骤一：将所有文件放到Matlab的当前文件夹中； 步骤二：双击打开main.m文件； 步骤三：点击运行，等程序运行完得到结果； 4、仿真咨询 如需其他服务，可私信博主或扫描博客文章底部QQ名片； 4.1 博客或资源的完整代码提供 4.2 期刊或参考文献复现 4.3 Matlab程序定制 4.4 科研合作
在线考试-java-基于springBoot的在线考试系统设计与实现
11-10
在线考试-java-基于springBoot的在线考试系统设计与实现
基于Java+SpringBoot+Vue的电影评价系统答辩PPT.pptx
11-10
基于Java+SpringBoot+Vue的电影评价系统答辩PPT.pptx
【定位问题】chan算法、chan-Taylor算法移动基站（不同数量基站）无源定位【Matlab仿真 3148期】.zip
11-10
CSDN Matlab武动乾坤上传的资料均有对应的代码，代码均可运行，亲测可用，适合小白； 1、代码压缩包内容 主函数：main.m； 调用函数：其他m文件；无需运行 运行结果效果图； 2、代码运行版本 Matlab 2019b；若运行有误，根据提示修改；若不会，私信博主； 3、运行操作步骤 步骤一：将所有文件放到Matlab的当前文件夹中； 步骤二：双击打开main.m文件； 步骤三：点击运行，等程序运行完得到结果； 4、仿真咨询 如需其他服务，可私信博主或扫描博客文章底部QQ名片； 4.1 博客或资源的完整代码提供 4.2 期刊或参考文献复现 4.3 Matlab程序定制 4.4 科研合作
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值