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

接：深入了解SQL性能杀手FILTER操作_PART4

1.4 数据物理分布对FILTER节点执行次数的影响

         案例如下：

--在t2表中插入1024行数据

DROP TABLE t2;

CREATE TABLE t2 AS SELECT LEVEL ID FROM dual CONNECT BY LEVEL<=1024;

--反复执行5次insert into

INSERT INTO t2 SELECT*FROM t2;

COMMIT;

对应的SQL如下：

SELECT COUNT(*)

FROM   t2

WHERE ID NOT IN

(SELECT ID

FROM(SELECT ID

FROM   t2

ORDER BY ID DESC)

WHERE ROWNUM<=100);

  以上SQL的含义很简单，也就是对T2表中的ID降序排列，查询不在前100的数据量，然而执行却比正常的慢很多（这里作为演示，只是构造少量数据，实际数据量很多，SQL执行非常慢）。执行计划如下所示：

      通过前面有关FILTER内容可以知道，表T2的ID只有1024个不同值，由于FILTER有缓存，那么这个子节点正常的执行次数应该是1024次，但是上述执行计划的执行次数却是12432次，子节点的执行次数增加N倍是导致SQL变慢的主要原因。

       解决这个问题，主要要搞清楚为什么执行次数不是1024次，在实际的应用中，表是按天分表的，每天的数据累计到前一天，执行计划未变，但是突然某一天变慢，通过分析，出现这种情况的原因，大概率是数据的分布不同。由于这里是按照ID构造HASH表，可以测试下按照ID顺序重新组织是什么情况。

DROP TABLE t3;

CREATE TABLE t3 AS SELECT*FROM t2 ORDER BY ID;--按照关联列ID重新组织顺序

执行下列语句：

SELECT COUNT(*)

FROM   t3

WHERE ID NOT IN

(SELECT ID

FROM(SELECT ID

FROM   t3

ORDER BY ID DESC)WHERE ROWNUM<=100);

执行计划如下：

      现在执行计划中子节点执行次数是1024次了，的确是数据的物理分布原因，看来FITER算法还不够完善，这点值得注意。

      如果要进一步优化上述语句，可以通过改写成NOT EXISTS避免FILTER来提高效率，改写如下：

SELECT COUNT(*)

FROM   t3 a

WHERE not exists

(SELECT 1 from

(SELECT ID

FROM(SELECT ID

FROM   t3

ORDER BY ID DESC)WHERE ROWNUM<=100) b

where a.ID = b.ID

);

执行计划如下：

通过执行计划可以看出，改写为NOT EXISTS后走HASH JOIN效率得到极大提升。

1.5 总结

      通过本文可以了解到，FILTER往往是导致SQL执行性能缓慢的元凶，主要是由于子查询未做UNNEST SUBQUERY查询转换，未UNNEST的原因有很多，比如统计信息不准，如果发现统计信息准确了还是无法UNNEST，那么要考虑SQL写法是否遇到优化器的限制或BUG，比如OR子查询。

    当然，FILTER由于内部构建HASH表，有缓存和HASH算法，对特定的查询效率可能不错，很显然是要求关联条件的重复值较少，这样子节点执行次数少，从而提高效率，所以在实际应用和优化中，还需要具体问题具体分析。