I have questions on how does Teradata execute queries?
I use viewpoint and explain for seeing how many queries are executed, however, it doesn't answer all my questions regarding the ordering.
1. In the explain output it's not clear when the conditions in the where clause is being applied. Is it before any joins or after?
2. If I wanted the smaller tables to spool first should I always use subqueries & volatile tables to achieve this?
3. Are there commands I can use to explicitly state the best order of execution?
Any clarify on this would be very much appreciated
Q1: Conditions are applied by the optimizer as soon as possible, usually before the join
Q2: No, the optimizer tries to start with small spools anyway
Q3: No, there's no hint in Teradata, the optimizer is usually smart enough (as long as the neccessary statistics exist)
You posted this question in the "Data Modeling" forum. I think your target audience for this question in the forumn named "Database".