Help Center/ Data Warehouse Service / Developer Guide/ Developer Guide (9.1.0.x)/ DWS Performance Tuning/ SQL Tuning Examples/ Cases: Modifying Statements That Cannot Be Pushed Down/ Case: WITH-RECURSIVE Contains Conflict Distribution in None-RECURSIVE (Xxx) RECURSIVE (Xxx)
Updated on 2026-01-04 GMT+08:00
View PDF
Share

Case: WITH-RECURSIVE Contains Conflict Distribution in None-RECURSIVE (Xxx) RECURSIVE (Xxx)

Possible Cause

The non-recursive part (before UNION ALL) uses a replicate plan, while the recursive part (after UNION ALL) uses a hash plan. These plans don't match.

Solution: Dump the non-recursive statements to a temporary table to ensure that the distribution of the temporary table is different from that of the plan.

Case 1: Using the Replicate Plan for Non-RECURSIVE Statements

Original statement

 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 self-diagnosis information

Rewritten statement

 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

Modification comparison

Parent Topic: Cases: Modifying Statements That Cannot Be Pushed Down