Order of Tables in SQL statement

Database
amp
Fan

Order of Tables in SQL statement

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?

Thanks in advance.
7 REPLIES
Enthusiast

Re: Order of Tables in SQL statement

I hope you meant inner joins, and the answer is no, teradata don't bother about the order in which you mention the tables in the from clause. The optimizer is smart enough to generate the same plans.

You can always verify it by doing an explain on both the queries and you would end up with the same plan.

An intelligent optimizer is supposed to generate the same, most efficient plan possible for two semantically identical queries.
Enthusiast

Re: Order of Tables in SQL statement

Hi Joed,

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?

Regards,
Sakthi
Enthusiast

Re: Order of Tables in SQL statement

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

Re: Order of Tables in SQL statement

That is true. But the one I mentioned was in Application Development document in Merge Join. The exact text goes like

"In general, better performance results if the left
table, in the explain, is the unique (smaller) table."

Can you post your opinion on this.

Regards,
Sakthi
Enthusiast

Re: Order of Tables in SQL statement

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.

thanks for tickling my neurons ...
Enthusiast

Re: Order of Tables in SQL statement

Hi Joe,

I didnt expect that you would have done such a big work on what I had asked you for.

Thanks for the analysis that you have done.

My learning is though the explain shows the small table as left or right it internally (ie the optimizer)uses it as left table only.

Regards,
Sakthi
amp
Fan

Re: Order of Tables in SQL statement

Thanks for your replies.
That helps!!!