基于ORACLE的SQL优化案例_PART2

接PART1：http://blog.chinaunix.net/uid-7655508-id-5834989.html

2. FILTER性能杀手问题

  FILTER操作是执行计划中常见的操作，这种操作有两种情况：
  1.只有一个子节点，那么就是简单过滤操作。

  2.有多个子节点，那么就是类似NESTED LOOPS操作，只不过与NESTED LOOPS差别在于，   FILTER内部会构建HASH表，对于重复匹配的，不会再次进行循环查找，而是利用已有结果，提高效率。但是一旦重复匹配的较少，循环次数多，那么，FILTER操作将是严重影响性能的操作，这是经常导致性能问题的原因。

2.1 NOT IN子查询中的FILTER

对于NOT IN子查询，在11g之前经常会出现性能问题，如下例SQL所示：

  针对上面的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操作,执行计划如下：

  可以从执行计划上看到，FILTER有子节点ID=2和ID=3，并且ID=3部分出现绑定变量:B1，这是典型的NOT IN子查询未UNNEST的执行计划，性能很差。
  11g有NULL AWARE专门针对NOT IN问题进行优化（要求参数：_optimizer_squ_bottomup、_optimizer_null_aware_antijoin同时为true），如下所示：



  11g中可以走HASH JOIN RIGHT ANTI NA，其中NA就是NULL AWARE的意思，逻辑读从原先的23w降低到6105，效率提升明显。如果在11g之前，针对这种SQL的优化方式有：

1) 子查询选择条件的列增加NOT NULL约束。如上SQL需要对anti_test1和anti_test2的object_id列增加NOT NULL约束。

2) 改写SQL：对子查询选择条件的列增加IS NOT NULL条件，如下所示：

3) 改写SQL：将NOT IN改为JOIN形式。如下所示：

NOT IN子查询改为JOIN的等价形式必须是外连接+子查询表对应的选择条件IS NULL。

4) 改写SQL：将NOT IN子查询改为NOT EXISTS子查询。如下所示：

  以上四种方式的执行计划都是可以走HASH JOIN RIGHT ANTI的正确计划：


  当然,如果NOT IN子查询的确存在NULL，可能不返回结果，这种情况下是不可以用以上方式进行等价改写的，只有在NOT IN子查询肯定会返回结果，而且执行计划出现FILTER的时候才考虑以上方式进行优化。

2.2 OR子查询中的FILTER

  再来看下常见的OR与子查询连用情况，在实际优化过程中，遇到OR与子查询连用，一般都不能unnest subquery了，这样执行计划出现FILTER，可能会导致严重性能问题，OR与子查询连用有两种可能：

1）condition or subquery

2）subquery内部包含or,如in (select … from tab where condition1 or condition 2)
如下例所示：

上面SQL的子查询关联条件包含OR，执行计划如下：


 
  可以看到执行计划走FILTER，子查询表DBA_OBJECTS_B被全表驱动9999次，逻辑读10M，耗时35s,性能低下。其根本原因就是因为CBO对包含OR的子查询此处没有进行unnest，导致走了FILTER。当然，在不考虑改写的情况下，可以对DBA_OBJECTS_B的OBJECT_ID和OBJECT_NAME分别建立索引，从而避免对DBA_OBJECTS_B进行上万次的全表扫描来提高效率。如下：

 
  现在逻辑读从10M变为6341，执行时间从从35s变为0.05s，这里建立索引还是没有消除FILTER，索引被执行9999次，很显然，如果ID=3的结果行数增大，索引的扫描次数就会增多，这显然是治标不治本的方式。
  针对OR子查询无法unnest导致走FILTER的问题，一般需要通过改写，改写思路如下：

1）将OR条件改为UNION或UNION ALL。

2）根据语义改写，彻底消除OR条件。

根据以上优化指导思想，这条语句可改写为UNION形式，如下：

将OR条件改写为两条语句，使用UNION合并，最终查询COUNT(*)，执行计划如下：

  现在的执行计划两个子查询语句都可以进行UNNEST，走HASH JOIN SEMI，避免了FILTER操作，最终执行时间从原来的35s变为0.04s，逻辑读从10M减少为2550，子查询都是执行一次，也避免了建立索引，子查询效率依赖于驱动表结果行数的目的。
  下面再看一个彻底消除OR条件的改写案例：
  这里是NOT EXISTS子查询带OR条件，执行计划如下：

 
  同样，这里的执行计划走FILTER，耗时21s,逻辑读468w，效率低下。如何彻底改写消除OR条件呢？可以使用集合运算的思路，集合运算中 NOT (A OR B) 等价于 NOT A AND NOT B。则可以将OR条件改写为AND条件：

执行计划如下：

 
  将子查询OR改写为AND后，子查询可以UNNEST，走HASH JOIN RIGHT ANTI，最终执行时间从21s到0.03s,逻辑读从468w到1450，效率提升明显。

  本文通过“分页查询优化”与“FILTER性能杀手”两类常见问题，探讨CBO优化器的特性以及通过编写高质量SQL语句来达到提升性能的目的。SQL语句性能，涉及的因素很多，如统计信息、索引等，更为重要的是，熟知优化器特性，从而能够编写与CBO优化器特性相匹配的SQL语句，这样才能使用到CBO优秀的特性，保证SQL语句的执行性能。