Filter消除-20190929-03

1 数据准备

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

2 性能改善

2.1 写法一

SELECT OWNER, COUNT(*)

  FROM TEST1

 GROUP BY OWNER

HAVING COUNT(*) + 1

         > ALL (SELECT COUNT(*)

                  FROM TEST1

                 GROUP BY OWNER);

执行计划走Filter连接，执行时间24秒左右。

2.2 写法二

WITH TMP AS

(SELECT T1.OWNER, COUNT(*) CNT

 FROM TEST1 T1

 GROUP BY T1.OWNER)

SELECT *

FROM TMP T1

WHERE NOT EXISTS (

  SELECT 1 FROM TMP T2

WHERE (T1.CNT+1)<=T2.CNT);

用with+not exists改写，SQL走了Merge Join反连接，一秒左右出结果。

2.3 写法三

WITH TMP AS

(SELECT T1.OWNER, COUNT(*) CNT

 FROM TEST1 T1

 GROUP BY T1.OWNER)

SELECT *

FROM TMP T1

   LEFT JOIN TMP T2

     ON (T1.CNT+1)<=T2.CNT

WHERE T2.OWNER IS NULL;

此写法与2.2类似，not exists改写为Left Join的方式，执行时间也是1秒左右。

2.4 写法四

SELECT TT.OWNER,TT.CNT

FROM (SELECT T.OWNER,T.CNT,dense_rank() over(ORDER BY T.CNT DESC) AS RNK

      FROM (SELECT T1.OWNER, COUNT(*) CNT

            FROM TEST1 T1

            GROUP BY T1.OWNER) T) TT

WHERE TT.RNK=1;

利用分析函数改写，执行时间1秒左右。