深入了解SQL性能杀手FILTER操作_PART1

FILTER操作是执行计划中常见的操作，这种操作有两种情况：

1.       只有一个子节点，那么就是简单过滤操作。（不是本文重点）

2.       有多个子节点，那么就是类似NESTED LOOPS操作，只不过与NESTED LOOPS差别在于，FILTER内部会构建HASH表（有缓存提高效率，缓存的bucket数目是1024个），对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，也就是类似NESTED LOOPS，那么这种FILTER操作将是严重影响性能的操作，可能你的SQL几天都执行不完了。（本文讨论重点）

下面看看各种情况下的FILTER操作：

单子节点：


很显然ID=1的FILTER操作只有一个子节点ID=2，这种情况下的FILTER操作也就是单纯的过滤操作。

多子节点：

        FILTER多子节点往往就是性能杀手，主要出现在子查询无法UNNEST查询转换，经常遇到的情况就是NOT IN子查询、子查询和OR连用、复杂子查询、CBO未做子查询UNNEST等情况。

1.1 NOT IN子查询中的FILTER

先来看下NOT IN情况：

针对上面的NOT IN子查询，如果子查询object_id有NULL存在，则整个查询都不会有结果，在11g之前，如果主表和子表的object_id未同时有NOT NULL约束，或都未加IS NOT NULL限制，则ORACLE会走FILTER。11g有新的ANTI NA（NULL AWARE）优化，可以对子查询进行UNNEST，从而提高效率。

 

对于未UNNEST的子查询，走了FILTER，有至少2个子节点，执行计划还有个特点就是Predicate谓词部分有：B1这种类似绑定变量的东西，内部操作走类似NESTED LOOPS操作。

 

11g有NULL AWARE专门针对NOT IN问题进行优化，如下所示：

通过NULL AWARE操作，对无法UNNEST的NOT IN子查询可以转换成JOIN形式，这样效率就大幅度提升了。如果在11g之前，遇到NOT IN无法UNNEST，那该怎么做呢？

1）  将NOT IN部分的匹配条件，针对本例就是ANTI_TEST1.object_id和ANTI_TEST2.object_id均设为NOT NULL约束。

2）  不改NOT NULL约束，则需要两个object_id均增加IS NOT NULL条件。

3）  改为NOT EXISTS。

4）  改为ANTI JOIN形式。

以上四种方式，大部分情况下均能达到让优化器走JOIN的目的，如下所示：

以上写法执行计划都是一样的，如下所示：

说白了，unnest subquery就是转换成JOIN形式，如果能转换成JOIN就可以利用高效JOIN特性来提高操作效率，不能转换就走FILTER，可能影响效率，11g的NULL AWARE从执行计划里可以看出，还是有点区别，没有走INDEX FULL SCAN扫描，因为没有条件让ORACLE知道object_id可能存在NULL，所以也就走不了索引了。

下一篇：深入了解SQL性能杀手FILTER操作_PART2