Filter消除-20190929-02

1 数据准备

DROP TABLE TEST1;

DROP TABLE TEST2;

DROP TABLE TEST3;

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;

CREATE TABLE TEST3 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(T2.RAN_VAL)

       FROM TEST2 T2

          WHERE T1.OWNER=T2.OWNER)>

            (SELECT SUM(T3.RAN_VAL)

                FROM TEST3 T3

                WHERE T1.OWNER=T3.OWNER);

2.2 写法二

 

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

FROM TEST1 T1,

      (SELECT T2.OWNER,SUM(T2.RAN_VAL) AS SUM_T2_VAL

          FROM TEST2 T2

          GROUP BY T2.OWNER) T22,

          (SELECT T3.OWNER,SUM(T3.RAN_VAL) AS SUM_T3_VAL

          FROM TEST3 T3

          GROUP BY T3.OWNER) T33

WHERE T1.OWNER=T22.OWNER

  AND T1.OWNER=T33.OWNER

  AND T22.OWNER=T33.OWNER

  AND T22.SUM_T2_VAL>T33.SUM_T3_VAL;