数仓实践丨常量标量子查询做全连接导致整体慢

本文分享自华为云社区《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。

 

点击关注,第一时间了解华为云新鲜技术~

 

热门相关:傲娇总裁:蜜宠小甜妻!   百炼成仙   宝贝迷人,总裁圈住爱   试婚100天:夜少,宠上瘾   惊世第一妃