UDN-企业互联网技术人气社区

板块导航

浏览  : 1260
回复  : 0

[讨论交流] SQL效率之where子句中的子查询和函数

[复制链接]
舞操的头像 楼主
发表于 2016-6-15 18:46:45 | 显示全部楼层 |阅读模式
  工作中,曾有同事问我以下sql的效率如何,这里扩展一下这个问题并进行分析,主要说明where子句中的子查询和函数执行次数及索引使用情况。

  select * from trd_fundjour a

  where oc_date = (select collect_date from hscon.sys_arg);

  首先来看一下表的数据分布情况:

1.png


  表trd_fundjour是分区表, 里面是按月分区的,以oc_date为索引。

  先看一下201605分区全扫描产生多少逻辑读。

2.png


  这里产生了88个一致读,接下来执行前面的sql。

3.png


  这里比前一次多出了7个一致读,也多出了对sys_arg表的扫描,单独查询sys_arg表看看。

4.png


  一致读刚好是7个,从这里可以看出,子查询只执行了一次,而不是针对分区中的每条记录比较一次,因为这个分区中有3279条记录。

  下面来看能否用上索引,这里强制走索引看看。

5.png


  显然,在用hint强制的情况下索引是可以走的。就本例而言,走索引比全表扫描效率会更高,但在不用hint的情况下Oracle选的却是全表扫描。虽然从表面上看是选择了较差的执行计划,其实是正常的,因为在生成执行计划的时候Oracle得不到子查询的值,无法判断全表扫描和索引扫描哪个更优,因此选择全表扫描就无可厚非了。所以,在可能的情况下应该将这些非相关子查询执行结果赋给变量,把上面的sql拆成两个语句来执行。

  下面来看一下函数的表现,hscon.f_get_collectdate的实现逻辑就是封装前面sql中的子查询,返回查询结果。

6.png


  将子查询改成函数,可以看到递归调用和一致读都大了很多,是哪里产生的呢?我们来单独查一下函数看看。

7.png


  从这里可以看出,函数调用一次就产生1个递归调用和7个一致读。3286*7(函数执行一次7个一致读)加上88(分区全扫描产生的一致读)刚好是23090,也就是说在前面的语句中函数执行了3286次!上文这一数字其实可以被拆分成3279+7两部分来看,3279是分区的记录数,7应该是和分区消除相关。我们可以得出这样的结论:针对分区中的每一条记录,函数都会调用一次进行比较。

  既然前面的写法会导致函数频繁调用,我们修改一下sql写法,先把函数生成一个结果集。

8.png


  从上图看,情况更加糟糕,但其实并不是我们的想法错了,而是Oracle太“聪明”了。从执行计划的filter中可以看出,Oracle重写了我们的sql,合并了子查询,基本上相当于又给改回了原样。下面加个hint来防止Oracle的这种自作聪明。

9.png


  情况有了很大的改善。虽说与直接使用子查询方式相比,使用函数在一致读上还是大了一些,这个差别怀疑是和分区表有关(非分区表应该没有差别,未做验证),并且实际上可以通过调整执行计划来达到无差别,这里不做详细说明。

  下面看一下索引的使用情况。

a.png


  同样地,也是可以走索引的,但Oracle不会主动使用,需要用hint强制,理由前面已经提过了。需要注意的是,这里递归调用只有8次,和分区全扫描时的表现完全不同。

  再看下面的语句。

b.png


  这应该是比较合理的执行计划了。

  综上所述,通常使用子查询的效率比用函数都要来得好一些,个别情况下用函数的效率很糟糕。

  最高效的方法就是尽可能拆分成两个语句,用变量来代替子查询和函数。

原文作者:李召雷 来源:开发者头条

相关帖子

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关于我们
联系我们
  • 电话:010-86393388
  • 邮件:udn@yonyou.com
  • 地址:北京市海淀区北清路68号
移动客户端下载
关注我们
  • 微信公众号:yonyouudn
  • 扫描右侧二维码关注我们
  • 专注企业互联网的技术社区
版权所有:用友网络科技股份有限公司82041 京ICP备05007539号-11 京公网网备安1101080209224 Powered by Discuz!
快速回复 返回列表 返回顶部