2个数仓中不等值关联优化案例
本文分享自华为云社区《GaussDB(DWS)性能调优：不等值关联优化》，作者： 门前一棵葡萄树。
场景1
使用场景：本案例适合满足以下条件的场景
- 关联条件使用OR连接
- 关联条件中使用同一列做数据筛选
原始语句
SELECT t2.PARTNER_CHANNEL_CODE AS CHANNEL_ID ,t1.COUNTRY_CODE ,t1.BRAND ,t2.CHANNEL_ID AS CHANNEL_ID2 FROM t1 LEFT JOIN t2 ON ( t2.CHANNEL_ID = t1.CHANNEL_ID AND t1.TYPE = 'DR' ) OR ( t2.PARTNER_CHANNEL_CODE = t1.CHANNEL_ID AND t1.TYPE = 'ALL' ) GROUP BY t2.PARTNER_CHANNEL_CODE ,t1.COUNTRY_CODE ,t1.BRAND ,t2.CHANNEL_ID
性能分析
通过查询计划分析发现，t1表和t2表关联走了NEST LOOP，查询整体耗时45S，NEST LOOP耗时占用整个查询执行耗时的96%。因此考虑能否通过SQL改写或HINT规避NEST LOOP。观察发现t1表和t2表包含两个关联关联条件，两个关联条件之间使用OR连接，属于非等值关联，因此不能走HASH JOIN。进一步分析SQL发现两个关联条件中都使用t1.TYPE进行过滤筛选：
(t2.CHANNEL_ID = t1.CHANNEL_ID AND t1.TYPE='DR') OR (t2.PARTNER_CHANNEL_CODE = t1.CHANNEL_ID AND t1.TYPE='ALL' )
该关联条件包含以下三种关联组合：
- t1表中t1.TYPE='DR'的行，只能使用第一个关联条件与t2表关联；
- t1表中t1.TYPE='ALL'的行，只能使用第二个关联条件与t2表关联；
- t1表中t1.TYPE NOT IN ('ALL','DR')的行，不与t2表关联，直接补空。
t1表中的一行数据只能选择这三个关联条件中的一个与t2表关联，因此该关联条件可以改写为不同关联条件的UNION ALL（UNION会去重，不等价）。
优化改写
改写后SQL如下所示：
SELECT
CHANNEL_ID
,COUNTRY_CODE
,BRAND
,CHANNEL_ID
FROM
(
SELECT
t2.PARTNER_CHANNEL_CODE AS CHANNEL_ID
,t1.COUNTRY_CODE
,t1.BRAND
,t2.CHANNEL_ID AS CHANNEL_ID2
FROM
t1
LEFT JOIN
t2
ON
t2.CHANNEL_ID = t1.CHANNEL_ID
WHERE
t1.TYPE = 'DR'
UNION ALL
SELECT
t2.PARTNER_CHANNEL_CODE AS CHANNEL_ID
,t1.COUNTRY_CODE
,t1.BRAND
,t2.CHANNEL_ID AS CHANNEL_ID2
FROM
t1
t2
ON t2.PARTNER_CHANNEL_CODE = t1.CHANNEL_ID
WHERE t1.TYPE='ALL'
UNION ALL
SELECT
t2.PARTNER_CHANNEL_CODE AS CHANNEL_ID
,t1.COUNTRY_CODE
,t1.BRAND
,t2.CHANNEL_ID AS CHANNEL_ID2
FROM t1
LEFT JOIN
t2
ON FALSE
WHERE t1.TYPE NOT IN ('ALL','DR')
)
GROUP BY CHANNEL_ID,COUNTRY_CODE,BRAND,CHANNEL_ID
改写后SQL变为三个子查询的UNION ALL，执行时间缩减至1s以内，性能优化45倍。
场景二
使用场景：本案例适合满足以下条件的场景
- 大表A不等值关联小表B
- B的等值关联字段为主键
【原始语句】
SELECT T.CREATE_INVOICE_USER, T.PERIOD_ID, T.AP_INVOICE_ID, T.AP_INVOICE_NUM, T.AP_BATCH_NAME, EMP1.EMPLOYEE_NO, EMP1.EMPLOYEE_NAME FROM DWACTDI.DWR_AP_GLOBAL_INVOICE_DETAIL_F_I T LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D EMP1 ON (EMP1.SCD_ACTIVE_IND = 1 AND(T.CREATE_INVOICE_USER = EMP1.EMPLOYEE_NO OR SUBSTR(T.CREATE_INVOICE_USER, 2) = EMP1.EMPLOYEE_NO))
【性能分析】
原始语句执行超时(超过1h)，执行计划如下。可以看到执行语句存在大表NestLoop操作
分析发现表dwrdim_dw1.dwr_dim_employee_d是维度表，且关联列employee_no是主键
【优化改写】
SELECT T.CREATE_INVOICE_USER, T.PERIOD_ID, T.AP_INVOICE_ID, T.AP_INVOICE_NUM, T.AP_BATCH_NAME, nvl(EMP1_0.EMPLOYEE_NO, EMP1_1.EMPLOYEE_NO) AS EMPLOYEE_NO, nvl(EMP1_0.EMPLOYEE_NAME, EMP1_1.EMPLOYEE_NAME) AS ERP_ACCOUNTANT_ENAME FROM DWACTDI.DWR_AP_GLOBAL_INVOICE_DETAIL_F_I T LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D EMP1_0 ON (EMP1_0.SCD_ACTIVE_IND = 1 AND(T.CREATE_INVOICE_USER = EMP1_0.EMPLOYEE_NO)) LEFT JOIN DWRDIM_DW1.DWR_DIM_EMPLOYEE_D EMP1_1 ON (EMP1_1.SCD_ACTIVE_IND = 1 AND(SUBSTR(T.CREATE_INVOICE_USER, 2) = EMP1_1.EMPLOYEE_NO))
改写后执行信息如下
