利用分析函数改写范围判断自关联查询

最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。

分析、定位数据库的主要负载是这条语句引起的过程相对简单,通过AWR报告就可以比较容易的完成定位,这里就不赘述了。

现在直接看一下这个导致性能问题的SQL语句,其对应的SQL REPORT统计如下:

从SQL的性能指标上看,其单次执行需要6分钟左右,处理5万多条记录,逻辑度只有756,主要消耗时间在CPU上。而这里就存在疑点,逻辑读如此之低,而CPU时间花费又如此之高,那么这些CPU都消耗在哪里呢?当然这个问通过SQL的统计信息中是找不到答案的,我们下面关注SQL的执行计划:

从执行计划看,Oracle选择了HASH JOIN ANTI,JOIN的两张表都是T_NUM,且都采用了全表扫描,并未选择索引。仅靠执行计划也只等得到上面的结论,至于为什么不选择索引,以及为什么执行时间过长,还需要进一步的分析。

将原SQL进行简单脱密改写后, SQL文本类似如下:

SELECT BEGIN, END, ROWID, LENGTH(BEGIN) FROM T_NUM A WHERE NOT EXISTS ( SELECT 1 FROM T_NUM B WHERE B.BEGIN <= A.BEGIN AND B.END >= A.END AND B.ROWID != A.ROWID AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN));

如果分析 SQL语句,会发现这是一个自关联语句,在BEGIN字段长度相等的前提下,想要找到那些不存在 BEGIN比当前记录 BEGIN小且 END比当前记录 END大的记录。

简单一点说,表中的记录表示的是由 BEGIN开始到 END截至的范围,那么当前想要获取的结果是找出哪些没有范围所包含的范围。需要注意的是,对于当前的 SQL逻辑,如果存在两条范围完全相同的记录,那么最终这两条记录都会被舍弃。

业务的逻辑并不是特别复杂,但是要解决一条记录与其他记录进行比较,多半采用的方法是自关联,而在这个自关联中,既有大于等于又有小于等于,还有不等于,仅有的一个等于的关联条件,来自范围段 BEGIN的长度的比较。

显而易见的是,如果是范围段本身的比较,其选择度一般还是不错的,但是如果只是比较其长度,那么无疑容易产生大量的重复,比如在这个例子中:

SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc; LENGTH(BEGIN) COUNT(*) ————- ———- 12 22096 11 9011 13 8999 14 8186 16 49 9 45 8 41 7 27

大量重复的数据出现在长度为11到14的范围上,在这种情况下,仅有的一个等值判断条件 LENGTH(BEGIN)是非常低效的,这时一条记录根据这个等值条件会关联到近万条记录,甚至关联到两万多条记录,显然大量的实践消耗在低效的连接过程中。

再来看一下具体的 SQL语句,会发现几乎没有办法建立索引,因为LENGTH(BEGIN)的选择度非常查,而其他的条件都是不等查询,选择度也不会好,即使建立索引,强制执行选择索引,效率也不会好。

那么如果想要继续优化这个SQL,就只剩下一个办法,那就是SQL的改写。对于自关联查询而言,最佳的改写方法是利用分析函数,其强大的行级处理能力,可以在一次扫描过程中获得一条记录与其他记录的关系,从而消除了自关联的必要性。

SQL改写结果如下:

SELECT BEGIN, OLDEND END, LENGTH(BEGIN) FROM ( SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN, ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN FROM ( SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END FROM T_NUM ) ) WHERE RN = 1 AND CN = 1;

简单的说,内层的分析函数MAX用来根据BEGIN从小到大,END从大到小的条件,确定每个范围对应的最大的END的值。而外层的两个分析函数,COUNT用来去掉完全重复的记录,而ROW_NUMBER用来获取范围最大的记录(也就是没有被其他记录的范围所涵盖)。

改写后,这个 SQL避免了自关联,也就不存在关联条件重复值过高的性能隐患了。在模拟环境中,性能对比如下:

SQL> SELECT BEGIN, END, ROWID, LENGTH(BEGIN) 2 FROM T_NUM A 3 WHERE NOT EXISTS ( 4 SELECT 1 5 FROM T_NUM B 6 WHERE B.BEGIN <= A.BEGIN 7 AND B.END >= A.END 8 AND B.ROWID != A.ROWID 9 AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN)) 10 ; 48344 rows selected. Elapsed: 00:00:57.68 Execution Plan ———————————————————- Plan hash value: 2540751655 ———————————————————————————— | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ———————————————————————————— | 0 | SELECT STATEMENT | | 48454 | 1703K| | 275 (1)| 00:00:04 | |* 1 | HASH JOIN ANTI | | 48454 | 1703K| 1424K| 275 (1)| 00:00:04 | | 2 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 | ———————————————————————————— Predicate Information (identified by operation id): ————————————————— 1 – access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”))) filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND “B”.ROWID<>”A”.ROWID) Statistics ———————————————————- 0 recursive calls 0 db block gets 404 consistent gets 0 physical reads 0 redo size 2315794 bytes sent via SQL*Net to client 35966 bytes received via SQL*Net from client 3224 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 48344 rows processed SQL> SELECT BEGIN, OLDEND END, LENGTH(BEGIN) 2 FROM ( 3 SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN, 4 ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN 5 FROM 6 ( 7 SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END 8 FROM T_NUM 9 ) 10 ) 11 WHERE RN = 1 12 AND CN = 1; 48344 rows selected. Elapsed: 00:00:00.72 Execution Plan ———————————————————- Plan hash value: 1546715670 —————————————————————————————— | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | —————————————————————————————— | 0 | SELECT STATEMENT | | 48454 | 2460K| | 800 (1)| 00:00:10 | |* 1 | VIEW | | 48454 | 2460K| | 800 (1)| 00:00:10 | |* 2 | WINDOW SORT PUSHED RANK| | 48454 | 1845K| 2480K| 800 (1)| 00:00:10 | | 3 | WINDOW BUFFER | | 48454 | 1845K| | 800 (1)| 00:00:10 | | 4 | VIEW | | 48454 | 1845K| | 311 (1)| 00:00:04 | | 5 | WINDOW SORT | | 48454 | 662K| 1152K| 311 (1)| 00:00:04 | | 6 | TABLE ACCESS FULL | T_NUM | 48454 | 662K| | 68 (0)| 00:00:01 | —————————————————————————————— Predicate Information (identified by operation id): ————————————————— 1 – filter(“RN”=1 AND “CN”=1) 2 – filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END” ORDER BY “BEGIN”)<=1) Statistics ———————————————————- 0 recursive calls 0 db block gets 202 consistent gets 0 physical reads 0 redo size 1493879 bytes sent via SQL*Net to client 35966 bytes received via SQL*Net from client 3224 SQL*Net roundtrips to/from client 3 sorts (memory) 0 sorts (disk) 48344 rows processed

原SQL运行时间接近1分钟,而改写后的SQL语句只需要0.72秒,执行时间变为原本的1/80,逻辑读减少一半。

原创:杨廷琨。

投稿:有投稿意向技术人请在公众号对话框留言。

转载:意向文章下方留言。

更多精彩请关注 “数据和云” 公众号 。

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2018-10-14

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序员宝库

电商系统设计之订单

用户交易将经历一段艰辛的历程,一般用户感觉不到,实际程序是经历了一段生死离别。具体付款流程如下:

52230
来自专栏杨建荣的学习笔记

sql语句的简化(r2第7天)

今天碰到一个sql语句简化的问题,虽然也不复杂,但是也值得从中学习一些东西 SELECT MOD(((SELECT TO_NUMBER(TO_CHAR(LOG...

28260
来自专栏Java Web

模仿天猫实战【SSM版】——项目起步

39140
来自专栏乐沙弥的世界

使用exchange方式切换普通表到分区表

      随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区...

8310
来自专栏Albert陈凯

理解SQL原理SQL调优你必须知道的10条铁律

原文地址: http://www.nowamagic.net/librarys/veda/detail/1502 我们做软件开发的,大部分人都离不开跟数据库打...

39750
来自专栏杨建荣的学习笔记

merge语句导致的CPU使用率过高的优化(二) (r7笔记第9天)

之前分享过一篇关于merge语句导致的CPU使用率过高优化的案例。http://blog.itpub.net/23718752/viewspace-181947...

32840
来自专栏自由而无用的灵魂的碎碎念

oracle 10g 手动创建scott(tiger) schema

转自:http://cnhtm.itpub.net/post/39970/496967

13130
来自专栏芋道源码1024

电商系统设计之订单

1. 前言2. 付款2.1 成功2.2 人祸2.4 天灾2.4 注释2.5 表结构2.5.1 交易表2.5.2 支付记录表2.5.3 订单表3. 运输4. 收货...

18430
来自专栏Grace development

电商系统设计之订单

用户交易将经历一段艰辛的历程,一般用户感觉不到,实际程序是经历了一段生死离别。具体付款流程如下

19820
来自专栏「3306 Pai」社区

NOT NULL列用IS NULL也能查到数据?

有没有觉得很奇怪,为什么查到了2条 dt 列值为 '0000-00-00 00:00:00' 的记录?

10500

扫码关注云+社区

领取腾讯云代金券