您现在的位置是：首页 > 文章详情
数仓实践丨常量标量子查询做全连接导致整体慢
日期：2023-11-30点击：315收藏
本文分享自华为云社区《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。
关注公众号
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有，本站原创内容转载请注明来源。
-
上一篇
从 Linux Crontab 到 K8s CronJob，定时任务正在经历怎样的变革
背景 Job 表示短周期的作业，定时 Job 表示按照预定的时间运行Job，或者按照某一频率周期性的运行 Job。比如： 许多传统企业使用 Linux 自带的 crontab 来做定时任务的方案，该方案非常简单，适合做主机上的运维工作，比如定时清理日志、周期性做健康检查。随着信息化时代的高速发展，业务变得越来越复杂，很多场景都需要定时任务，但是 crontab 方案存在高可用问题，不适合应用在业务应用上。 在云原生时代，K8s CronJob 设计了一套高可用的定时任务解决方案，保障了业务的稳定。但是把 K8s CronJob 应用在生产上，发现定时任务真的出问题的时候排查起来很麻烦，于是越来越多用户对定时任务的可观测有了更多的诉求，阿里云也推出了自己的云原生定时任务解决方案，可以托管原生 K8s CronJob，提供可报警、可观测、可运维等能力，帮助企业提效。 Linux Crontab 方案面临的问题 什么是 Crontab Crontab 是 Linux 系统中的一个服务，用于创建、编辑和管理定时任务。通过 crontab 命令，用户可以设置系统在指定时间自动执行某个命令或脚本。...
-
下一篇
DrissionPage v3.2.35 已经发布，WEB 自动化测试集成工具
DrissionPage v3.2.35 已经发布，WEB 自动化测试集成工具 此版本更新内容包括： 浏览器最小化时也能响应模拟操作 接管浏览器无须'--remote-allow-origins=*'参数 tabs属性忽略隐私声明 修复 8x 版浏览器选择下拉列表时报错问题 修复某些情况下下拉框不触发联动的问题 修复配置文件损坏时出现的问题 修复get()方法url参数含某些特殊字符时连接失败的问题 详情查看：https://gitee.com/g1879/DrissionPage/releases/v3.2.35
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
扫描即可查看该文章
微信收款码
支付宝收款码