Oracle 使用 with as 优化重复查询

我们有时写的sql 会多次查询和使用相同的结果集，而事实上每次查询都会消耗资源和降低sql的整体查询效率，特别是对大量数据表，耗时特别长。

使用with as 将想要查询的数据集保存到一张虚表中，数据查询均从这张虚拟表（视图）中查询获得，减少对数据库的直接访问。

 1、该sql性能太低，其中有两张表（数据量均比较大）数据拼接操作，没有先过滤掉不需要的数据，全表扫描两张大表然后拼接，然后过滤
SELECT A.CUST_ID,
               A.CUST_CODE,
               A.CUST_NAME,
               A.CREATED_DATE,
               A.CUST_TYPE,
               C.CERT_TYPE_NAME,
               B.CERT_NBR,
               D.STATE,
               D.STATE_DATE,
               E.MDN_STATE MSISDN_STATE
          FROM CUST A,
               CERT B,
               CERT_TYPE C,
               ECONET_CUST_STATE D,
               (SELECT G.CUST_ID, WM_CONCAT(G.MDN_STATE) MDN_STATE
                  FROM (SELECT *
                          FROM (SELECT F.CUST_ID,
                                       F.MDN_STATE,
                                       ROW_NUMBER() OVER(PARTITION BY F.CUST_ID ORDER BY F.MDN_STATE) TOP
                                  FROM (SELECT DISTINCT S.CUST_ID,
                                                        S.PREFIX || S.ACC_NBR || '|' ||
                                                        P.PROD_STATE MDN_STATE
                                          FROM SUBS S, PROD P
                                         WHERE S.SUBS_ID = P.PROD_ID) F)
                         WHERE TOP <= 5) G
                 WHERE G.CUST_ID IS NOT NULL
                 GROUP BY G.CUST_ID) E
         WHERE 1 = 1
           AND A.CUST_ID = D.CUST_ID
           AND A.CERT_ID = B.CERT_ID(+)
           AND B.CERT_TYPE_ID = C.CERT_TYPE_ID(+)
           AND A.CUST_ID = E.CUST_ID(+)
           AND D.STATE IN ('F', 'A', 'D')

2、第一次优化，先过滤掉数据然后拼接，然而部分数据由于拼接元素为null,通过条件过滤的话会漏掉，因此要单独查询 然后union（性能有所提高）

SELECT G.CUST_ID,
            G.CUST_CODE,
            G.CUST_NAME,G.CREATED_DATE,G.CUST_TYPE,G.CERT_TYPE_NAME,
            G.CERT_NBR,G.STATE,G.STATE_DATE, WM_CONCAT(G.MDN_STATE) MDN_STATE
    FROM (SELECT *
            FROM (SELECT F.CUST_ID,
            F.CUST_CODE,
            F.CUST_NAME,F.CREATED_DATE,F.CUST_TYPE,F.CERT_TYPE_NAME,
            F.CERT_NBR,F.STATE,F.STATE_DATE,
                         F.MDN_STATE,
                         ROW_NUMBER() OVER(PARTITION BY F.CUST_ID ORDER BY F.MDN_STATE) TOP
                    FROM (  SELECT A.CUST_ID,
                                   A.CUST_CODE,
                                   A.CUST_NAME,
                                   A.CREATED_DATE,
                                   A.CUST_TYPE,
                                   C.CERT_TYPE_NAME,
                                   B.CERT_NBR,
                                   D.STATE,
                                   D.STATE_DATE,S.PREFIX || S.ACC_NBR || '|' ||P.PROD_STATE MDN_STATE
                            FROM SUBS S, PROD P, CUST A,CERT B,CERT_TYPE C, ECONET_CUST_STATE D
                            WHERE S.SUBS_ID = P.PROD_ID
                            AND B.CERT_TYPE_ID = C.CERT_TYPE_ID(+)
                            AND A.CERT_ID = B.CERT_ID(+)
                            AND A.CUST_ID  = S.CUST_ID(+)
                            AND A.CUST_ID = D.CUST_ID
                            AND D.STATE IN ('F', 'A', 'D')) F)
           WHERE TOP <= 5) G
   WHERE G.CUST_ID IS NOT NULL
   GROUP BY G.CUST_ID,
            G.CUST_CODE,
            G.CUST_NAME,G.CREATED_DATE,G.CUST_TYPE,G.CERT_TYPE_NAME,
            G.CERT_NBR,G.STATE,G.STATE_DATE
    UNION ALL
    SELECT A.CUST_ID,
                     A.CUST_CODE,
                     A.CUST_NAME,
                     A.CREATED_DATE,
                     A.CUST_TYPE,
                     C.CERT_TYPE_NAME,
                     B.CERT_NBR,
                     D.STATE,
                     D.STATE_DATE,WM_CONCAT(S.CUST_ID) MDN_STATE
                FROM CUST A,CERT B,CERT_TYPE C, ECONET_CUST_STATE D,SUBS S
                WHERE 1=1
                AND B.CERT_TYPE_ID = C.CERT_TYPE_ID(+)
                AND A.CERT_ID = B.CERT_ID(+)
                AND A.CUST_ID = D.CUST_ID
                AND D.CUST_ID  = S.CUST_ID(+)
                AND D.STATE IN ('F', 'A', 'D')
                AND S.CUST_ID IS NULL
GROUP BY A.CUST_ID,
                     A.CUST_CODE,
                     A.CUST_NAME,
                     A.CREATED_DATE,
                     A.CUST_TYPE,
                     C.CERT_TYPE_NAME,
                     B.CERT_NBR,
                     D.STATE,
                     D.STATE_DATE

3、第二次优化（第一次优化 使用了union all,其中SUBS S,CUST A,CERT B,CERT_TYPE C, ECONET_CUST_STATE D 这些个关联查询在union all 两边 都做了，如果只查询一次的话，性能会更好点，因此抽出来，用with  as 先做一次查询 保存SCCCE 这张虚表中  这样只会对subs 这张数据量很大的表做一次全表扫描）

 WITH SCCCE AS (SELECT A.CUST_ID, 
                 S.SUBS_ID,                                          
                 A.CUST_CODE,
                 A.CUST_NAME,
                 A.CREATED_DATE,
                 A.CUST_TYPE,
                 C.CERT_TYPE_NAME,
                 B.CERT_NBR,
                 D.STATE,
                 D.STATE_DATE,
                 S.PREFIX || S.ACC_NBR MISDN            
          FROM SUBS S,CUST A,CERT B,CERT_TYPE C, ECONET_CUST_STATE D
          WHERE B.CERT_TYPE_ID = C.CERT_TYPE_ID(+)
          AND A.CERT_ID = B.CERT_ID(+)
          AND A.CUST_ID  = S.CUST_ID(+)
          AND A.CUST_ID = D.CUST_ID
          AND D.STATE IN ('F', 'A', 'D'))
          
   SELECT G.CUST_ID,
          G.CUST_CODE,
          G.CUST_NAME,G.CREATED_DATE,G.CUST_TYPE,G.CERT_TYPE_NAME,
          G.CERT_NBR,G.STATE,G.STATE_DATE, WM_CONCAT(G.MDN_STATE) MDN_STATE
      FROM (SELECT * 
            FROM (SELECT F.CUST_ID,
                          F.CUST_CODE,
                          F.CUST_NAME,F.CREATED_DATE,F.CUST_TYPE,F.CERT_TYPE_NAME,
                          F.CERT_NBR,F.STATE,F.STATE_DATE,
                          F.MDN_STATE,
                          ROW_NUMBER() OVER(PARTITION BY F.CUST_ID ORDER BY F.MDN_STATE) TOP
                  FROM (SELECT SC.CUST_ID,
                             SC.CUST_CODE,
                             SC.CUST_NAME,
                             SC.CREATED_DATE,
                             SC.CUST_TYPE,
                             SC.CERT_TYPE_NAME,
                             SC.CERT_NBR,
                             SC.STATE,
                             SC.STATE_DATE,
                             SC.MISDN|| '|' ||P.PROD_STATE MDN_STATE 
                       FROM PROD P,SCCCE SC
                       WHERE SC.SUBS_ID = P.PROD_ID)F)
           WHERE TOP <= 5) G
     WHERE G.CUST_ID IS NOT NULL
     GROUP BY G.CUST_ID,
              G.CUST_CODE,
              G.CUST_NAME,G.CREATED_DATE,G.CUST_TYPE,G.CERT_TYPE_NAME,
              G.CERT_NBR,G.STATE,G.STATE_DATE
     UNION ALL
     SELECT SC.CUST_ID,
          SC.CUST_CODE,
          SC.CUST_NAME,
          SC.CREATED_DATE,
          SC.CUST_TYPE,
          SC.CERT_TYPE_NAME,
          SC.CERT_NBR,
          SC.STATE,
          SC.STATE_DATE,WM_CONCAT( SC.MISDN) MDN_STATE
    FROM SCCCE SC
    WHERE 1=1           
    AND  SC.SUBS_ID IS NULL
    GROUP BY SC.CUST_ID,
           SC.CUST_CODE,
           SC.CUST_NAME,
           SC.CREATED_DATE,
           SC.CUST_TYPE,
           SC.CERT_TYPE_NAME,
           SC.CERT_NBR,
           SC.STATE,
           SC.STATE_DATE