Order of execution

Database

Order of execution

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

2 REPLIES
Senior Apprentice

Re: Order of execution

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)

Teradata Employee

Re: Order of execution

jjj,

You posted this question in the "Data Modeling" forum.  I think your target audience for this question in the forumn named "Database".