Teradata使用LEFT JOIN时遇Spool空间不足错误求助

云原生阿岚

2026-6-27

解决Teradata查询"No more spool space in user"错误

执行以下查询时触发"No more spool space in user"错误：

SEL
C.*,AD.*,P.*
FROM mytestDB.TABLE_1 C
 INNER JOIN mytestDB.TABLE_2 D
 ON C.COL_A     = D.COL_A
 AND C.COL_B = D.COL_B

LEFT OUTER JOIN mytestDB.TABLE_3 AD
 ON C.COL_A = AD.COL_A
 AND C.COL_B = AD.COL_B

LEFT OUTER JOIN mytestDB.TABLE_4 P
 ON C.COL_A = P.COL_A
 AND C.COL_B = P.COL_B;

观察结果

移除任一LEFT OUTER JOIN后，查询可正常返回结果；
使用count(*)替代列列表时，查询也可正常执行；
仅选择1-2列时，查询可在viewpoint中正常返回数据；
查询执行期间Spool skew约为70-80。

表信息

涉及的4张表均使用相同的PI列（COL_A, COL_B），且已在该PI列上收集统计信息，所有表的Skew factor均≤2。各表的总行数及基于PI列的唯一行数如下：

--TOTAL Count
sel count(1) from  mytestDB.TABLE_1; 89,758,653
sel count(1) from  mytestDB.TABLE_2; 148,915,580
sel count(1) from  mytestDB.TABLE_3; 12,446,171
sel count(1) from  mytestDB.TABLE_4; 160,661

-- DISTINCT PI count
sel count( 1) from (sel distinct  COL_A,COL_B  from  mytestDB.TABLE_1)a; 89,758,653
sel count( 1) from (sel distinct  COL_A,COL_B  from  mytestDB.TABLE_2)a; 64,616,959
sel count( 1) from (sel distinct  COL_A,COL_B  from  mytestDB.TABLE_3)a; 11,032,454
sel count( 1) from (sel distinct  COL_A,COL_B  from  mytestDB.TABLE_4)a;     121,860

执行计划

1) First, we lock mytestDB.AD in TD_MAP1 for read on a reserved
RowHash in all partitions to prevent global deadlock.
2) Next, we lock mytestDB.P in TD_MAP1 for read on a reserved
RowHash in all partitions to prevent global deadlock.
3) We lock mytestDB.D in TD_MAP1 for read on a reserved RowHash in
all partitions to prevent global deadlock.
4) We lock mytestDB.C in TD_MAP1 for read on a reserved RowHash in
all partitions to prevent global deadlock.
5) We lock mytestDB.AD in TD_MAP1 for read, we lock mytestDB.P in
TD_MAP1 for read, we lock mytestDB.D in TD_MAP1 for read, and we
lock mytestDB.C in TD_MAP1 for read.
6) We execute the following steps in parallel.
  1) We do an all-AMPs JOIN step in TD_MAP1 from mytestDB.C by
     way of a RowHash match scan with no residual conditions,
     which is joined to mytestDB.AD by way of a RowHash match
     scan with no residual conditions.  mytestDB.C and
     mytestDB.AD are left outer joined using a rowkey-based
     merge join, with a join condition of (
     "(mytestDB.C.COL_A = mytestDB.AD.COL_A) AND
     (mytestDB.C.COL_B = mytestDB.AD.COL_B)").
     The result goes into Spool 2 (all_amps), which is built
     locally on the AMPs.  Then we do a SORT to partition Spool 2
     by rowkey.  The size of Spool 2 is estimated with low
     confidence to be 89,797,457 rows (12,212,454,152 bytes).  The
     estimated time for this step is 7.85 seconds.
  2) We do an all-AMPs RETRIEVE step in TD_MAP1 from mytestDB.P
     by way of an all-rows scan with no residual conditions into
     Spool 5 (all_amps), which is built locally on the AMPs.  The
     size of Spool 5 is estimated with high confidence to be
     12,476,097 rows (1,147,800,924 bytes).  The estimated time
     for this step is 0.39 seconds.
7) We do an all-AMPs JOIN step in TD_Map1 from Spool 2 (Last Use) by
way of a RowHash match scan, which is joined to mytestDB.D by
way of a RowHash match scan with no residual conditions.  Spool 2
and mytestDB.D are joined using a rowkey-based merge join, with
a join condition of ("(COL_B = mytestDB.D.COL_B)
AND (COL_A = mytestDB.D.COL_A)").  The result goes into
Spool 6 (all_amps), which is redistributed by the hash code of (
mytestDB.C.COL_A, mytestDB.C.COL_B) to all AMPs in
TD_Map1.  The size of Spool 6 is estimated with low confidence to
be 149,320,197 rows (20,307,546,792 bytes).  The estimated time
for this step is 14.00 seconds.
8) We do an all-AMPs JOIN step in TD_Map1 from Spool 5 (Last Use) by
way of an all-rows scan, which is joined to Spool 6 (Last Use) by
way of an all-rows scan.  Spool 5 and Spool 6 are right outer
joined using a single partition hash join, with a join condition
of ("(COL_A = COL_A) AND (COL_B = COL_B)").
The result goes into Spool 1 (group_amps), which is built locally
on the AMPs.  The size of Spool 1 is estimated with low confidence
to be 151,674,776 rows (37,463,669,672 bytes).  The estimated time
for this step is 9.80 seconds.
9) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 1 are sent back to the user as the result of
statement 1.  The total estimated time is 31.65 seconds.

解决建议

1. 调整JOIN顺序与精简返回列

优先执行INNER JOIN：先将TABLE_1与TABLE_2做INNER JOIN过滤掉不匹配的行，再执行后续LEFT JOIN，减少中间数据集的大小。改写后的查询如下：

SELECT CD.*, AD.*, P.*
FROM (
    SELECT C.*, D.*
    FROM mytestDB.TABLE_1 C
    INNER JOIN mytestDB.TABLE_2 D
        ON C.COL_A = D.COL_A AND C.COL_B = D.COL_B
) CD
LEFT OUTER JOIN mytestDB.TABLE_3 AD
    ON CD.COL_A = AD.COL_A AND CD.COL_B = AD.COL_B
LEFT OUTER JOIN mytestDB.TABLE_4 P
    ON CD.COL_A = P.COL_A AND CD.COL_B = P.COL_B;

避免SELECT *：只选择业务需要的列，减少每行的字节占用，降低Spool空间需求。原查询返回所有列导致中间Spool数据量预估超过37GB，精简列能显著缓解这个问题。

2. 缓解Spool Skew问题

强制重新分布中间结果：执行SET SESSION REDISTRIBUTE_ON_JOIN = ON;，让优化器在JOIN后重新分布数据，避免单AMP负载过高。
排查异常PI值：查询是否存在COL_A/COL_B的特定组合对应大量行，导致Skew。可以通过以下语句检查：
```
SELECT COL_A, COL_B, COUNT(1) AS row_count
FROM mytestDB.TABLE_2
GROUP BY COL_A, COL_B
ORDER BY row_count DESC
LIMIT 10;
```
临时调整Spool配额：联系DBA临时增加用户的Spool空间配额，或者使用SET SESSION SPOOLSPACE = <size>;（需权限）临时提升配额。

3. 优化执行计划准确性

补充统计信息：除PI列外，收集表的全表统计或JOIN相关列的统计，帮助优化器更准确预估数据量：
```
COLLECT STATISTICS ON mytestDB.TABLE_2;
COLLECT STATISTICS COLUMN (COL_A, COL_B) ON mytestDB.TABLE_3;
```
使用查询提示：添加OPTIMIZE FOR ALL ROWS提示，让优化器基于全量数据生成计划，解决原计划中低置信度预估的问题：
```
SELECT /*+ OPTIMIZE FOR ALL ROWS */
C.*,AD.*,P.*
FROM mytestDB.TABLE_1 C
... -- 原JOIN逻辑
```
指定JOIN类型：针对数据量较小的表（如TABLE_4），使用USE_HASH提示强制哈希JOIN，减少Spool排序开销：
```
SELECT /*+ USE_HASH(C P) */
C.*,AD.*,P.*
FROM mytestDB.TABLE_1 C
... -- 原JOIN逻辑
```

4. 其他优化方式

使用临时表存储中间结果：将TABLE_1与TABLE_2的INNER JOIN结果存入临时表，指定相同的PI列，再执行后续LEFT JOIN，避免重复计算和数据重分布：

CREATE VOLATILE TABLE CD_TEMP AS (
    SELECT C.*, D.*
    FROM mytestDB.TABLE_1 C
    INNER JOIN mytestDB.TABLE_2 D
        ON C.COL_A = D.COL_A AND C.COL_B = D.COL_B
) WITH DATA PRIMARY INDEX(COL_A, COL_B) ON COMMIT PRESERVE ROWS;

SELECT CD_TEMP.*, AD.*, P.*
FROM CD_TEMP
LEFT OUTER JOIN mytestDB.TABLE_3 AD
    ON CD_TEMP.COL_A = AD.COL_A AND CD_TEMP.COL_B = AD.COL_B
LEFT OUTER JOIN mytestDB.TABLE_4 P
    ON CD_TEMP.COL_A = P.COL_A AND CD_TEMP.COL_B = P.COL_B;

去重处理：如果TABLE_2存在重复的PI值行，先去重再JOIN，减少数据膨胀：

SELECT C.*, D.*, AD.*, P.*
FROM mytestDB.TABLE_1 C
INNER JOIN (
    SELECT DISTINCT COL_A, COL_B, <其他需要的列>
    FROM mytestDB.TABLE_2
) D ON C.COL_A = D.COL_A AND C.COL_B = D.COL_B
... -- 后续LEFT JOIN逻辑

内容的提问来源于stack exchange，提问作者Sbala

Teradata使用LEFT JOIN时遇Spool空间不足错误求助

观察结果 #

表信息 #

执行计划 #

解决建议 #

1. 调整JOIN顺序与精简返回列 #

2. 缓解Spool Skew问题 #

3. 优化执行计划准确性 #

4. 其他优化方式 #