用with改写优化sql

最新推荐文章于 2021-03-29 18:03:40 发布
最新推荐文章于 2021-03-29 18:03:40 发布
阅读量1.3k 收藏
点赞数
分类专栏： 改写优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jgmydsai/article/details/14644821
版权

网友发来一案例，sql如下，表名及部分字段名作了处理，这个sql要跑20分钟。因为本次改写与plan无关，所以plan也就不发了

WITH SAWITH0 AS
 (select distinct T_A.T_A_SHORT_NAME as c1,
                  cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c2,
                  T_B.CAL_MONTH_NAME as c3,
                  T_A.EFFECTIVE_MONTH_ACT as c4,
                  T_A.EFFECTIVE_MONTH_BGT as c5,
                  T_A.T_A_CODE as c6,
                  T_A.T_A_COOPER as c7,
                  T_B.CAL_MONTH_ID as c8
    from T_B T_B,
         T_C T_C,
         (SELECT CHAIN_T_A_SK,
                 HEADER_ID,
                 OFFICE_CODE,
                 T_A_CODE,
                 T_A_SHORT_NAME,
                 T_A_NAME,
                 T_A_ADDRESS,
                 T_A_PHONE,
                 T_A_PROPERITY,
                 COMPANY_PROPERTY,
                 T_A_AREA,
                 PROVINCE_SEQ,
                 T_A_PROVINCE,
                 CITY_SEQ,
                 T_A_CITY,
                 T_A_COUNTY,
                 T_A_COOPER,
                 T_A_BRAND,
                 T_A_NATURE,
                 LEASE_BUILDING_AREA,
                 CONS_BUILDING_AREA,
                 ROOM_QUANTITY,
                 SEATS_QUANTITY,
                 MEAL_QUANTITY,
                 BUSINESS_LICENSE_DATE,
                 SIGN_DATE,
                 START_DATE,
                 END_DATE,
                 case
                   when SIGN_DATE >= PRE_BUSINESS_DATE then
                    SIGN_DATE
                   else
                    PRE_BUSINESS_DATE
                 end PRE_BUSINESS_DATE,
                 BUSINESS_DATE,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATION_DATE,
                 CREATED_BY,
                 LAST_UPDATE_LOGIN,
                 T_A_SEGMENT,
                 T_A_AREA_1,
                 T_A_TYPE,
                 ORG_CODE,
                 BUSINESS_LICENSE_END_DATE,
                 PMS_CODE,
                 ORG_ID,
                 PRO_HANDOVER_DATE,
                 FOREIGN_RENT_AREA,
                 SIGN_YEAR,
                 COMPANY_NAME,
                 REMOVE_DATE,
                 EFFECTIVE_DATE,
                 EXPIRATION_DATE,
                 CURRENT_FLAG,
                 ETL_DATE,
                 OLD_T_A_NAME,
                 BRAND_CODE,
                 EQUITY_NAME,
                 (EQUITY_RATE / 100.00) AS EQUITY_RATE,
                 nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE,
                 floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN,
                 floor(business_date - pre_business_date) AS DIFF_B_PRE,
                 floor(business_date - sign_date) AS DIFF_B_SIGN,
                 CASE
                   WHEN TERMINATION_DATE IS NULL THEN
                    'N'
                   ELSE
                    'Y'
                 END IS_FLAG,
                 TERMINATION_DATE,
                 to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act,
                 to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act,
                 to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt,
                 to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt
            FROM T_A,
                 (select t.T_A_code u_T_A_code,
                         max(t.effective_date) u_effective_date
                    from T_A t
                   group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t
           WHERE T_A.T_A_code = t.u_T_A_code
             and T_A.effective_date = t.u_effective_date) T_A
   where (T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and
         T_C.PERIOD_MON >= '2013-05' and
         (substr(T_C.T_A_CODE, 1, 1) in ('A', 'B')) and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'), '9999'), '-'), TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and
         T_B.CAL_MONTH_NAME >= '2013-05')),
SACOMMON3694452 AS
 (select sum(T_C.AVHIRE_QTY_ACT) as c9,
         sum(T_C.RMHIRE_QTY_ACT) as c10,
         T_A.EFFECTIVE_MONTH_ACT as c11,
         T_A.EFFECTIVE_MONTH_BGT as c12,
         T_A.T_A_CODE as c13,
         T_A.T_A_COOPER as c14,
         cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c15,
         T_B.CAL_MONTH_ID as c16,
         grouping_id(T_A.T_A_CODE,
                     T_A.T_A_COOPER,
                     T_A.EFFECTIVE_MONTH_ACT,
                     T_A.EFFECTIVE_MONTH_BGT,
                     cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER),
                     T_B.CAL_MONTH_ID) as c17
    from T_B T_B,
         T_C T_C,
         (SELECT CHAIN_T_A_SK,
                 HEADER_ID,
                 OFFICE_CODE,
                 T_A_CODE,
                 T_A_SHORT_NAME,
                 T_A_NAME,
                 T_A_ADDRESS,
                 T_A_PHONE,
                 T_A_PROPERITY,
                 COMPANY_PROPERTY,
                 T_A_AREA,
                 PROVINCE_SEQ,
                 T_A_PROVINCE,
                 CITY_SEQ,
                 T_A_CITY,
                 T_A_COUNTY,
                 T_A_COOPER,
                 T_A_BRAND,
                 T_A_NATURE,
                 LEASE_BUILDING_AREA,
                 CONS_BUILDING_AREA,
                 ROOM_QUANTITY,
                 SEATS_QUANTITY,
                 MEAL_QUANTITY,
                 BUSINESS_LICENSE_DATE,
                 SIGN_DATE,
                 START_DATE,
                 END_DATE,
                 case
                   when SIGN_DATE >= PRE_BUSINESS_DATE then
                    SIGN_DATE
                   else
                    PRE_BUSINESS_DATE
                 end PRE_BUSINESS_DATE,
                 BUSINESS_DATE,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATION_DATE,
                 CREATED_BY,
                 LAST_UPDATE_LOGIN,
                 T_A_SEGMENT,
                 T_A_AREA_1,
                 T_A_TYPE,
                 ORG_CODE,
                 BUSINESS_LICENSE_END_DATE,
                 PMS_CODE,
                 ORG_ID,
                 PRO_HANDOVER_DATE,
                 FOREIGN_RENT_AREA,
                 SIGN_YEAR,
                 COMPANY_NAME,
                 REMOVE_DATE,
                 EFFECTIVE_DATE,
                 EXPIRATION_DATE,
                 CURRENT_FLAG,
                 ETL_DATE,
                 OLD_T_A_NAME,
                 BRAND_CODE,
                 EQUITY_NAME,
                 (EQUITY_RATE / 100.00) AS EQUITY_RATE,
                 nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE,
                 floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN,
                 floor(business_date - pre_business_date) AS DIFF_B_PRE,
                 floor(business_date - sign_date) AS DIFF_B_SIGN,
                 CASE
                   WHEN TERMINATION_DATE IS NULL THEN
                    'N'
                   ELSE
                    'Y'
                 END IS_FLAG,
                 TERMINATION_DATE,
                 to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act,
                 to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act,
                 to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt,
                 to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt
            FROM T_A,
                 (select t.T_A_code u_T_A_code,
                         max(t.effective_date) u_effective_date
                    from T_A t
                   group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t
           WHERE T_A.T_A_code = t.u_T_A_code
             and T_A.effective_date = t.u_effective_date) T_A
   where (T_B.CAL_MONTH_NAME < T_A.EXPIRATION_MONTH_ACT and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_B.CAL_MONTH_NAME >= T_A.EFFECTIVE_MONTH_ACT and
         T_B.CAL_MONTH_NAME >= '2013-05' and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'),
                                   '9999'),
                         '-'),
                  TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_B.CAL_MONTH_NAME or T_A.TERMINATION_DATE is null))
   group by grouping sets((cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_COOPER, T_A.T_A_CODE),(T_B.CAL_MONTH_ID, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_CODE))),
SAWITH1 AS
 (select sum(T_C.AVHIRE_QTY_ACT) as c7,
         sum(T_C.RMHIRE_QTY_ACT) as c8,
         T_A.EFFECTIVE_MONTH_ACT as c9,
         T_A.EFFECTIVE_MONTH_BGT as c10,
         T_A.T_A_CODE as c11,
         T_A.T_A_COOPER as c12
    from T_B T_B,
         T_C T_C,
         (SELECT CHAIN_T_A_SK,
                 HEADER_ID,
                 OFFICE_CODE,
                 T_A_CODE,
                 T_A_SHORT_NAME,
                 T_A_NAME,
                 T_A_ADDRESS,
                 T_A_PHONE,
                 T_A_PROPERITY,
                 COMPANY_PROPERTY,
                 T_A_AREA,
                 PROVINCE_SEQ,
                 T_A_PROVINCE,
                 CITY_SEQ,
                 T_A_CITY,
                 T_A_COUNTY,
                 T_A_COOPER,
                 T_A_BRAND,
                 T_A_NATURE,
                 LEASE_BUILDING_AREA,
                 CONS_BUILDING_AREA,
                 ROOM_QUANTITY,
                 SEATS_QUANTITY,
                 MEAL_QUANTITY,
                 BUSINESS_LICENSE_DATE,
                 SIGN_DATE,
                 START_DATE,
                 END_DATE,
                 case
                   when SIGN_DATE >= PRE_BUSINESS_DATE then
                    SIGN_DATE
                   else
                    PRE_BUSINESS_DATE
                 end PRE_BUSINESS_DATE,
                 BUSINESS_DATE,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATION_DATE,
                 CREATED_BY,
                 LAST_UPDATE_LOGIN,
                 T_A_SEGMENT,
                 T_A_AREA_1,
                 T_A_TYPE,
                 ORG_CODE,
                 BUSINESS_LICENSE_END_DATE,
                 PMS_CODE,
                 ORG_ID,
                 PRO_HANDOVER_DATE,
                 FOREIGN_RENT_AREA,
                 SIGN_YEAR,
                 COMPANY_NAME,
                 REMOVE_DATE,
                 EFFECTIVE_DATE,
                 EXPIRATION_DATE,
                 CURRENT_FLAG,
                 ETL_DATE,
                 OLD_T_A_NAME,
                 BRAND_CODE,
                 EQUITY_NAME,
                 (EQUITY_RATE / 100.00) AS EQUITY_RATE,
                 nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE,
                 floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN,
                 floor(business_date - pre_business_date) AS DIFF_B_PRE,
                 floor(business_date - sign_date) AS DIFF_B_SIGN,
                 CASE
                   WHEN TERMINATION_DATE IS NULL THEN
                    'N'
                   ELSE
                    'Y'
                 END IS_FLAG,
                 TERMINATION_DATE,
                 to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act,
                 to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act,
                 to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt,
                 to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt
            FROM T_A,
                 (select t.T_A_code u_T_A_code,
                         max(t.effective_date) u_effective_date
                    from T_A t
                   group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t
           WHERE T_A.T_A_code = t.u_T_A_code
             and T_A.effective_date = t.u_effective_date) T_A
   where (T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'),
                                   '9999'),
                         '-'),
                  TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and
         T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and
         T_C.PERIOD_MON >= '2013-05' and
         T_B.CAL_MONTH_NAME >= '2013-05')
   group by T_A.T_A_CODE,
            T_A.T_A_COOPER,
            T_A.EFFECTIVE_MONTH_ACT,
            T_A.EFFECTIVE_MONTH_BGT),
SAWITH2 AS
 (select D4.c7  as c1,
         D4.c8  as c2,
         D1.c7  as c3,
         D1.c1  as c4,
         D2.c9  as c5,
         D2.c10 as c6,
         D3.c10 as c7,
         D1.c2  as c8,
         D1.c3  as c9,
         D1.c4  as c10,
         D1.c5  as c11,
         D1.c6  as c12
    from ((SAWITH0 D1 inner join SACOMMON3694452 D2 On
          SYS_OP_MAP_NONNULL(D2.c15) = SYS_OP_MAP_NONNULL(D1.c2) and
          D2.c12 = D1.c5 and D2.c11 = D1.c4 and
          SYS_OP_MAP_NONNULL(D2.c13) = SYS_OP_MAP_NONNULL(D1.c6) and
          SYS_OP_MAP_NONNULL(D2.c14) = SYS_OP_MAP_NONNULL(D1.c7)) inner join
          SACOMMON3694452 D3 On
          SYS_OP_MAP_NONNULL(D3.c15) = SYS_OP_MAP_NONNULL(D1.c2) and
          D3.c12 = D1.c5 and D3.c11 = D1.c4 and
          SYS_OP_MAP_NONNULL(D3.c16) = SYS_OP_MAP_NONNULL(D1.c8) and
          SYS_OP_MAP_NONNULL(D3.c13) = SYS_OP_MAP_NONNULL(D1.c6))
   inner join SAWITH1 D4
      On D4.c10 = D1.c5
     and D4.c9 = D1.c4
     and SYS_OP_MAP_NONNULL(D4.c11) = SYS_OP_MAP_NONNULL(D1.c6)
     and SYS_OP_MAP_NONNULL(D4.c12) = SYS_OP_MAP_NONNULL(D1.c7)
   where (D2.c17 = 1 and D3.c17 = 16))
select D1.c1  as c1,
       D1.c2  as c2,
       D1.c3  as c3,
       D1.c4  as c4,
       D1.c5  as c5,
       D1.c6  as c6,
       D1.c7  as c7,
       D1.c8  as c8,
       D1.c9  as c9,
       D1.c10 as c10
  from (select 0 as c1,
               D1.c3 as c2,
               D1.c4 as c3,
               '经营利润率' as c4,
               D1.c8 as c5,
               case
                 when D1.c9 <= '2013-10' then
                  case
                    when D1.c5 = 0 then
                     0
                    else
                     cast(D1.c6 as DOUBLE PRECISION) / nullif(D1.c5, 0) * 100.0
                  end
                 else
                  NULL
               end as c6,
               case
                 when D1.c9 <= '2013-10' then
                  case
                    when D1.c1 = 0 then
                     0
                    else
                     cast(D1.c2 as DOUBLE PRECISION) / nullif(D1.c1, 0) * 100.0
                  end
                 else
                  NULL
               end as c7,
               D1.c10 as c8,
               D1.c11 as c9,
               D1.c12 as c10,
               ROW_NUMBER() OVER(PARTITION BY D1.c3, D1.c4, D1.c8, D1.c10, D1.c11, D1.c12 ORDER BY D1.c3 ASC, D1.c4 ASC, D1.c8 ASC, D1.c10 ASC, D1.c11 ASC, D1.c12 ASC) as c11
          from SAWITH2 D1) D1
 where (D1.c11 = 1);


经观察，以上语句中t_a重复调用了多次，于是用with改写如下 

WITH T_A AS 
(SELECT CHAIN_T_A_SK,
                 HEADER_ID,
                 OFFICE_CODE,
                 T_A_CODE,
                 T_A_SHORT_NAME,
                 T_A_NAME,
                 T_A_ADDRESS,
                 T_A_PHONE,
                 T_A_PROPERITY,
                 COMPANY_PROPERTY,
                 T_A_AREA,
                 PROVINCE_SEQ,
                 T_A_PROVINCE,
                 CITY_SEQ,
                 T_A_CITY,
                 T_A_COUNTY,
                 T_A_COOPER,
                 T_A_BRAND,
                 T_A_NATURE,
                 LEASE_BUILDING_AREA,
                 CONS_BUILDING_AREA,
                 ROOM_QUANTITY,
                 SEATS_QUANTITY,
                 MEAL_QUANTITY,
                 BUSINESS_LICENSE_DATE,
                 SIGN_DATE,
                 START_DATE,
                 END_DATE,
                 case
                   when SIGN_DATE >= PRE_BUSINESS_DATE then
                    SIGN_DATE
                   else
                    PRE_BUSINESS_DATE
                 end PRE_BUSINESS_DATE,
                 BUSINESS_DATE,
                 LAST_UPDATE_DATE,
                 LAST_UPDATED_BY,
                 CREATION_DATE,
                 CREATED_BY,
                 LAST_UPDATE_LOGIN,
                 T_A_SEGMENT,
                 T_A_AREA_1,
                 T_A_TYPE,
                 ORG_CODE,
                 BUSINESS_LICENSE_END_DATE,
                 PMS_CODE,
                 ORG_ID,
                 PRO_HANDOVER_DATE,
                 FOREIGN_RENT_AREA,
                 SIGN_YEAR,
                 COMPANY_NAME,
                 REMOVE_DATE,
                 EFFECTIVE_DATE,
                 EXPIRATION_DATE,
                 CURRENT_FLAG,
                 ETL_DATE,
                 OLD_T_A_NAME,
                 BRAND_CODE,
                 EQUITY_NAME,
                 (EQUITY_RATE / 100.00) AS EQUITY_RATE,
                 nvl(BUSINESS_DATE, PRE_BUSINESS_DATE) UNI_BUSINESS_DATE,
                 floor(pre_business_date - sign_date) AS DIFF_PRE_SIGN,
                 floor(business_date - pre_business_date) AS DIFF_B_PRE,
                 floor(business_date - sign_date) AS DIFF_B_SIGN,
                 CASE
                   WHEN TERMINATION_DATE IS NULL THEN
                    'N'
                   ELSE
                    'Y'
                 END IS_FLAG,
                 TERMINATION_DATE,
                 to_char(add_months(effective_date, -1), 'YYYY-MM') effective_month_act,
                 to_char(add_months(expiration_date, -1), 'YYYY-MM') expiration_month_act,
                 to_char(add_months(effective_date, 12), 'YYYY-MM') effective_month_bgt,
                 to_char(add_months(expiration_date, 12), 'YYYY-MM') expiration_month_bgt
            FROM T_A,
                 (select t.T_A_code u_T_A_code,
                         max(t.effective_date) u_effective_date
                    from T_A t
                   group by t.T_A_code, to_char(t.effective_date, 'YYYY-MM')) t
           WHERE T_A.T_A_code = t.u_T_A_code
             and T_A.effective_date = t.u_effective_date),
SAWITH0 AS
 (select distinct T_A.T_A_SHORT_NAME as c1,
                  cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c2,
                  T_B.CAL_MONTH_NAME as c3,
                  T_A.EFFECTIVE_MONTH_ACT as c4,
                  T_A.EFFECTIVE_MONTH_BGT as c5,
                  T_A.T_A_CODE as c6,
                  T_A.T_A_COOPER as c7,
                  T_B.CAL_MONTH_ID as c8
    from T_B T_B /* Dim-T_B */,
         T_C T_C /* 
                  Fact-T_C_Act */,
          T_A
   where (T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and
         T_C.PERIOD_MON >= '2013-05' and
         (substr(T_C.T_A_CODE, 1, 1) in ('A', 'B')) and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'),
                                   '9999'),
                         '-'),
                  TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and
         T_B.CAL_MONTH_NAME >= '2013-05')),
SACOMMON3694452 AS
 (select sum(T_C.AVHIRE_QTY_ACT) as c9,
         sum(T_C.RMHIRE_QTY_ACT) as c10,
         T_A.EFFECTIVE_MONTH_ACT as c11,
         T_A.EFFECTIVE_MONTH_BGT as c12,
         T_A.T_A_CODE as c13,
         T_A.T_A_COOPER as c14,
         cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER) as c15,
         T_B.CAL_MONTH_ID as c16,
         grouping_id(T_A.T_A_CODE,
                     T_A.T_A_COOPER,
                     T_A.EFFECTIVE_MONTH_ACT,
                     T_A.EFFECTIVE_MONTH_BGT,
                     cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER),
                     T_B.CAL_MONTH_ID) as c17
    from T_B T_B /* Dim-T_B */,
         T_C T_C /* 
                  Fact-T_C_Act */,
          T_A
   where (T_B.CAL_MONTH_NAME < T_A.EXPIRATION_MONTH_ACT and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_B.CAL_MONTH_NAME >= T_A.EFFECTIVE_MONTH_ACT and
         T_B.CAL_MONTH_NAME >= '2013-05' and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'),
                                   '9999'),
                         '-'),
                  TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_B.CAL_MONTH_NAME or T_A.TERMINATION_DATE is null))
   group by grouping sets((cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_COOPER, T_A.T_A_CODE),(T_B.CAL_MONTH_ID, cast(substr(T_B.CAL_MONTH_NAME, - (2)) as INTEGER), T_A.EFFECTIVE_MONTH_BGT, T_A.EFFECTIVE_MONTH_ACT, T_A.T_A_CODE))),
SAWITH1 AS
 (select sum(T_C.AVHIRE_QTY_ACT) as c7,
         sum(T_C.RMHIRE_QTY_ACT) as c8,
         T_A.EFFECTIVE_MONTH_ACT as c9,
         T_A.EFFECTIVE_MONTH_BGT as c10,
         T_A.T_A_CODE as c11,
         T_A.T_A_COOPER as c12
    from T_B T_B /* Dim-T_B */,
         T_C T_C /* 
                  Fact-T_C_Act */,
          T_A
   where (T_C.T_A_CODE = T_A.T_A_CODE and
         T_C.PERIOD_MON = T_B.CAL_MONTH_NAME and
         T_B.CAL_YEAR_ID = '2013' and
         T_C.PERIOD_MON < T_A.EXPIRATION_MONTH_ACT and
         (substr(T_A.T_A_CODE, 1, 1) in ('A', 'B')) and
         (concat(concat(TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'yyyy'),
                                   '9999'),
                         '-'),
                  TO_NUMBER(TO_CHAR(T_A.TERMINATION_DATE, 'MM'), '99')) >=
         T_C.PERIOD_MON or T_A.TERMINATION_DATE is null) and
         T_C.PERIOD_MON >= T_A.EFFECTIVE_MONTH_ACT and
         T_C.PERIOD_MON >= '2013-05' and
         T_B.CAL_MONTH_NAME >= '2013-05')
   group by T_A.T_A_CODE,
            T_A.T_A_COOPER,
            T_A.EFFECTIVE_MONTH_ACT,
            T_A.EFFECTIVE_MONTH_BGT),
SAWITH2 AS
 (select D4.c7  as c1,
         D4.c8  as c2,
         D1.c7  as c3,
         D1.c1  as c4,
         D2.c9  as c5,
         D2.c10 as c6,
         D3.c10 as c7,
         D1.c2  as c8,
         D1.c3  as c9,
         D1.c4  as c10,
         D1.c5  as c11,
         D1.c6  as c12
    from ((SAWITH0 D1 inner join SACOMMON3694452 D2 On
          SYS_OP_MAP_NONNULL(D2.c15) = SYS_OP_MAP_NONNULL(D1.c2) and
          D2.c12 = D1.c5 and D2.c11 = D1.c4 and
          SYS_OP_MAP_NONNULL(D2.c13) = SYS_OP_MAP_NONNULL(D1.c6) and
          SYS_OP_MAP_NONNULL(D2.c14) = SYS_OP_MAP_NONNULL(D1.c7)) inner join
          SACOMMON3694452 D3 On
          SYS_OP_MAP_NONNULL(D3.c15) = SYS_OP_MAP_NONNULL(D1.c2) and
          D3.c12 = D1.c5 and D3.c11 = D1.c4 and
          SYS_OP_MAP_NONNULL(D3.c16) = SYS_OP_MAP_NONNULL(D1.c8) and
          SYS_OP_MAP_NONNULL(D3.c13) = SYS_OP_MAP_NONNULL(D1.c6))
   inner join SAWITH1 D4
      On D4.c10 = D1.c5
     and D4.c9 = D1.c4
     and SYS_OP_MAP_NONNULL(D4.c11) = SYS_OP_MAP_NONNULL(D1.c6)
     and SYS_OP_MAP_NONNULL(D4.c12) = SYS_OP_MAP_NONNULL(D1.c7)
   where (D2.c17 = 1 and D3.c17 = 16))
select D1.c1  as c1,
       D1.c2  as c2,
       D1.c3  as c3,
       D1.c4  as c4,
       D1.c5  as c5,
       D1.c6  as c6,
       D1.c7  as c7,
       D1.c8  as c8,
       D1.c9  as c9,
       D1.c10 as c10
  from (select 0 as c1,
               D1.c3 as c2,
               D1.c4 as c3,
               '经营利润率' as c4,
               D1.c8 as c5,
               case
                 when D1.c9 <= '2013-10' then
                  case
                    when D1.c5 = 0 then
                     0
                    else
                     cast(D1.c6 as DOUBLE PRECISION) / nullif(D1.c5, 0) * 100.0
                  end
                 else
                  NULL
               end as c6,
               case
                 when D1.c9 <= '2013-10' then
                  case
                    when D1.c1 = 0 then
                     0
                    else
                     cast(D1.c2 as DOUBLE PRECISION) / nullif(D1.c1, 0) * 100.0
                  end
                 else
                  NULL
               end as c7,
               D1.c10 as c8,
               D1.c11 as c9,
               D1.c12 as c10,
               ROW_NUMBER() OVER(PARTITION BY D1.c3, D1.c4, D1.c8, D1.c10, D1.c11, D1.c12 ORDER BY D1.c3 ASC, D1.c4 ASC, D1.c8 ASC, D1.c10 ASC, D1.c11 ASC, D1.c12 ASC) as c11
          from SAWITH2 D1) D1
 where (D1.c11 = 1);
改写后网友反馈


 有教无类 18:40:09 
起作用了没
2013-11-09
     11:05:23 
起作用了
     11:05:33 
0.8秒  



jgmydsai
关注
专栏目录
解决Oracle SQL语句性能问题——SQL语句改写（分析函数、with as、union及or）
数据库生态圈（RDB & NoSQL & Bigdata）——专注于关系库优化（Oracle&Mysql&PG&SQL Server ）
09-03 960
本文详细说明了解决Oracle SQL性能问题的方法——SQL语句改写（分析函数、with as、union及or），同时，结合实例对其进行了深入讲解。
使用WITH语法优化SQL一则
youyonghu001的专栏
04-21 573
对应SQL中重复执行的部分，可以使用WITH语法进行优化
标量子查询SQL改写一则（包括WITH的改写）
youyonghu001的专栏
04-21 748
在主查询返回大量结果集的情况下，标量子查询（Scalar Subquery）的性能是非常差的，而且即使使用并行也不行，这种情况一般需要根据SQL本身的逻辑进行改写，一般可以改写为外关联的方式。部分标量子查询还可以直接使用分析函数（window function）进行改写，不需要外关联。
with语句性能优化
u010806384的博客
07-17 881
学习javascript 时在语句里面看见了with语句，兴之所至仔细看了下。 在一位前辈的博客看见十分详细的介绍，本着原汁原味的原则特摘录下来。 举一个实际例子吧：   Js代码   with(document.forms[0]){          name.value = "lee king";          address.value = 
SQL优化 第九章 With改写优化SQL
songjian1104的专栏
08-03 476
参考资料： 本系列博客主要参考资料有CUUG冉乃纲老师数据库教学笔记，《SQL优化核心思想》（罗炳森，黄超，钟侥著），《PostgreSQL技术内幕：查询优化深度探索》（张树杰著），排名不分先后。 9.1 提取公共项 9.1.1. 数据脚本 drop table test1; drop table test2; create table test1 as select * from...
落落 Oracle SQL优化与改写培训教程
06-15
字典式写作手法：不讲具体语法，没有简单知识堆砌，直接以案例形式讲技巧与案例 大量优化实战方法：将主要SQL优化点一一剖析，分享大量SQL优化的实际工作经验 50余改写调优案例：覆盖大多数DBA日常工作场景，具有相当大的实用价值 技巧+案例：可以作为DBA的参考手册，也可以作为开发人员编写SQL的速查字典
SQL Server中with as使用介绍
09-09
总的来说，`WITH AS` 语句是SQL Server中一个非常实用的功能，它使得编写和维护复杂的SQL查询变得更加容易，同时也提供了优化查询性能的可能性。在编写SQL查询时，熟练运用CTE能够极大地提高代码质量，并有助于提升...
收获不止SQL优化
08-07
第1章　全局在胸——用工具对SQL整体优化 1 1.1　都有哪些性能工具 1 1.1.1　不同调优场景分析 2 1.1.2　不同场景对应工具 2 1.2　整体性能工具的要点 4 1.2.1　五大性能报告的获取 5 1.2.2　五大报告关注的要点 10 ...
with语句使用的经典案例
friend_ship的博客
03-29 176
with语句被用于在访问一个对象的属性或方法时避免重复使用指定对象引用。 通过下面的js代码，展示说明： var date_time = new Date(); with(date_time){ let a = getMonth() + 1; console.log(getYear() + '年' + a + '月' + getDate() + '日') } 下面的代码是不使用with的写法： var date_time = new Date(); let a = date_ti
Oracle查询优化改写技巧与案例 有教无类落落
04-05
《Oracle查询优化改写技巧与案例》看点： # 字典式写作手法：不讲具体语法，没有简单知识堆砌，直接以案例形式讲技巧与案例 # 大量优化实战方法：将主要SQL优化点一一剖析，分享大量SQL优化的实际工作经验 # 50余改写调优案例：覆盖大多数DBA日常工作场景，具有相当大的实用价值 # 技巧+案例：可以作为DBA的参考手册，也可以作为开发人员编写SQL的速查字典
With...As 用法--公用表达式
杨楠
03-15 4880
前言：在看牛腩视频的时候，发现有一个with...as语句，用于分页的使用，于是就查了一下，结果发现了这个语句非常了不起，下面就说一下这个是什么东西。内容：一、公用表达式概念公用表达式 （Common Table Expression） 简称：CTE，可以看做一个临时的结果集，在接下来的select ，insert，update，delete可以被多次引用。使用公用表达式CTE可以让语句更加清晰简...
python中with的用法_Python中with的介绍以及高级用法
weixin_39714275的博客
12-05 337
在Python2.6中引入了 with 语句，如以下代码with open("myfile", "r") as f:print f.readline()相信很多的Python教程中都提到过 with 这么一个简洁的语法。如果不用with，那么我们将改写成如下形式：f = file("myfile", "r")try:print f.readline()execpt Exception:passfi...
用with改写优化sql之二
有教无类的专栏
11-09 1173
这是另一个网友发的sql，这个有点儿坑 SYS@netdb1(netdb61)>select l_target.*, rownum as rn 2 FROM (select l0.l0_id as l0Id, 3 l0.date1 as createDate, 4
python使用with as 读写文件
热门推荐
.
08-14 3万+
最近在项目中用到了Python的with as来开关读写文件，特此记录下，方便以后使用 1. 传统读文件 传统读取文件的方法是先使用Python内置的open函数打开文件，然后标示符’r’表示读，这样，我们就成功地打开了一个文件，获得到一个f文件句柄。 f = open('test.txt','r') 如果文件不存在，open()函数就会抛出一个错误，并且给出错误码和详细的信息告诉你文件不存在：...
with 子句
bigdata_zx的博客
10-22 1832
WITH子句提供了一种定义临时表的操作方法 使用WITH子句将emp表中的数据定义为临时表 WITH e AS (SELECT * FROM emp) 创建临时表e 创建多个with子句，可以用逗号隔开 with q as(…），w as(…) 查询每个部门中各个职位的总工资 —— 按照部门编号及职位进行分组 select deptno,job,sum(sal) from emp group ...
oracle 语法start with
晨风
03-12 634
参考： 1. 令人迷糊的Oracle递归查询(start with) http://blog.csdn.net/weiwenhp/article/details/8218091 2. PostgreSQL Oracle 兼容性系列之 - WITH 递归 ( connect by ) https://yq.aliyun.com/articles/240
关于python中with 和 try 块的联合使用的问题
zjm750617105的专栏
10-17 8119
最近学习python，看到with的用法，感觉不用try except就ok，但是事实证明并不是这样，如果不用try except，with语句只是帮你关闭没有释放的资源，并且抛出异常，但是后面的语句是不能执行的，所以为了即能够输出我们自定义的错误信息，又能不影响后面代码的执行，必须还得使用try except 语句。但是此时又会问：那使用with ，还有啥用呢？其实还有有用的，不用担心资源没有关
利用WITH AS改写SQL
风中清淡
12-26 3562
with as 改写sql
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值