数仓实践丨常量标量子查询做全连接导致整体慢
本文分享自华为云社区《GaussDB(DWS)性能调优：常量标量子查询做全连接导致整体慢》，作者： Zawami 。
问题描述
由于SQL中存在标量子查询同另一查询做笛卡尔积使SQL整体慢。标量子查询，即结果集只有一行一列的子查询。这里导致的SQL语句执行慢不只是在于做笛卡尔积慢，也会使后续聚合更慢。
原始语句
WITH TMP AS( SELECT case when length('[“202309“]') = 6 then '[“202309“]' || '01' WHEN length('[“202309“]') <> 8 THEN TO_CHAR(CURRENT_DATE, 'YYYYMMDD') END AS V_DATE from DUAL ) SELECT BG_CODE, BG_CN_NAME, BG_EN_NAME, METRIC_CODE --指标ID , METRIC_CN_NAME --指标中文名称 , METRIC_EN_NAME --指标英文名称 , CURRENCY --币种 , OVERSEAS_FLAG, REGION_CODE, REGION_CN_NAME, REGION_EN_NAME, REPOFFICE_CODE, REPOFFICE_CN_NAME, REPOFFICE_EN_NAME, OFFICE_CODE, OFFICE_CN_NAME, OFFICE_EN_NAME, REGION_CUSTCATG_CODE, REGION_CUSTCATG_CN_NAME, REGION_CUSTCATG_EN_NAME, TOP_CUST_CATEGORY_CODE, TOP_CUST_CATEGORY_EN_NAME, TOP_CUST_CATEGORY_CN_NAME, ACCTCUST_HQ_CODE, ACCTCUST_HQ_CN_NAME, ACCTCUST_HQ_EN_NAME, ACCTCUST_BRANCH_CODE, ACCTCUST_BRANCH_CN_NAME, ACCTCUST_BRANCH_EN_NAME, ACCTCUST_SUBSIDIARY_CODE, ACCTCUST_SUBSIDIARY_CN_NAM, ACCTCUST_SUBSIDIARY_EN_NAM, COUNTRY_CODE --新增加入参 , COUNTRY_CN_NAME --新增加入参 , COUNTRY_EN_NAME --新增加入参 , AGREE_AMOUNT --BUSI_DSCT_00001 总优惠 , AGREE_REMAIN_AMOUNT --BUSI_DSCT_00002 即期优惠 , SIGN_AMOUNT --BUSI_DSCT_00003 即期优惠/一次性优惠 , USE_AMOUNT --BUSI_DSCT_00004 即期优惠/单价量折扣 , NOT_USED_VALID_AMOUNT --BUSI_DSCT_00005 延期优惠 , NOT_USED_INVALID_AMOUNT --BUSI_DSCT_00006 voucher , NEW_SIGN_AMOUNT --BUSI_DSCT_00007 其他延期优惠 , NEW_USE_AMOUNT --BUSI_DSCT_00008 本月新使用金额 , EXPIRED_AMOUNT --BUSI_DSCT_00009 本月已过期金额 , IMMED_EXPIRED_AMOUNT --BUSI_DSCT_00010 半年内即将过期金额 FROM ( SELECT C.BG_CODE, C.BG_CN_NAME, C.BG_EN_NAME, C.M_ID AS METRIC_CODE --指标ID , C.M_CN AS METRIC_CN_NAME --指标中文名称 , C.M_EN AS METRIC_EN_NAME --指标英文名称 , C.CURRENCY_CODE AS CURRENCY --币种 ,CASE WHEN 1 = 0 THEN C.OVERSEA_FLAG ELSE NULL END AS OVERSEAS_FLAG,CASE WHEN 1 = 0 THEN C.REGION_CODE ELSE NULL END AS REGION_CODE,CASE WHEN 1 = 0 THEN C.REGION_CN_NAME ELSE NULL END AS REGION_CN_NAME,CASE WHEN 1 = 0 THEN C.REGION_EN_NAME ELSE NULL END AS REGION_EN_NAME,CASE WHEN 1 = 0 THEN C.REPOFFICE_CODE ELSE NULL END AS REPOFFICE_CODE,CASE WHEN 1 = 0 THEN C.REPOFFICE_CN_NAME ELSE NULL END AS REPOFFICE_CN_NAME,CASE WHEN 1 = 0 THEN C.REPOFFICE_EN_NAME ELSE NULL END AS REPOFFICE_EN_NAME,CASE WHEN 1 = 0 THEN C.OFFICE_CODE ELSE NULL END AS OFFICE_CODE,CASE WHEN 1 = 0 THEN C.OFFICE_CN_NAME ELSE NULL END AS OFFICE_CN_NAME,CASE WHEN 1 = 0 THEN C.OFFICE_EN_NAME ELSE NULL END AS OFFICE_EN_NAME,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CODE ELSE NULL END AS REGION_CUSTCATG_CODE,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CN_NAME ELSE NULL END AS REGION_CUSTCATG_CN_NAME,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_EN_NAME ELSE NULL END AS REGION_CUSTCATG_EN_NAME,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CODE ELSE NULL END AS TOP_CUST_CATEGORY_CODE,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_EN_NAME ELSE NULL END AS TOP_CUST_CATEGORY_EN_NAME,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CN_NAME ELSE NULL END AS TOP_CUST_CATEGORY_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CODE ELSE NULL END AS ACCTCUST_HQ_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CN_NAME ELSE NULL END AS ACCTCUST_HQ_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_EN_NAME ELSE NULL END AS ACCTCUST_HQ_EN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CODE ELSE NULL END AS ACCTCUST_BRANCH_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CN_NAME ELSE NULL END AS ACCTCUST_BRANCH_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_EN_NAME ELSE NULL END AS ACCTCUST_BRANCH_EN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CODE ELSE NULL END AS ACCTCUST_SUBSIDIARY_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CN_NAM ELSE NULL END AS ACCTCUST_SUBSIDIARY_CN_NAM,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_EN_NAM ELSE NULL END AS ACCTCUST_SUBSIDIARY_EN_NAM,CASE WHEN 1 = 0 THEN C.COUNTRY_CODE ELSE NULL END AS COUNTRY_CODE --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_CN_NAME ELSE NULL END AS COUNTRY_CN_NAME --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_EN_NAME ELSE NULL END AS COUNTRY_EN_NAME --新增加入参 , SUM(C.AGREE_AMOUNT) AS AGREE_AMOUNT --协议金额 , SUM(C.AGREE_REMAIN_AMOUNT) AS AGREE_REMAIN_AMOUNT --协议剩余金额 , SUM(C.SIGN_AMOUNT) AS SIGN_AMOUNT --可用金额 , SUM(C.USE_AMOUNT) AS USE_AMOUNT --已使用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND NVL( C.EXPIRED_DATE, add_months(C.EFFECTIVE_DATE, C.VALID_MONTH) ) >= to_date(T.V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE >= to_date(T.V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NOT_USED_VALID_AMOUNT --未使用金额(有效期外) , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.SIGN_AMOUNT ELSE C.EFFECTIVE_TOTAL_AMOUNT END - CASE WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.USE_AMOUNT ELSE C.USED_TOTAL_AMOUNT END - CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND NVL( C.EXPIRED_DATE, add_months(C.EFFECTIVE_DATE, C.VALID_MONTH) ) >= to_date(T.V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE >= to_date(T.V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NOT_USED_INVALID_AMOUNT --未使用金额(有效期内) , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE >= to_date(substr(T.V_DATE, 1, 6), 'yyyymm') and C.EXPIRED_DATE <= LAST_DAY(to_date(T.V_DATE, 'yyyymmdd')) THEN C.SIGN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_START_DATE >= to_date(substr(T.V_DATE, 1, 6), 'yyyymm') and C.DSCT_START_DATE <= LAST_DAY(to_date(T.V_DATE, 'yyyymmdd')) THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NEW_SIGN_AMOUNT --本月新增可用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EFFECTIVE_DATE >= to_date(substr(T.V_DATE, 1, 6), 'yyyymm') and C.EFFECTIVE_DATE <= LAST_DAY(to_date(T.V_DATE, 'yyyymmdd')) THEN C.USE_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_START_DATE >= to_date(substr(T.V_DATE, 1, 6), 'yyyymm') and C.DSCT_START_DATE <= LAST_DAY(to_date(T.V_DATE, 'yyyymmdd')) THEN C.USED_TOTAL_AMOUNT ELSE NULL END ) AS NEW_USE_AMOUNT --本月新使用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE < to_date(T.V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE < to_date(T.V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS EXPIRED_AMOUNT --本月已过期金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE BETWEEN to_date(T.V_DATE, 'yyyymmdd') AND add_months(to_date(T.V_DATE, 'yyyymmdd'), 6) THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE BETWEEN to_date(T.V_DATE, 'yyyymmdd') AND add_months(to_date(T.V_DATE, 'yyyymmdd'), 6) THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS IMMED_EXPIRED_AMOUNT --半年内即将过期金额 FROM DMSALESW.DM_SALE_BUSI_DSCT_SUM_F C LEFT JOIN TMP T ON 1 = 1 WHERE C.CURRENCY_CODE IN ('USD') --改为多值 AND C.BG_CODE IN ('PDCG901159') AND C.M_ID IN ( 'BUSI_DSCT_00001', 'BUSI_DSCT_00002', 'BUSI_DSCT_00003', 'BUSI_DSCT_00004', 'BUSI_DSCT_00005', 'BUSI_DSCT_00006', 'BUSI_DSCT_00007' ) --新增加字段 --AND C.M_CN IN ('#[#P_REPORT_ITEM_NAME#]#') --新增加字段 --新增加字段 GROUP BY C.BG_CODE, C.BG_CN_NAME, C.BG_EN_NAME, C.M_ID --指标ID , C.M_CN --指标中文名称 , C.M_EN --指标英文名称 , C.CURRENCY_CODE --币种 ,CASE WHEN 1 = 0 THEN C.OVERSEA_FLAG ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.REPOFFICE_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.REPOFFICE_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.REPOFFICE_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.OFFICE_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.OFFICE_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.OFFICE_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_EN_NAME ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CODE ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CN_NAM ELSE NULL END,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_EN_NAM ELSE NULL END,CASE WHEN 1 = 0 THEN C.COUNTRY_CODE ELSE NULL END --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_CN_NAME ELSE NULL END --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_EN_NAME ELSE NULL END ) T --新增加入参
从SQL中可以看到TMP为标量子查询，并且在子查询T中和物理表C做了笛卡尔积。 下面是该SQL的执行计划：
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs ----+-------------------------------------------------------------------------+----------------------+---------+---------+------------+----------------+----------+-----------+---------+----------- 1 | -> Row Adapter | 3037.648 | 7 | 245 | | 419KB | | | 1318 | 117210.62 2 | -> Vector Streaming (type: GATHER) | 3037.633 | 7 | 245 | | 777KB | | | 1318 | 117210.62 3 | -> Vector Hash Aggregate | [3031.872, 3032.516] | 7 | 245 | | [4MB, 4MB] | 16MB | [0,870] | 557 | 117128.41 4 | -> Vector Streaming(type: REDISTRIBUTE) | [3031.560, 3032.232] | 112 | 3920 | | [1MB, 1MB] | 2MB | | 557 | 116852.33 5 | -> Vector Hash Aggregate | [2728.059, 2909.255] | 112 | 3920 | | [8MB, 8MB] | 16MB | [833,833] | 557 | 116699.48 6 | -> Vector Nest Loop Left Join (7, 8) | [441.050, 471.725] | 3007901 | 2106919 | | [1MB, 1MB] | 1MB | | 237 | 67316.28 7 | -> CStore Scan on dmsalesw.dm_sale_busi_dsct_sum_f c | [145.354, 158.560] | 3007901 | 2106919 | | [5MB, 5MB] | 1MB | | 205 | 65011.82 8 | -> Vector Materialize | [32.034, 38.902] | 3007901 | 1 | | [288KB, 288KB] | 16MB | [21,21] | 32 | 0.03 9 | -> Vector Subquery Scan on dual | [0.067, 0.093] | 16 | 1 | | [128KB, 128KB] | 1MB | | 32 | 0.02 10 | -> Vector Adapter | [0.005, 0.006] | 16 | 1 | | [40KB, 40KB] | 1MB | | 0 | 0.01 11 | -> Result | [0.001, 0.002] | 16 | 1 | | [8KB, 8KB] | 1MB | | 0 | 0.01
把TMP作为一列放到T中后，性能有明显提升。
EXPLAIN PERFORMANCE SELECT BG_CODE, BG_CN_NAME, BG_EN_NAME, METRIC_CODE --指标ID , METRIC_CN_NAME --指标中文名称 , METRIC_EN_NAME --指标英文名称 , CURRENCY --币种 , OVERSEAS_FLAG, REGION_CODE, REGION_CN_NAME, REGION_EN_NAME, REPOFFICE_CODE, REPOFFICE_CN_NAME, REPOFFICE_EN_NAME, OFFICE_CODE, OFFICE_CN_NAME, OFFICE_EN_NAME, REGION_CUSTCATG_CODE, REGION_CUSTCATG_CN_NAME, REGION_CUSTCATG_EN_NAME, TOP_CUST_CATEGORY_CODE, TOP_CUST_CATEGORY_EN_NAME, TOP_CUST_CATEGORY_CN_NAME, ACCTCUST_HQ_CODE, ACCTCUST_HQ_CN_NAME, ACCTCUST_HQ_EN_NAME, ACCTCUST_BRANCH_CODE, ACCTCUST_BRANCH_CN_NAME, ACCTCUST_BRANCH_EN_NAME, ACCTCUST_SUBSIDIARY_CODE, ACCTCUST_SUBSIDIARY_CN_NAM, ACCTCUST_SUBSIDIARY_EN_NAM, COUNTRY_CODE --新增加入参 , COUNTRY_CN_NAME --新增加入参 , COUNTRY_EN_NAME --新增加入参 , AGREE_AMOUNT --BUSI_DSCT_00001 总优惠 , AGREE_REMAIN_AMOUNT --BUSI_DSCT_00002 即期优惠 , SIGN_AMOUNT --BUSI_DSCT_00003 即期优惠/一次性优惠 , USE_AMOUNT --BUSI_DSCT_00004 即期优惠/单价量折扣 , NOT_USED_VALID_AMOUNT --BUSI_DSCT_00005 延期优惠 , NOT_USED_INVALID_AMOUNT --BUSI_DSCT_00006 voucher , NEW_SIGN_AMOUNT --BUSI_DSCT_00007 其他延期优惠 , NEW_USE_AMOUNT --BUSI_DSCT_00008 本月新使用金额 , EXPIRED_AMOUNT --BUSI_DSCT_00009 本月已过期金额 , IMMED_EXPIRED_AMOUNT --BUSI_DSCT_00010 半年内即将过期金额 FROM ( SELECT case when length('[“202309“]') = 6 then '[“202309“]' || '01' WHEN length('[“202309“]') <> 8 THEN TO_CHAR(CURRENT_DATE, 'YYYYMMDD') END AS V_DATE, C.BG_CODE, C.BG_CN_NAME, C.BG_EN_NAME, C.M_ID AS METRIC_CODE --指标ID , C.M_CN AS METRIC_CN_NAME --指标中文名称 , C.M_EN AS METRIC_EN_NAME --指标英文名称 , C.CURRENCY_CODE AS CURRENCY --币种 ,CASE WHEN 1 = 0 THEN C.OVERSEA_FLAG ELSE NULL END AS OVERSEAS_FLAG,CASE WHEN 1 = 0 THEN C.REGION_CODE ELSE NULL END AS REGION_CODE,CASE WHEN 1 = 0 THEN C.REGION_CN_NAME ELSE NULL END AS REGION_CN_NAME,CASE WHEN 1 = 0 THEN C.REGION_EN_NAME ELSE NULL END AS REGION_EN_NAME,CASE WHEN 1 = 0 THEN C.REPOFFICE_CODE ELSE NULL END AS REPOFFICE_CODE,CASE WHEN 1 = 0 THEN C.REPOFFICE_CN_NAME ELSE NULL END AS REPOFFICE_CN_NAME,CASE WHEN 1 = 0 THEN C.REPOFFICE_EN_NAME ELSE NULL END AS REPOFFICE_EN_NAME,CASE WHEN 1 = 0 THEN C.OFFICE_CODE ELSE NULL END AS OFFICE_CODE,CASE WHEN 1 = 0 THEN C.OFFICE_CN_NAME ELSE NULL END AS OFFICE_CN_NAME,CASE WHEN 1 = 0 THEN C.OFFICE_EN_NAME ELSE NULL END AS OFFICE_EN_NAME,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CODE ELSE NULL END AS REGION_CUSTCATG_CODE,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_CN_NAME ELSE NULL END AS REGION_CUSTCATG_CN_NAME,CASE WHEN 1 = 0 THEN C.REGION_CUSTCATG_EN_NAME ELSE NULL END AS REGION_CUSTCATG_EN_NAME,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CODE ELSE NULL END AS TOP_CUST_CATEGORY_CODE,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_EN_NAME ELSE NULL END AS TOP_CUST_CATEGORY_EN_NAME,CASE WHEN 1 = 0 THEN C.TOP_CUST_CATEGORY_CN_NAME ELSE NULL END AS TOP_CUST_CATEGORY_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CODE ELSE NULL END AS ACCTCUST_HQ_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_CN_NAME ELSE NULL END AS ACCTCUST_HQ_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_HQ_EN_NAME ELSE NULL END AS ACCTCUST_HQ_EN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CODE ELSE NULL END AS ACCTCUST_BRANCH_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_CN_NAME ELSE NULL END AS ACCTCUST_BRANCH_CN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_BRANCH_EN_NAME ELSE NULL END AS ACCTCUST_BRANCH_EN_NAME,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CODE ELSE NULL END AS ACCTCUST_SUBSIDIARY_CODE,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_CN_NAM ELSE NULL END AS ACCTCUST_SUBSIDIARY_CN_NAM,CASE WHEN 1 = 0 THEN C.ACCTCUST_SUBSIDIARY_EN_NAM ELSE NULL END AS ACCTCUST_SUBSIDIARY_EN_NAM,CASE WHEN 1 = 0 THEN C.COUNTRY_CODE ELSE NULL END AS COUNTRY_CODE --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_CN_NAME ELSE NULL END AS COUNTRY_CN_NAME --新增加入参 ,CASE WHEN 1 = 0 THEN C.COUNTRY_EN_NAME ELSE NULL END AS COUNTRY_EN_NAME --新增加入参 , SUM(C.AGREE_AMOUNT) AS AGREE_AMOUNT --协议金额 , SUM(C.AGREE_REMAIN_AMOUNT) AS AGREE_REMAIN_AMOUNT --协议剩余金额 , SUM(C.SIGN_AMOUNT) AS SIGN_AMOUNT --可用金额 , SUM(C.USE_AMOUNT) AS USE_AMOUNT --已使用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND NVL( C.EXPIRED_DATE, add_months(C.EFFECTIVE_DATE, C.VALID_MONTH) ) >= to_date(V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE >= to_date(V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NOT_USED_VALID_AMOUNT --未使用金额(有效期外) , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.SIGN_AMOUNT ELSE C.EFFECTIVE_TOTAL_AMOUNT END - CASE WHEN C.DSCT_TYPE = 'VOUCHER' THEN C.USE_AMOUNT ELSE C.USED_TOTAL_AMOUNT END - CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND NVL( C.EXPIRED_DATE, add_months(C.EFFECTIVE_DATE, C.VALID_MONTH) ) >= to_date(V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE >= to_date(V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NOT_USED_INVALID_AMOUNT --未使用金额(有效期内) , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE >= to_date(substr(V_DATE, 1, 6), 'yyyymm') and C.EXPIRED_DATE <= LAST_DAY(to_date(V_DATE, 'yyyymmdd')) THEN C.SIGN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_START_DATE >= to_date(substr(V_DATE, 1, 6), 'yyyymm') and C.DSCT_START_DATE <= LAST_DAY(to_date(V_DATE, 'yyyymmdd')) THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS NEW_SIGN_AMOUNT --本月新增可用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EFFECTIVE_DATE >= to_date(substr(V_DATE, 1, 6), 'yyyymm') and C.EFFECTIVE_DATE <= LAST_DAY(to_date(V_DATE, 'yyyymmdd')) THEN C.USE_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_START_DATE >= to_date(substr(V_DATE, 1, 6), 'yyyymm') and C.DSCT_START_DATE <= LAST_DAY(to_date(V_DATE, 'yyyymmdd')) THEN C.USED_TOTAL_AMOUNT ELSE NULL END ) AS NEW_USE_AMOUNT --本月新使用金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE < to_date(V_DATE, 'yyyymmdd') THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE < to_date(V_DATE, 'yyyymmdd') THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS EXPIRED_AMOUNT --本月已过期金额 , SUM( CASE WHEN C.DSCT_TYPE = 'VOUCHER' AND C.EXPIRED_DATE BETWEEN to_date(V_DATE, 'yyyymmdd') AND add_months(to_date(V_DATE, 'yyyymmdd'), 6) THEN C.AGREE_REMAIN_AMOUNT WHEN C.DSCT_TYPE in ( 'FOC', 'Volume Based List Price Adjustment', 'One-Time Discount' ) AND C.DSCT_END_DATE BETWEEN to_date(V_DATE, 'yyyymmdd') AND add_months(to_date(V_DATE, 'yyyymmdd'), 6) THEN C.EFFECTIVE_TOTAL_AMOUNT ELSE NULL END ) AS IMMED_EXPIRED_AMOUNT --半年内即将过期金额 FROM DMSALESW.DM_SALE_BUSI_DSCT_SUM_F C WHERE C.CURRENCY_CODE IN ('USD') --改为多值 AND C.BG_CODE IN ('PDCG901159') AND C.M_ID IN ( 'BUSI_DSCT_00001', 'BUSI_DSCT_00002', 'BUSI_DSCT_00003', 'BUSI_DSCT_00004', 'BUSI_DSCT_00005', 'BUSI_DSCT_00006', 'BUSI_DSCT_00007' ) --新增加字段 --AND C.M_CN IN ('#[#P_REPORT_ITEM_NAME#]#') --新增加字段 --新增加字段 GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36
) T --新增加入参
下面是执行计划：
id | operation | A-time | A-rows | E-rows | E-distinct | Peak Memory | E-memory | A-width | E-width | E-costs ----+-------------------------------------------------------------------------+----------------------+---------+---------+------------+----------------+----------+-----------+---------+----------- 1 | -> Row Adapter | 1139.637 | 7 | 245 | | 419KB | | | 1318 | 117002.27 2 | -> Vector Streaming (type: GATHER) | 1139.616 | 7 | 245 | | 777KB | | | 1318 | 117002.27 3 | -> Vector Subquery Scan on t | [1129.463, 1130.072] | 7 | 245 | | [504KB, 504KB] | 1MB | | 1318 | 116920.22 4 | -> Vector Hash Aggregate | [1129.459, 1130.067] | 7 | 245 | | [4MB, 4MB] | 16MB | [0,898] | 523 | 116920.07 5 | -> Vector Streaming(type: REDISTRIBUTE) | [1129.142, 1129.918] | 112 | 3920 | | [1MB, 1MB] | 2MB | | 523 | 116643.28 6 | -> Vector Hash Aggregate | [882.194, 987.474] | 112 | 3920 | | [8MB, 8MB] | 16MB | [861,861] | 523 | 116498.95 7 | -> CStore Scan on dmsalesw.dm_sale_busi_dsct_sum_f c | [126.343, 142.697] | 3080954 | 2135243 | | [5MB, 5MB] | 1MB | | 203 | 66116.77
可以看到，不但省去了Nest Loop的耗时，而且后面Aggregate的耗时也减少了不少。整体从3s+优化到1.2s。