Basic premise - I've been working on some larger and larger complex queries.. join table after table. Now even though each join is fairly efficient, these are some monster size tables and I'm running out of spool space.
Select (...) from table1 JOIN table2 on (...) JOIN table3 on etc
I used to believe the sever did something like a linear programming language: Select items from table 1.. join on table 2, combine in stack space. Join on table 3 combine and save space.. etc.
But! A a closer look reveals that our server does " 2) Next, we execute the following steps in parallel. " with many areas of Spool created Spool 5, Spool 26, etc.
So hey that's great for speed right? But the sacrifice is all these spool spaces in use at the same time. How do I ovverride? My current workaround is a static table, save my results halfway and keep going. It's a pain and a behavior I wish I could hardcode. (Using Teradata SQL assistant)
Hi Tim, the optimizer doesn't check if you spool space is large enough (although this would be a nice feature), it just tries to get the fastest plan. So the best/easiest solution is to ask your DBA for more spool, of course depending on the DBA this might not be easy at all ;-)
You might try to move some stuff into Derived Tables, if there's something like DISTINCT, GROUP, OLAP in it, the optimizer is forced to materialize it, but this might be in parallel, too or might change in another release.