案例：SubPlan exec on CN can't be shipped

问题根因

某个子查询语句只能在CN上执行，通常是这个子查询有不下推因素，比如有调用了系统表、系统视图，或者存在不下推函数等。

案例1：子查询过程中存在系统表/系统视图的查询

原始语句

    
     
       
       
         WITH error_log  AS NOT MATERIALIZED (
    SELECT 
        upper(log_column_name) AS log_column_name,
        log_error_code,
        s.char_length AS data_length,
        s.data_type,s.nullable
    FROM (SELECT 
            distinct
            unnest(string_to_array(bad_log_column_name,',')) AS log_column_name,
            unnest(string_to_array(bad_log_error_code,',')) AS log_error_code
        FROM stgltc.BAD_cfs_inv_invoice_ad_2500
    ) T, 
    (SELECT * FROM user_tab_columns WHERE table_name=lower('dlt_cfs_inv_invoice_ad_2500')) S 
    WHERE  upper(T.log_column_name)=upper(S.column_name)
) 

SELECT 
    CASE WHEN upper('ACTIVITY_NAME') IN (SELECT log_column_name FROM error_log WHERE data_type IN ('varchar','char','character','nchar','character varying','varchar2','nvarchar2','clob','text') AND log_error_code='22001'/*字符超长*/) 
        THEN SUBSTRB(ACTIVITY_NAME,0,(SELECT distinct DATA_LENGTH FROM error_log WHERE upper(log_column_name)=upper('ACTIVITY_NAME'))) 
        ELSE ACTIVITY_NAME 
    END AS ACTIVITY_NAME, 
    CASE WHEN upper('ADJUSTMENT_ID') IN (SELECT log_column_name FROM error_log WHERE data_type IN ('varchar','char','character','nchar','character varying','varchar2','nvarchar2','clob','text') AND log_error_code='22001'/*字符超长*/) 
        THEN SUBSTRB(ADJUSTMENT_ID,0,(SELECT distinct DATA_LENGTH FROM error_log WHERE upper(log_column_name)=upper('ADJUSTMENT_ID'))) 
        ELSE ADJUSTMENT_ID 
    END AS ADJUSTMENT_ID
FROM stgltc.BAD_cfs_inv_invoice_ad_2500 

        

      

    
   

改写语句

    
     
       
       
         -- 识别不下推的子查询为WITH error_log字句中的
-- SELECT * FROM user_tab_columns WHERE table_name=lower('dlt_cfs_inv_invoice_ad_2500')
--
-- 因为这部分为系统表查询，无论如何都不能下推，所以此处把这部分结果转储到一个中间表中
-- 中间表创建成行存表
CREATE TEMP TABLE s WITH(orientation=row) DISTRIBUTE BY ROUNDROBIN AS 
SELECT * FROM user_tab_columns WHERE table_name=lower('dlt_cfs_inv_invoice_ad_2500')

-- 因为整个查询涉及到的表都是列存表，之后前面创建的临时表s为行存表
-- 所以此处加一个强制走向量化的hint
WITH error_log  AS NOT MATERIALIZED (
    SELECT 
        upper(log_column_name) AS log_column_name,
        log_error_code,
        s.char_length AS data_length,
        s.data_type,s.nullable
    FROM (SELECT 
            distinct
            unnest(string_to_array(bad_log_column_name,',')) AS log_column_name,
            unnest(string_to_array(bad_log_error_code,',')) AS log_error_code
        FROM stgltc.bad_cfs_inv_invoice_ad_2500
    ) T, 
    pg_temp.S 
    WHERE  upper(T.log_column_name)=upper(S.column_name)
) 

SELECT /*+ set global(enable_force_vector_engine on)*/
    CASE WHEN upper('ACTIVITY_NAME') IN (SELECT log_column_name FROM error_log WHERE data_type IN ('varchar','char','character','nchar','character varying','varchar2','nvarchar2','clob','text') AND log_error_code='22001'/*字符超长*/) 
        THEN SUBSTRB(ACTIVITY_NAME,0,(SELECT distinct DATA_LENGTH FROM error_log WHERE upper(log_column_name)=upper('ACTIVITY_NAME'))) 
        ELSE ACTIVITY_NAME 
    END AS ACTIVITY_NAME, 
    CASE WHEN upper('ADJUSTMENT_ID') IN (SELECT log_column_name FROM error_log WHERE data_type IN ('varchar','char','character','nchar','character varying','varchar2','nvarchar2','clob','text') AND log_error_code='22001'/*字符超长*/) 
        THEN SUBSTRB(ADJUSTMENT_ID,0,(SELECT distinct DATA_LENGTH FROM error_log WHERE upper(log_column_name)=upper('ADJUSTMENT_ID'))) 
        ELSE ADJUSTMENT_ID 
    END AS ADJUSTMENT_ID
FROM stgltc.bad_cfs_inv_invoice_ad_2500 

        

      

    
   