Oracle性能优化-子查询到特殊问题
编辑手记：前面我们介绍常用的子查询优化方法，但总有一些情况时在规律之外。谨慎处理方能不掉坑。
前文回顾：
作者简介：
韩锋
精通包括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。
