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 条评论
登录 后参与评论

相关文章

来自专栏学习有记

聚集索引:SQL Server 进阶 Level 3

1373
来自专栏大数据

大数据干货系列(五)-Hive总结

Hive总结 一、本质 Hive基于一个统一的查询分析层,通过SQL语句的方式对HDFS上的数据进行查 询、统计和分析。 二、四大特点** • Hive本身不存...

2419
来自专栏Java进阶架构师

「mysql优化专题」你们要的多表查询优化来啦!请查收(4)

相信这内连接,左连接什么的大家都比较熟悉了,当然还有左外连接什么的,基本用不上我就不贴出来了。这图只是让大家回忆一下,各种连接查询。 然后要告诉大家的是,需要根...

802
来自专栏Java帮帮-微信公众号-技术文章全总结

【数据库】数据库优化(SQL优化)

【数据库】数据库优化(SQL优化) sql语句优化 1.查询的模糊匹配 尽量避免在一个复杂查询里面使用 LIKE '%parm1%'—— 红色标识位置的百分...

4454
来自专栏搜云库

MySql常用30种SQL查询语句优化方法

1、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

44319
来自专栏同步博客

MySQL查询优化

     一个好的web应用,最重要的一点是有着优秀的访问性能。数据库MySQL是web应用的组成部分,也是决定其性能的重要部分。所以提升MySQL的性能至关重...

1623
来自专栏数据和云

让SQL成为一种生活方式:认识分页查询

? 1分页查询的书写方式 分页的目的就是控制输出结果集大小,将结果尽快的返回。 Oracle的分页查询语句有两种基本格式。第一种格式如下: ? 上面给出的这个...

3046
来自专栏Java3y

数据库面试题(开发者必看)

数据库常见面试题(开发者篇) ? ? 这里写图片描述 什么是存储过程?有哪些优缺点? 什么是存储过程?有哪些优缺点? 存储过程就像我们编程语言中的函数一样,封装...

5815
来自专栏Python爬虫实战

MySQL 从零开始:04 表的增删改查

在上一小节中介绍了 MySQL 数据库的一些最最最基础的入门级也是必须要掌握的10条语句,本节将继续深入学习 MySQL 的增删改查语句。本节讲的增删改查是相对...

771
来自专栏Linyb极客之路

Mysql有军规

写在前面的话: 总是在灾难发生后,才想起容灾的重要性; 总是在吃过亏后,才记得曾经有人提醒过。

1293

扫码关注云+社区