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.
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.
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.
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.
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.