文档首页/ 数据仓库服务 DWS/ 更多文档/ 开发指南（联盟区域）/ DWS性能调优/ SQL调优案例/ 案例：典型不下推语句整改案例集/ 案例：With-Recursive contains conflict distribution in none-recursive(xxx) recursive(xxx)
更新时间：2025-12-12 GMT+08:00
查看PDF
分享

案例：With-Recursive contains conflict distribution in none-recursive(xxx) recursive(xxx)

问题根因

非递归部分语句（UNION ALL前面部分）的执行计划为replicate计划，递归部分语句（UNION ALL后面部分）的执行计划为Hash计划，计划存在冲突。

处理方案：把非递归部分语句转储为临时表，促使临时表的分布形式和计划的分布式不一致即可。

案例1：非递归部分语句的执行计划为replicate计划

原始语句

 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
 
WITH RECURSIVE CTE AS (
    SELECT
        AOH.ORG_CODE,1 AS LEVEL,
        1 FLAG,
        LAST_UPDATE_DATE
    FROM SDIHR.HW_HR_ACCOUNT_ORG_HY_V_1100 AOH 
    WHERE AOH.ORG_CODE IN (SELECT V.ORG_CODE
        FROM SDIHR.HW_HR_ACCOUNT_ORG_HY_V_1100 V
        WHERE V.ORG_CODE IS NOT NULL
        AND AOH.ORG_CODE = V.ORG_CODE
    )

    UNION ALL

    SELECT
      AOH.ORG_CODE,T.LEVEL + 1 AS LEVEL,
      1 FLAG,
      AOH.LAST_UPDATE_DATE
    FROM SDIHR.HW_HR_ACCOUNT_ORG_HY_V_1100 AOH 
    INNER JOIN CTE T ON T.ORG_CODE = AOH.PARENT_ORG_CODE
)
SELECT
    ORG_CODE,
    MAX(LEVEL) -1 AS AOH_LEVEL,
    1 FLAG,
    LAST_UPDATE_DATE AS LAST_UPDATE_DATE1
FROM CTE
GROUP BY ORG_CODE, LAST_UPDATE_DATE

SQL自诊断信息

改写语句

 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
 
CREATE TEMP TABLE t WITH(orientation=column) DISTRIBUTE BY ROUNDROBIN
AS
SELECT
    AOH.ORG_CODE,1 AS LEVEL,
    1 FLAG,
    LAST_UPDATE_DATE
FROM SDIHR.HW_HR_ACCOUNT_ORG_HY_V_1100 AOH 
WHERE AOH.ORG_CODE IN (SELECT V.ORG_CODE
    FROM SDIHR.HW_HR_ACCOUNT_ORG_HY_V_1100 V
    WHERE V.ORG_CODE IS NOT NULL
    AND AOH.ORG_CODE = V.ORG_CODE
);

WITH RECURSIVE CTE AS (
    SELECT * FROM t

    UNION ALL

    SELECT
      AOH.ORG_CODE,T.LEVEL + 1 AS LEVEL,
      1 FLAG,
      AOH.LAST_UPDATE_DATE
    FROM SDIHR.HW_HR_ACCOUNT_ORG_HY_V_1100 AOH 
    INNER JOIN CTE T ON T.ORG_CODE = AOH.PARENT_ORG_CODE
)
SELECT
    ORG_CODE,
    MAX(LEVEL) -1 AS AOH_LEVEL,
    1 FLAG,
    LAST_UPDATE_DATE AS LAST_UPDATE_DATE1
FROM CTE
GROUP BY ORG_CODE, LAST_UPDATE_DATE

修改点对比