where语句中的假脱机空间错误

Question

我被指定运行此查询，但它返回假脱机错误。我被告知去掉加粗的where语句，并使用它来创建categoryG exclusions表，然后添加另一个新步骤以将该表与表的其余部分连接起来。我是Teradata的新手，非常迷茫。希望有人能帮忙。谢谢!

CREATE VOLATILE TABLE A
    ,NO FALLBACK
    ,NO BEFORE JOURNAL
    ,NO AFTER JOURNAL AS 
(
    SELECT 'TX' AS STATE
        ,ADD_MONTHS(li.column7 - EXTRACT(DAY FROM li.Column21) + 1, 1) - 1 AS column12
        ,ADD_MONTHS(li.Column22 - EXTRACT(DAY FROM li.Column22) + 1, 1) - 1 AS column13
        ,EXTRACT(YEAR FROM column13) * 12 + EXTRACT(MONTH FROM column13) - EXTRACT(YEAR FROM column12) * 12 - EXTRACT(MONTH FROM column12) AS offset
        ,column8
        ,CASE 
            WHEN clm.Column20 = '01strong text'
                AND li.Column23 = '1'
                THEN 'INP'
            WHEN clm.Column20 = '01'
                AND li.Column23 IN (
                    '2'
                    ,'3'
                    )
                THEN 'OUT'
            WHEN clm.Column20 = '02'
                AND CHARACTER_LENGTH(TRIM(Column24)) <> 5
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 1 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 2 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 3 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 4 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 5 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND Column24 BETWEEN '00000'
                    AND '99999'
                THEN 'PRO'
            WHEN clm.Column20 = '02'
                THEN 'OME'
            WHEN clm.Column20 = '03'
                THEN 'PDP'
            WHEN clm.Column20 = '04'
                THEN 'DEN'
            ELSE ''
            END AS column9
        ,CASE 
            WHEN fcg.column15 IN (
                    '000166915'
                    ,'000166916'
                    ,'000166913'
                    ,'000168717'
                    ,'000168718'
                    )
                THEN 'Y'
            WHEN fcg.column15 IN (
                    '000168719'
                    ,'000166920'
                    ,'000166917'
                    ,'000166919'
                    ,'000166914'
                    ,'000168716'
                    )
                THEN 'N'
            WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '1'
                THEN 'Y'
            WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '2'
                THEN 'N'
            ELSE ''
            END AS column10
        ,CASE 
            WHEN fcg.Column25 IN (
                    '0004'
                    ,'0005'
                    ,'0006'
                    )
                THEN 'Y'
            ELSE 'N'
            END column11
        ,SUM(li.Column26) AS Column26
        ,SUM(li.Column27) AS Column27
        ,SUM(li.Column28) AS paid_amt
        ,SUM(CASE 
                WHEN clm.Column20 = '01'
                    THEN li.Column26 - li.non_covd_amt - li.Column30
                ELSE li.Column29
                END) AS amount 
    FROM DatabaseLi li 
        INNER JOIN DatabaseFCG fcg 
            ON fcg.column30 = li.column30
            AND li.disp_cd = 'A' 
        INNER JOIN Database11 clm 
            ON clm.column30 = li.column30 
        INNER JOIN Database11_mbr MBR 
            ON li.column30 = MBR.column30 
        LEFT JOIN / CategoryG exclusion / 
            (
                SELECT column30
                FROM Database11_src
                WHERE Column50IN('MA', 'H8')
                GROUP BY 1
            ) src 
            ON li.column30 = src.column30 
        INNER JOIN 
            (
                SELECT column15
                    ,CASE 
                        WHEN product_id LIKE '%apple%'
                            THEN 'apple'
                        WHEN product_id LIKE '%orange%'
                            THEN 'orange'
                        WHEN product_id LIKE '%banana%'
                            THEN 'apple'
                        ELSE ''
                        END AS column8
                FROM DatabaseDD
                WHERE lob IN ('Categrory3')
                    AND product_id IS NOT NULL
                    AND product_id NOT LIKE '%LV%'
                GROUP BY 1,2
            ) AS a 
            ON a.column15 = fcg.column15 
    /*This is the WHERE statement to remove*/
    WHERE /* Excluding ALL CategoryG after 2014 - 01 - 01 */

        li.column7 >= '2014-01-01'
        AND src.column30 IS NULL        
        AND clm.Column20 <> '04'
        AND NOT MBR.column55 IN (
                SELECT DISTINCT a.column55
                FROM DatabaseA a
                INNER JOIN DatabaseB b ON a.column1 = b.column1
                    AND a.column2 = b.column2
                    AND a.column3 = b.column3
                    AND a.column4 = b.column4
                    AND a.column5 = '11'
                    AND a.column14 IN (
                        SELECT DISTINCT column15
                        FROM DatabaseDD
                        WHERE lob LIKE '%Categrory3%'
                        )
                    AND a.column6 <> 'IND'
                )           
        AND NOT fcg.column15 IN (
                SELECT DISTINCT a.column14
                FROM DatabaseA a
                INNER JOIN DatabaseB b ON a.column1 = b.column1
                    AND a.column2 = b.column2
                    AND a.column3 = b.column3
                    AND a.column4 = b.column4
                    AND a.column5 = '11'
                    AND a.column14 IN (
                        SELECT DISTINCT column15
                        FROM DatabaseDD
                        WHERE lob LIKE '%Categrory3%'
                        )
                    AND a.column6 <> 'IND'
                )
        AND li.column7 < '2016-01-01'
    GROUP BY 1,2,3,4,5,6,7,8
    )
    WITH DATA UNIQUE PRIMARY INDEX (
            STATE
            ,column8
            ,column9
            ,column10
            ,column11
            ,column12
            ,column13
            ) ON COMMIT PRESERVE ROWS;

我尝试了select的第一个块的explain，但不确定它是什么意思- explain SELECT DISTINCT ( xxx ) 1)首先，我们锁定xxx进行访问，在视图xxx中锁定xxx进行访问，然后在视图xxx中锁定xxx进行访问。2)接下来，我们从视图xxx中的xxx执行all-AMPs检索步骤，通过条件(Xxx)的所有行扫描进入缓冲池1 (all_amps)，该缓冲池1( AMPs )是本地构建在AMPs上的。假脱机1的大小估计为225,735,188行(9,255,142,708字节)。此步骤的估计时间为11.64秒。3)并行执行以下步骤。1)我们从视图xxx中的xxx执行all-AMPs检索步骤，通过条件(xxx)的全行扫描进入缓冲池1 (all_amps)，该缓冲池1( AMPs )是本地构建在AMPs上的。假脱机1的大小估计为241,718,108行(9,910,442,428字节)。此步骤的估计时间为1.21秒。2)我们通过全行扫描的方式从视图xxx中的xxx执行all-AMPs检索步骤，其中条件为(xxx)进入Spool5 (all_amps) (允许压缩列)，其被扇出成10个散列连接分区，其通过散列代码(xxx)被重新分发到所有AMP。假脱机5的大小估计为44,346,884行( 2,172,997,316字节)。此步骤的估计时间为11.23秒。4)我们通过以(xxx)为条件的全行扫描的方式从Spool 1(最后使用)执行all-AMPs检索步骤，进入Spool 6 (all_amps) (允许压缩的列)，将其扇出成10个散列连接分区，其通过散列代码(xxx)重分布到所有AMP。假脱机6的大小估计为241,718,108行( 9,910,442,428字节)。此步骤的估计时间为8.49秒。5)我们通过全行扫描的方式从Spool 5 (Last Use)执行all-AMPs步骤，通过全行扫描的方式将其加入Spool 6 (Last Use)。假脱机5和假脱机6使用10个分区的散列联接，连接条件为(xxx)，结果进入假脱机4 (all_amps) (允许压缩列)，该假脱机4(允许压缩列)在AMP上本地构建。假脱机4的大小估计为45,125,207行( 1,037,879,761字节)。此步骤的估计时间为1.00秒。6)我们执行全AMPs求和步骤，通过全行扫描的方式从Spool 4(上次使用)聚合，按field1 ( xxx)分组。聚合中间结果是全局计算的，然后放在Spool 2中。Spool 2的大小估计为18,209,884行(528,086,636字节)。此步骤的估计时间为1.34秒。7)最后，我们向处理请求所涉及的所有AMP发送一个结束事务步骤。->将缓冲池2的内容作为语句1的结果发回给用户。总估计时间为33.70秒。

Answer 1

除了JNevill在他的回答中提供的合理建议之外，我还建议与您的数据库管理员一起评估查询的DBQL Step Info，以便准确地了解查询中发生错误的位置。您可能会发现，其他统计信息可能会影响优化器的计划，从而避免假脱机错误。

因为这是一个易失性表，所以它是在用户的假脱机空间中创建的。如果查询在查询计划的中间步骤中没有失败，则可能是因为没有足够的假脱机空间来实体化假脱机中的易失性表。如果这是在用户会话中执行的其他语句中的一条语句，并产生其他易失性表，则可能是导致问题的各个部分的总和。

Answer 2

假脱机空间是存储中间结果集的地方。这些通常来自子查询(但也包括在连接之前预过滤的表和其他许多东西)。因此，为了解决假脱机空间问题，一个很好的第一步是将子查询删除到它们自己的易失性表中。然后将这些易失性表连接到。

这可能看起来像这样：

CREATE MULTISET VOLATILE TABLE volatile_1 AS
(
    SELECT DISTINCT a.column55
    FROM DatabaseA a
    INNER JOIN DatabaseB b ON a.column1 = b.column1
        AND a.column2 = b.column2
        AND a.column3 = b.column3
        AND a.column4 = b.column4
        AND a.column5 = '11'
        AND a.column14 IN (
            SELECT DISTINCT column15
            FROM DatabaseDD
            WHERE lob LIKE '%Categrory3%'
            )
        AND a.column6 <> 'IND'
) WITH DATA ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE volatile_2 AS
(
    SELECT DISTINCT a.column14
    FROM DatabaseA a
    INNER JOIN DatabaseB b ON a.column1 = b.column1
        AND a.column2 = b.column2
        AND a.column3 = b.column3
        AND a.column4 = b.column4
        AND a.column5 = '11'
        AND a.column14 IN (
            SELECT DISTINCT column15
            FROM DatabaseDD
            WHERE lob LIKE '%Categrory3%'
            )
        AND a.column6 <> 'IND'
) WITH DATA ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE volatile_src AS
(
    SELECT column30
    FROM Database11_src
    WHERE Column50IN('MA', 'H8')
    GROUP BY 1
) WITH DATA ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE volatile_src AS
(
    SELECT column15
        ,CASE 
            WHEN product_id LIKE '%apple%'
                THEN 'apple'
            WHEN product_id LIKE '%orange%'
                THEN 'orange'
            WHEN product_id LIKE '%banana%'
                THEN 'apple'
            ELSE ''
            END AS column8
    FROM DatabaseDD
    WHERE lob IN ('Categrory3')
        AND product_id IS NOT NULL
        AND product_id NOT LIKE '%LV%'
    GROUP BY 1,2
) WITH DATA ON COMMIT PRESERVE ROWS;

CREATE VOLATILE TABLE A
    ,NO FALLBACK
    ,NO BEFORE JOURNAL
    ,NO AFTER JOURNAL AS 
(
    SELECT 'TX' AS STATE
        ,ADD_MONTHS(li.column7 - EXTRACT(DAY FROM li.Column21) + 1, 1) - 1 AS column12
        ,ADD_MONTHS(li.Column22 - EXTRACT(DAY FROM li.Column22) + 1, 1) - 1 AS column13
        ,EXTRACT(YEAR FROM column13) * 12 + EXTRACT(MONTH FROM column13) - EXTRACT(YEAR FROM column12) * 12 - EXTRACT(MONTH FROM column12) AS offset
        ,column8
        ,CASE 
            WHEN clm.Column20 = '01strong text'
                AND li.Column23 = '1'
                THEN 'INP'
            WHEN clm.Column20 = '01'
                AND li.Column23 IN (
                    '2'
                    ,'3'
                    )
                THEN 'OUT'
            WHEN clm.Column20 = '02'
                AND CHARACTER_LENGTH(TRIM(Column24)) <> 5
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 1 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 2 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 3 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 4 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND SUBSTRING(TRIM(Column24) FROM 5 FOR 1) BETWEEN 'A'
                    AND 'Z'
                THEN 'OME'
            WHEN clm.Column20 = '02'
                AND Column24 BETWEEN '00000'
                    AND '99999'
                THEN 'PRO'
            WHEN clm.Column20 = '02'
                THEN 'OME'
            WHEN clm.Column20 = '03'
                THEN 'PDP'
            WHEN clm.Column20 = '04'
                THEN 'DEN'
            ELSE ''
            END AS column9
        ,CASE 
            WHEN fcg.column15 IN (
                    '000166915'
                    ,'000166916'
                    ,'000166913'
                    ,'000168717'
                    ,'000168718'
                    )
                THEN 'Y'
            WHEN fcg.column15 IN (
                    '000168719'
                    ,'000166920'
                    ,'000166917'
                    ,'000166919'
                    ,'000166914'
                    ,'000168716'
                    )
                THEN 'N'
            WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '1'
                THEN 'Y'
            WHEN SUBSTRING(fcg.column15 FROM 6 FOR 1) = '2'
                THEN 'N'
            ELSE ''
            END AS column10
        ,CASE 
            WHEN fcg.Column25 IN (
                    '0004'
                    ,'0005'
                    ,'0006'
                    )
                THEN 'Y'
            ELSE 'N'
            END column11
        ,SUM(li.Column26) AS Column26
        ,SUM(li.Column27) AS Column27
        ,SUM(li.Column28) AS paid_amt
        ,SUM(CASE 
                WHEN clm.Column20 = '01'
                    THEN li.Column26 - li.non_covd_amt - li.Column30
                ELSE li.Column29
                END) AS amount 
    FROM DatabaseLi li 
        INNER JOIN DatabaseFCG fcg 
            ON fcg.column30 = li.column30
            AND li.disp_cd = 'A' 
        INNER JOIN Database11 clm 
            ON clm.column30 = li.column30 
        INNER JOIN Database11_mbr MBR 
            ON li.column30 = MBR.column30 
        LEFT JOIN volatile_src src 
            ON li.column30 = src.column30 
        INNER JOIN volatile_a AS a 
            ON a.column15 = fcg.column15 
    WHERE / Excluding ALL CategoryG after 2014 - 01 - 01 /      
        li.column7 >= DATE '2014-01-01'
        AND src.column30 IS NULL        
        AND clm.Column20 <> '04'
        AND NOT MBR.column55 IN (SELECT * FROM volatile_1)
        AND NOT fcg.column15 IN (SELECT * FROM volatile_2)
        AND li.column7 < '2016-01-01'
    GROUP BY 1,2,3,4,5,6,7,8
    )
    WITH DATA UNIQUE 
    PRIMARY INDEX 
        (
            STATE
            ,column8
            ,column9
            ,column10
            ,column11
            ,column12
            ,column13
        ) 
    ON COMMIT PRESERVE ROWS;

如果您仍然遇到假脱机问题，请分解查询。删除SELECT and WHERE子句中的所有联接和对联接表的任何引用，然后运行它。它会脱机吗？如果不是，则添加下一个，它是相关的SELECT和WHERE子句项。运行它。它会脱机吗？如果不是，则添加到下一表中。继续这样做，直到你脱机。您可以确定可以通过这一查询推送多少数据。

您可能需要使用UPDATE语句将其分解为更多的语句，以便将更多的数据从连接写入记录。

您可能需要向您的DBA请求更多的缓冲池。如果这是一次性的事情，也许有人有更多的缓冲池可以为你运行它？