Does the order of tables matter in teradata SQL statement?
Would the result come up faster if the first table in the from clause has a major filter in the where clause than the rest of tables? Or, whatever the way you put the tables in the from clause, it comes up in the exact same time?
I have read in a doc where it has mentioned if the left table has lesser no of records than the right one then the execution will be faster. Is it true? Or does the optimizer has to do something on this?
Well it doesn't work that way in Teradata because we have a cost based optimizer and not a rule based one.
I am not sure if it works as you mentioned in Oracle, but I remember during my old informix days, this was the case and made a lot off difference as to which table was mentioned where. Most of the those old world DBMS used to work that way... I am not sure if things go that way still around, but certainly not in TD.
Have had a lot to unlearn ever since :-)
As I mentioned, if you do the explain on the two queries you will see exactly the same plan.
I did some background research on what was the documentation trying to put across but drew blank mostly.
Below are my thoughts on this, I could be wrong, hopefully someone should be able to throw more light on it.
The issue is pertaining to how TD internally does the join of the tables.
I found a little different wording one of the more recent docs
"Teradata merge joins must use this same logic mainly due to hash synonyms, so it, normally, places the smaller table as the left-table."
The key here would be the word "it" which means TD is doing on it's own.
So I would assume it was meant that the optimizer makes the intelligent decision of which one is the smaller of the tables, and will put it on the left during the actual join process.
However the explains did not show the same table on the left for the two different queries.
So that's a concern, given it's coming from an intelligent optimizer. :o
A possible explanation could be just that Optimizer didn't bother to make a re-wording in the user presentable english statements dumped to the screen, but was still generating the same efficient plan and there by the same plastic steps for the AMPs.
I did a join to test if there could be performance differences in the order.
T1=unique,small 10 records T2=nonunique,large ~ 440,000 records
And got the same response time irrespective how how I ordered the tables.
So I'll stick to the story that TD takes care of the table ordering and wait for some enlightenment to come by, there are definitely some missing puzzle pieces.