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

案例：With-Recursive contains system table is not shippable

问题根因

递归语句的某个分支中没有完整的From子句，仅使用系统表或者系统视图(DUAL也被视为系统视图)代替。

案例1：递归驱动分支是FROM DUAL查询

原始语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
WITH recursive cte AS (
    SELECT 
        TO_DATE(201701, 'YYYYMM') as level ,TO_DATE(20170131, 'YYYYMMDD')  LASTDAY  
    FROM dual 

    UNION ALL 

    SELECT
        ADD_MONTHS(cte.LEVEL,   1) AS PERIOD,
        LAST_DAY(ADD_MONTHS(cte.LEVEL,   1)) AS LASTDAY
    FROM cte WHERE cte.LEVEL <=SYSDATE 
) 
SELECT 
    TO_CHAR(cte.level,'YYYYMMDD') AS PERIOD , cte.LASTDAY 
    FROM cte
WHERE TO_CHAR(cte.level,'YYYYMMDD')<= TO_CHAR(SYSDATE,'YYYYMMDD')

改写语句

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
 
WITH recursive cte AS (
    SELECT 
        TO_DATE(201701, 'YYYYMM') as level ,TO_DATE(20170131, 'YYYYMMDD')  LASTDAY  
    FROM generate_series(1, 1) 

    UNION ALL 

    SELECT
        ADD_MONTHS(cte.LEVEL,   1) AS PERIOD,
        LAST_DAY(ADD_MONTHS(cte.LEVEL,   1)) AS LASTDAY
    FROM cte WHERE cte.LEVEL <=SYSDATE 
) 
SELECT 
    TO_CHAR(cte.level,'YYYYMMDD') AS PERIOD , cte.LASTDAY 
    FROM cte
WHERE TO_CHAR(cte.level,'YYYYMMDD')<= TO_CHAR(SYSDATE,'YYYYMMDD')

修改点对比