数仓实践丨常量标量子查询做全连接导致整体慢
本文分享自华为云社区《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。
