0

Scenarios where Spool error occurs in Teradata

Does join clause can get the spool error

I have implemented a query where there is a join similar to

SELECT <case_stmt> FROM Tabel_1 A JOIN Tabel_2 B ON A.column = B.column sample 10;

and i also tried with other columns suspecting that only one AMP encountering the load because of only one column

SELECT <some_columns from table_1 and table_2>, <case_stmt> FROM Tabel_1 A JOIN Tabel_2 B ON A.column = B.column sample 10;

where Table_1 is a big fact table, and Table_2 is a dim table

I encountered an error: No more Spool Space for my account But when i run a query similar to

SELECT * FROM Tabel_1 sample 10;

then it ran fine and i got the results.

Question: What exactly caused spool error in this case, can joining clause can create spool error.

santhosh
  • 1
  • Sampling from a single table can be optimized but with the JOIN generally the result is materialized in spool before sampling. It's good to COLLECT STATISTICS on the join column especially on the small table to get the most efficient join plan. Or in this case you could try sampling the fact table .... FROM (SELECT * FROM Table_1 SAMPLE 10) A JOIN Table2 B ... – Fred Nov 11 '22 at 18:43

0 Answers0