GaussDB(DWS)性能调优：常量标量子查询做全连接出现NestLoop导致整体慢

1、【问题描述】

由于SQL中存在标量子查询同另一查询做笛卡尔积使SQL整体慢。标量子查询，即结果集只有一行一列的子查询。这里导致的SQL语句执行慢不只是在于做笛卡尔积慢，也会使后续聚合更慢。

2、【原始语句】

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 --新增加入参

3、【原因分析】

从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      

可以看到，由于Nest Loop的存在，第8层物化算子需要用参考第7层表扫描的结果集，根据第9层的16个元组生成300791个元组之后，才能够进行Nest Loop。也就是说第8层的物化算子会被第7层阻塞，而第6层的Nest Loop算子又会被第8层的物化算子阻塞。同时，Nest Loop的上层算子是Aggregate，它属于物化算子，需要获取所有元组后才开始处理。

简而言之，该SQL的瓶颈在于Nest Loop和Aggregate都需要拿到下层算子给出的所有元组之后才能开始处理，且Nest Loop本身较慢，故尽管它们向上层算子都是流水线提交元组，在这里也造成了比较严重的阻塞。

既然找到了SQL慢的根因在于Nest Loop，那么解决的方法自然就是消除它。从SQL中可以看到，Nest Loop的作用在于把一个静态的列插入到子查询的结果集中。这个操作实际上可以通过把这个静态列直接写进子查询的SELECT中完成。

TMP作为一列放到T中后，性能有明显提升。改写后的SQL如下：

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 --新增加入参

下面是改写后SQL的执行计划：

 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。