Filter消除-20190929-01

 

1 数据准备

DROP TABLE TEST1;

DROP TABLE TEST2;

CREATE TABLE TEST1 AS SELECT D.* ,trunc(dbms_random.value(0,100)) AS RAN_VAL FROM DBA_OBJECTS D;

CREATE TABLE TEST2 AS SELECT D.* ,trunc(dbms_random.value(0,100)) AS RAN_VAL FROM DBA_OBJECTS D;

2 性能改善

2.1 写法一

SELECT T1.OWNER,T1.OBJECT_ID,T1.OBJECT_NAME

FROM TEST1 T1

WHERE (SELECT SUM(T.RAN_VAL)

       FROM TEST2 T

          WHERE T1.OWNER=T.OWNER)<1000;

2.2 写法二

SELECT T1.OWNER,T1.OBJECT_ID,T1.OBJECT_NAME

FROM TEST1 T1,

     (SELECT T.OWNER, SUM(T.RAN_VAL) AS SUM_RAN_VAL

         FROM TEST2 T

         GROUP BY T.OWNER) T2

WHERE T1.OWNER=T2.OWNER

  AND T2.SUM_RAN_VAL<1000;

2.3 分析

写法一走了Filter连接，因为where条件内有聚合，并且聚合的值基于外表test1，性能很差；

写法二走Hash连接。