KB37782: How would the join order in FROM clause of the report SQL affect SQL execution performance?

This document explains how the join order in FROM clause of report SQL affects SQL execution performance. For example, will "A join B on A.c1=B.c2" give the same performance as "B join A on A.c1=B.c2"?
 
To fully understand this, understanding of query plan (explain plan) is required. After database server receives a SQL statement request, it will parse the SQL statement, and generate a query plan about how to execute the SQL and get the results back. The query plan is generated by query optimizer in the database server.
 
A query plan usually contains the following:
 
 
 

• Access method: This determines how to access each table that involved in the SQL request. On the database side, there are several ways to access tables: Table scan, Index scan, Index seek, Bookmark lookup, etc.
• Join method: This determines how to facilitate a join between two tables. Although the SQL generated by Strategy SQL Engine will only display as "A join B", there are three major methods on the database side to do the actual join: Nested Loop, Hash join, and Merge join.
• Join order: Query optimizer will decide the join order basing on join method and database statistics(table size, index, cardinality, and etc).

For Nested Loop join, "A join B" will out-perform "B join A", if A is much smaller than B and B is indexed. But it's up to the database side (query optimizer) to decide how to do the acutal join, and a good query optimizer with accurate statistics will try to find an optimal query plan. In other words, even if the SQL is submitted as "B join A", a good query optimizer will generate a query plan that will do "A nested loop join B", which will yield the same result as the SQL as "A join B".
 
Therefore, the best way to tune the SQL execution performance is to analyze the query plan on the database side, and check the database settings/parameters (such as the ones related to table indexes, statistics generation, etc).