用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 953
本文详细说明了解决Oracle SQL性能问题的方法——SQL语句改写（分析函数、with as、union及or），同时，结合实例对其进行了深入讲解。
使用WITH语法优化SQL一则
youyonghu001的专栏
04-21 571
对应SQL中重复执行的部分，可以使用WITH语法进行优化
标量子查询SQL改写一则（包括WITH的改写）
youyonghu001的专栏
04-21 748
在主查询返回大量结果集的情况下，标量子查询（Scalar Subquery）的性能是非常差的，而且即使使用并行也不行，这种情况一般需要根据SQL本身的逻辑进行改写，一般可以改写为外关联的方式。部分标量子查询还可以直接使用分析函数（window function）进行改写，不需要外关联。
with语句性能优化
u010806384的博客
07-17 880
学习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 175
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的速查字典
Oracle 使用 with as 优化重复查询
panlong189的博客
09-23 4624
我们有时写的sql 会多次查询和使用相同的结果集，而事实上每次查询都会消耗资源和降低sql的整体查询效率，特别是对大量数据表，耗时特别长。 使用with as 将想要查询的数据集保存到一张虚表中，数据查询均从这张虚拟表（视图）中查询获得，减少对数据库的直接访问。 WITH SCCCE AS (SELECT A.CUST_ID,                 S.SUBS_ID,
With...As 用法--公用表达式
杨楠
03-15 4867
前言：在看牛腩视频的时候，发现有一个with...as语句，用于分页的使用，于是就查了一下，结果发现了这个语句非常了不起，下面就说一下这个是什么东西。内容：一、公用表达式概念公用表达式 （Common Table Expression） 简称：CTE，可以看做一个临时的结果集，在接下来的select ，insert，update，delete可以被多次引用。使用公用表达式CTE可以让语句更加清晰简...
with as 与临时表的区别_Oracle实验--从一个需求实现来看为什么要用with改写sql?
weixin_39610964的博客
12-09 566
概述前面已经介绍了with的一些用法，今天主要通过一个实验来简单测试下用不用with的一些不同。1、环境准备准备测试数据267万CREATE TABLE t AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;insert into t select * from t;//.....2、需求需求：列出T表中用户包含对象数量大于平均用户对象数的记录。这里大家可以想...
python中with的用法_Python中with的介绍以及高级用法
weixin_39714275的博客
12-05 334
在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 as 用法及with as 改写sql进行优化
qq_42902673的博客
01-18 2023
with as 在oracle中用法： WITH wa_tab_name AS (SELECT * FROM emp) SELECT * FROM wa_tab_name; #相当于在将emp表临时存放在了wa_tab_name中 再如： WITH wa_tab_name AS (SELECT deptno,COUNT(*) AS cnt FROM emp GROUP BY deptno) SE...
使用with as 优化SQL
落落的专栏 专注SQL调优 性能调优
06-03 6626
在一个SQL语句中，如果某个表需要被访问多次，而且每次访问的时候限制调剂基本上一致的话，我们就可以利用with as来优化下面举个例子：SQL> CREATE TABLE t1 (id number, name varchar2(10));Table created.SQL> begin  2      FOR i IN 1 .. 1000 LOOP  3          INSERT INTO t1 VALUES(i,'fuck');  4      END loop;  5      commit; 
sql查询优化 索引优化
weixin_34163553的博客
12-04 742
  sql语句优化  性能不理想的系统中除了一部分是因为应用程序的负载确实超过了服务器的实际处理能力外,更多的是因为系统存在大量的SQL语句需要优化。 为了获得稳定的执行性能，SQL语句越简单越好。对复杂的SQL语句，要设法对之进行简化。 常见的简化规则如下： 1）不要有超过5个以上的表连接（JOIN）2）考虑使用临时表或表变量存放中间结果。3）少用子查询4）视图嵌套不要过深,一般视图嵌套...
一个超超超烂SQL的优化
落落的专栏 专注SQL调优 性能调优
10-18 9823
最近遇到一个超超超烂的SQL，由于SQL涉及某某税务局的数据，表名字是经过处理的。 原始SQL如下： select * from ( select t.zxid,t.gh,t.xm,t.bm,t.fzjgdm, (select count(a.sessio
SQL等价改写：优化关键与实战
罗炳森，业内知名的SQL优化专家，具有丰富的数据库设计与开发经验，尤其在SQL优化和等价改写方面有深厚造诣。他强调SQL优化不仅仅是更新统计信息、建立索引或绑定执行计划，更重要的是进行SQL等价改写，这是优化中最...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值