sql最適化:with asはunion allのサブクエリとしてテーブルを複数回スキャンしないようにする

構文:
1.構造:with別名as(共通sqlフラグメント)
2.CTE(共通式)の後にCTEを使用するSQL文を直接フォローしなければならない.そうしないと失効する.
次のようになります.1) with   cr as   (select   CountryRegionCode  from   person.CountryRegion  where   Name   like   'C%'   )    select   *  from   person.StateProvince  where   CountryRegionCode  in   ( select   *  from   cr)  2) with   cr as   (select   CountryRegionCode  from   person.CountryRegion  where   Name   like   'C%'   )    select   *  from   person.StateProvince      select   *  from   person.StateProvince  where   CountryRegionCode  in   ( select   *  from   cr)
3. CTE CTE， with ， CTE （,） , : with   cte1  as   (select   *  from   table1  where   name   like   'abc%' ),  cte2  as   (select   *  from   table2  where   id > 20),  cte3  as   ( select   *  from   table3  where   price  select   a.*  from    cte1 a, cte2 b, cte3 c  where   a.id = b.id  and   a.id = c.id 4. CTE ， CTE SQL CTE， ， SQL ， : --  table1 with   table1 as   ( select   *  from   persons  where   age  select   *  from   table1   --   table1 select   *  from   table1   --   table1
5.  CTE  ， WITH   CTE。
6. CTE_query_definition  ： 1）ORDER   BY （ TOP   ）2）COMPUTE  COMPUTE BY 3）INTO 4） OPTION   5）FOR   XML 6）FOR   BROWSE 7.バッチの一部に属する文にCTEを使用する場合、その前の文は次のようにセミコロンで終わる必要があります.declare   @s nvarchar(3)  set   @s =  'C%'   ;   --  with   t_tree as   ( select   CountryRegionCode  from   person.CountryRegion  where   Name   like   @s)  select   *  from   person.StateProvince  where   CountryRegionCode  in   ( select   *  from   t_tree)
 
UNIOALLと比較:
1.元のsql(union all):

SELECT
  0                          REFUNDSUCCESSNUMBER,
  0                          REFUNDFAILNUMBER,
  COUNT(ID)                  REFUNDNUMBER,
  SUM(NVL(REFUND_AMOUNT, 0)) REFUNDAMOUNT,
  SUM(NVL(POINT, 0))         REFUNDPOINTS,
  CHANNEL,
  GATEWAY_CODE               GATEWAY
FROM PP_PAY
WHERE TRADE_TYPE = 1
      AND CREATE_TIME >= TO_DATE('20180819', 'YYYYMMDD') AND
      CREATE_TIME < TO_DATE('20180820', 'YYYYMMDD')
GROUP BY CHANNEL, GATEWAY_CODE
UNION ALL SELECT
            COUNT(ID)    REFUNDSUCCESSNUMBER,
            0            REFUNDFAILNUMBER,
            0            REFUNDNUMBER,
            0            REFUNDAMOUNT,
            0            REFUNDPOINTS,
            CHANNEL,
            GATEWAY_CODE GATEWAY
          FROM PP_PAY
          WHERE TRADE_TYPE = 1
                AND REFUND_STATUS = 1 AND
                CREATE_TIME >= TO_DATE('20180819', 'YYYYMMDD') AND CREATE_TIME < TO_DATE('20180820', 'YYYYMMDD')
          GROUP BY CHANNEL, GATEWAY_CODE
UNION ALL SELECT
            0            REFUNDSUCCESSNUMBER,
            COUNT(ID)    REFUNDFAILNUMBER,
            0            REFUNDNUMBER,
            0            REFUNDAMOUNT,
            0            REFUNDPOINTS,
            CHANNEL,
            GATEWAY_CODE GATEWAY
          FROM PP_PAY
          WHERE TRADE_TYPE = 1
                AND REFUND_STATUS >= 1 AND REFUND_STATUS <= 1 AND
                CREATE_TIME >= TO_DATE('20180819', 'YYYYMMDD') AND CREATE_TIME < TO_DATE('20180820', 'YYYYMMDD')
          GROUP BY CHANNEL, GATEWAY_CODE

2.with asで置換後:

with refund as (select
                  0                     REFUNDSUCCESSNUMBER,
                  0                     REFUNDFAILNUMBER,
                  1                     REFUNDNUMBER,
                  NVL(REFUND_AMOUNT, 0) REFUNDAMOUNT,
                  NVL(POINT, 0)         POINT,
                  CHANNEL,
                  GATEWAY_CODE,
                  REFUND_STATUS
                from ULEAPP_PAYMENT.PP_PAY
                WHERE TRADE_TYPE = 1
                      AND CREATE_TIME >= TO_DATE('20180819', 'YYYYMMDD') AND
                      CREATE_TIME < TO_DATE('20180820', 'YYYYMMDD'))


SELECT
  0                         REFUNDSUCCESSNUMBER,
  0                         REFUNDFAILNUMBER,
  COUNT(REFUNDNUMBER)       REFUNDNUMBER,
  SUM(NVL(REFUNDAMOUNT, 0)) REFUNDAMOUNT,
  SUM(NVL(POINT, 0))        REFUNDPOINTS,
  CHANNEL,
  GATEWAY_CODE
FROM refund
GROUP BY CHANNEL, GATEWAY_CODE
UNION ALL SELECT
            COUNT(REFUNDSUCCESSNUMBER) REFUNDSUCCESSNUMBER,
            0                          REFUNDFAILNUMBER,
            0                          REFUNDNUMBER,
            0                          REFUNDAMOUNT,
            0                          REFUNDPOINTS,
            CHANNEL,
            GATEWAY_CODE
          FROM refund
          WHERE REFUND_STATUS = 1
          GROUP BY CHANNEL, GATEWAY_CODE
UNION ALL SELECT
            0                       REFUNDSUCCESSNUMBER,
            COUNT(REFUNDFAILNUMBER) REFUNDFAILNUMBER,
            0                       REFUNDNUMBER,
            0                       REFUNDAMOUNT,
            0                       REFUNDPOINTS,
            CHANNEL,
            GATEWAY_CODE
          FROM refund
          WHERE REFUND_STATUS >= 1 and REFUND_STATUS <= 1
          GROUP BY CHANNEL, GATEWAY_CODE

この表開発ライブラリデータ量10 w条、CREATE_TIMEインデックスが有効になり、置換後のクエリ速度が1倍以上に向上します.