Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

First I would like to cite the existing post Table order in Join Query

so the author is saying that the table order really doesn't make things(result, access plan)different. I have doubt on this regarding the access plan.

so the documentation of DB2 (TABLE JOIN) tells in the NLJoin, outer table is fixed and scanned only once, while the inner tables might be scanned multiple times depend on different scenarios, under this circumstance, I think the order of tables in the join really matters, we need to put big table as outer table, as we only want to scan this once.

is this the case, is this only for DB2? did I miss something?

+is there general guidance when writing SQL, how should we deal with the table join orders ?

share|improve this question
add comment

2 Answers

Well, the question you first highlight is really for SQLServer, so DB2 may use a different algorithm for determining join order, type of join, and optimization plans.

The documentation you found on DB2 is correct. I would also check out this link on strategies for selecting optimal joins. Even though this was for an older version of DB2, I have found this article incredibly helpful with understanding how/when DB2 picks which join to use and what influences its choice.

share|improve this answer
add comment

You are right that for Nested Loop Join, the choice of which table is the inner and which the outer table matters for perforamnce.

However, there is nothing in the documentation, in the link you provided, that implies that for a query that has a INNER JOIN b, the table a will be used as inner and b as outer table when the Nested Loop Join algorithm is selected.

Any decent optimizer evaluates many different combinations of algorithms, placing of tables and order of execution, so I don't think there is any difference if you write a INNER JOIN b or b INNER JOIN a, the chosen execution plans should be the same in both cases. If there are exceptions to this, I would expect them to be for very complex queries with tens of joined tables and/or multiple groupings.

Testing and checking the actual execution plans is one way to confirm this. Another would be to analyze the source code of the query optimizer.


The general guidance when writing SQL (in whatever DBMS), is not to care at all about the table join orders. SQL code describes what you want as result, it doesn't tell the DBMS how to get it. And many optimizers now are really smarter and faster than most of us in choosing the best execution plan most of the time.

Unless documentation shows that the optimizer is naive or in a very early version and the way the queries are written, really affects the chosen execution plan.

Or testing/running of a specific query shows that it's slow and some obviously good plan was not chosen. Then you can experiment with hints (if the DBMS has such feature), try rewriting the query in different ways, check if statistics are updated, etc.

share|improve this answer
    
so there is no general guideline as how the table should be placed ? –  zinking Jun 27 '12 at 6:21
1  
In DB2 running stats frequently is important as statistics gathered by the database engine may influence how the optimizer picks its access path. –  Chris Aldrich Jun 27 '12 at 12:51
    
@Chris: thnx for the point. I see you have used DB2 a lot, so you surely have more insight on this. –  ypercube Jun 27 '12 at 13:07
    
Which order the optimizer chooses to process them also may depend on what indexes exist over the tables in question. –  WarrenT Sep 5 '12 at 5:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.