暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Oracle性能优化-子查询到特殊问题

韩锋 2017-02-20
325

编辑手记:前面我们介绍常用的子查询优化方法,但总有一些情况时在规律之外。谨慎处理方能不掉坑。

前文回顾:

性能优化之查询转换 - 子查询类

将SQL优化做到极致 - 子查询优化


作者简介:

韩锋

精通包括Oracle、MySQL、informix等多种关系型数据库,有丰富的数据库架构设计开发经验。就职于宜信。


1、空值问题

首先值得关注的问题是,在NOT IN子查询中,如果子查询列有空值存在,则整个查询都不会有结果。这可能是跟主观逻辑上感觉不同,但数据库就是这样处理的。因此,在开发过程中,需要注意这一点。看个例子吧。

SQL> select * from dual where 2 not in (select 1 from dual);

D

-

X

SQL> select * from dual where 2 not in (select 1 from dual union all select null from dual);

no rows selected

显然,第二条语句在印象中应该会返回记录,但实际情况就是没有。


第二个值得关注的是,在11g之前,如果主表和子表的对应列未同时有NOT NULL约束,或都未加IS NOT NULL限制,则Oracle会走FILTER。11g有新的ANTI NA(NULL AWARE)优化,可以正常对子查询进行UNNEST。


注意此时的关联字段OBJECT_ID,是可为空的。示例模拟了11g以前的情况,此时走了最原始的FILTER


在确定子查询列object_id不会有NULL存在的情况下,又不想通过增加NOT NULL约束来优化,可以通过上面方式进行改写


在11g的默认情况下,走的就是ANTI NA(NA=NULL AWARE)


2、OR问题

对含有OR的Anti Join或Semi Join,注意有FILTER的情况。如果FILTER影响效率,可以通过改写为UNION、UNION ALL、AND等逻辑条件进行优化。优化的关键要看FILTER满足条件的次数。看下面的示例。

//上例中包含有OR条件的Semi Join,执行计划中使用了FILTER过滤,整个逻辑读消耗为69。


//下面通过改写,看看效果如何?

//将上面的OR连接修改为UNION,消除了FILTER。从成本或逻辑读等角度来看,整个逻辑读为30,较前面的69大大降低了


3、[NOT] IN/EXISTS问题

下面看两个关于[NOT] IN/EXISTS的问题。

1. IN/EXISTS

从原理来讲,IN操作是先进行子查询操作,再进行主查询操作。EXISTS操作是先进行主查询操作,再到子查询中进行过滤。


  • IN操作相当于对inner table执行一个带有distinct的子查询语句,然后得到的查询结果集再与outer table进行连接,当然连接的方式和索引的使用仍然等同于普通的两表连接。

  • EXISTS操作相当于对outer table进行全表扫描,用从中检索到的每一行与inner table做循环匹配输出相应的符合条件的结果,其主要开销是对outer table的全表扫描(full scan),而连接方式是nested loop方式。


当子查询表数据量巨大且索引情况不好(大量重复值等),则不宜使用产生对子查询的distinct检索而导致系统开支巨大的IN操作;反之当外部表数据量巨大(不受索引影响)而子查询表数据较少且索引良好时,不宜使用引起外部表全表扫描的EXISTS操作。如果限制性强的条件在子查询,一般建议使用IN操作。如果限制性强的条件在主查询,则使用EXISTS操作。


2. NOT IN/EXISTS

在子查询中,NOT IN子句将执行一个内部的排序和合并。无论在哪种情况下,NOT IN都是最低效的(因为它对子查询中的表执行了一个全表遍历)。


为了避免使用NOT IN,可以把它改写成外连接(Outer Joins)或NOT EXISTS。

加入"云和恩墨大讲堂"微信群,参与讨论学习

搜索 盖国强(Eygle) :eyygle,或者扫描下面二维码,备注:云和恩墨大讲堂,即可入群。每周与千人共享免费技术分享,与讲师在线讨论。


关注微信,获得后续精彩分享

最后修改时间:2020-05-08 00:20:44
文章转载自韩锋,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论