# 案例：With-Recursive contains conflict distribution in none-recursive(xxx) recursive(xxx)
#### 问题根因
非递归部分语句（UNION ALL前面部分）的执行计划为replicate计划，递归部分语句（UNION ALL后面部分）的执行计划为Hash计划，计划存在冲突。
**处理方案**：把非递归部分语句转储为临时表，促使临时表的分布形式和计划的分布式不一致即可。
#### 案例1：非递归部分语句的执行计划为replicate计划
**原始语句**
```
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自诊断信息**
![](https://support.huaweicloud.com/devg-911-dws/figure/zh-cn_image_0000002494126958.png "点击放大")
**改写语句**
```
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
```
**修改点对比**
![](https://support.huaweicloud.com/devg-911-dws/figure/zh-cn_image_0000002494127274.png "点击放大")