Spool space issue


Spool space issue

Teradata queries are executed top down or bottom up?

We're getting spool space error in some of the quereis. Any pointers to tune the queries will be helpful.

Have checked indices, collect stats, spool allocated for the id. all are fine.


Re: Spool space issue


Check the number of sessions connected or queries executed by the user.



Re: Spool space issue

I am not able to understand your question properly. Do you mean to ask the order , in which a query has so many joins?



Re: Spool space issue


If you are talking about the sequence of joins execution, Teradata optmizer decides how to and in which sequence process the joins. 

In order to avoid spool space problem you can check and move any drived tables to Volatile tables. 

Also check that the appropriate spool is assigned to the user. Collect only those stats which are required.


Re: Spool space issue

which part is executed first? conditions in "where" clause or joins in "from" clause, when the queries are written in ANSI92 stnds.


Re: Spool space issue

According to Standard SQL FROM is processed before WHERE in theory, but all optimizers try to push the WHERE-conditions into the join, thus they're are practically done before or during the join (this might be different for some conditions in Outer joins).

Spool is calculated on a user level, not on session level.

Each queriy might fit into the user's spool, but could fail if the same user runs multiple queries in different sessions concurrently.

Re: Spool space issue

these queries are triggered from business objects reports, which uses a generic id, that has a high spool allocated. there aren't many users running these reports simultaneously.

The query has around 15 tables joined and around 10 conditions applied.


Re: Spool space issue

You should check the spool usage of an individual query based on QueryLog. 

Those functional users like BO normally get an insane amount of spool to be able to run multiple reports in parallel. What spool size is assigned to it and what's your system space?

If there are too many queries in parallel you might implement a throttle using TDWM.