Spool space error
Okay, so this is an old one, but I still find it useful.
If you can’t run a report because of a spool space error, and you can’t convince the DBA to give you a couple of gigabytes more, then you might want to go to the Report Editor, Data ->VLDB Properties -> Tables -> Intermediate Table Type and select True temporary table.
What this does is modifying the SQL so that instead of having a huge chunk of code (that would use up a lot of memory) it breaks the code into CREATE – INSERT bits that are far easier to handle. It also makes the SQL more readable by humans (this includes programmers).
By the way, this setting also comes to rescue when you get the “Database is terrified by the script and it can’t even begin to analyze it” error. I don’t have the specific error message at hand, but I know for sure that Teradata does this when the SQL code is all in one pass and is so large that it can be seen from the Moon.
Can you show us an example of the difference in generated SQL?
Hi Jaap,
To put it very simple, here is a “before”:
SELECT a11.x, a12.y
FROM (SELECT table1.x, table1.z FROM table1) a11,
(SELECT table2.y, table2.z FROM table2) a12
WHERE a11.z = a12.z
And here is the “after”:
CREATE pa11 (x NUMERIC, z NUMERIC)
INSERT INTO pa11
SELECT table1.x, table1.z FROM table1
CREATE pa12 (y NUMERIC, z NUMERIC)
INSERT INTO pa11
SELECT table2.y, table2.z FROM table2
SELECT pa11.x, pa12.y
FROM pa11, pa12
WHERE pa11.z = pa12.z
DROP TABLE pa11
DROP TABLE pa12
It may look as if the second version is nastier, but it actually puts far less pressure on the Database. The first version is like trying to do math in your head, while the second version is like doing math in your head while using a paper to store the intermediate results.