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

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

前文回顾:

性能优化之查询转换 - 子查询类 将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。

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

原文发表时间:2017-02-21

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏小古哥的博客园

读书笔记-JavaScript面向对象编程(一)

前前后后大概花了两周的时间,终于把这本书大致看完了,对之前javascript高级程序设计中模糊不清的概念,有了一些新的看法和角度,整体上来说,本书还是一本比较...

2947
来自专栏WindCoder

MySQL数据库对象与应用-MySQL进阶SQL应用单元测验

1、(单选)有表 song(id,song_name,album,play_count,fav_count)分别表示歌曲的主键id、歌曲名、专辑名、播放次数和收...

301
来自专栏互联网开发者交流社区

用于 SELECT 和 WHERE 子句的函数

933
来自专栏乐沙弥的世界

PL/SQL 联合数组与嵌套表

      通常情况下,在PL/SQL中,处理单行单列的数据可以使用标量变量,而处理单行多列的数据则使用PL/SQL记录是不错的选择。单列多行数据 则由联合数组...

543
来自专栏C/C++基础

C++特性使用建议

使用引用替代指针且所有不变的引用参数必须加上const。在C 语言中,如果函数需要修改变量的值,参数必须为指针,如int foo(int *pval),在 C+...

853
来自专栏牛肉圆粉不加葱

Spark 内存管理的前世今生(下)

在《Spark 内存管理的前世今生(上)》中,我们介绍了 UnifiedMemoryManager 是如何管理内存的。然而,UnifiedMemoryManag...

662
来自专栏JetpropelledSnake

Django学习笔记之Django QuerySet的方法

1055
来自专栏JAVA高级架构

SQL索引一步到位

742
来自专栏ImportSource

JDK11中增加了一个常量池类型:CONSTANT_Dynamic

有关class file 的内容,这里不再赘述。你可以参阅此文:来自JVM的一封ClassFile介绍信。

721
来自专栏乐沙弥的世界

绑定变量及其优缺点

    绑定变量是Oracle解决硬解析的首要利器,能解决OLTP系统中library cache的过度耗用以提高性能。然刀子磨的太快,使起来锋利,却容 易折...

772

扫描关注云+社区