文档首页/ 数据仓库服务 DWS/ 更多文档/ 开发指南（联盟区域）/ DWS性能调优/ SQL调优案例/ 案例：典型不下推语句整改案例集/ 案例：main plan exec on CN and SubPlan exec on DN can't be shipped
更新时间：2025-12-12 GMT+08:00
查看PDF
分享

案例：main plan exec on CN and SubPlan exec on DN can't be shipped

问题根因

根因：复杂语句中的主查询为不涉及用户表的查询，但相关的子查询是涉及用户表的查询语句。

案例1：主查询来源表是维表DUAL

原始语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
 
SELECT 
    1 
FROM (SELECT 1 
    FROM dual 
    WHERE -2 = -2
    AND 202210 IN(SELECT /*+ PARALLEL(8) */ 
        DISTINCT PERIOD_ID
        FROM DWICBG_VI.CBGDWR_PSI_FIN_OVER_INV_V T 
        WHERE T.LAST_MODIFIED_DATE > (SELECT NVL(MAX(LAST_MODIFIED_DATE), TRUNC(SYSDATE) - 1) from FIN_DWB_MSS.DWB_MSS_PSI_FIN_OVER_INV_M_F) AND T.LAST_MODIFIED_DATE <= (SELECT  TRUNC(SYSDATE) + 1) 
    )
) t LIMIT 1

改写后语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
SELECT 
    1 
FROM(SELECT 1 
    FROM (SELECT /*+ PARALLEL(8) */
            DISTINCT PERIOD_ID
        FROM DWICBG_VI.CBGDWR_PSI_FIN_OVER_INV_V T 
        WHERE T.LAST_MODIFIED_DATE > (SELECT NVL(MAX(LAST_MODIFIED_DATE), TRUNC(SYSDATE) - 1) from FIN_DWB_MSS.DWB_MSS_PSI_FIN_OVER_INV_M_F) AND T.LAST_MODIFIED_DATE <= (SELECT  TRUNC(SYSDATE) + 1) 
    ) t(PERIOD_ID) 
where PERIOD_ID = 202210
) t LIMIT 1

修改点对比