The order of the tables in joins in Query

Tuning de Banco, Tuning de SQL, Ferramentas de tuning

Poston Mon, 09 May 2016 5:49 pm

Good Afternoon Folks.

I have a question about the order of tables using joins. The order influence performance? For example, if I write:
Code: Select all
select * from bigtable b
                             inner join smalltable s on ( =
... the above query will be more costly in relation to the below query:
Code: Select all
select * from smalltable b
                             inner join bigtable s on ( =

Poston Tue, 10 May 2016 1:04 pm

In the old days, even the Oracle 8, the optimizer was as a rule. (RBO-Rule Based Optimization)
That is, the order of the tables and the WHERE clause made difference in the execution plan.

But from the 9, the Oracle has adopted the cost optimizer (CBO-Cost Based optimization). In this case, he seeks a number of statistics for indexes, contents of the tables, histograms, etc. And with that, he can set up an execution plan, regardless of the order they are in SQL.

So: it makes no difference to order.
Location: Seattle, WA, USA

Thomas F. G

Poston Fri, 30 Jun 2017 4:01 pm

In addition, I inform you that in addition to the order of the tables in the join, something else that does not influence is the order of the columns in the WHERE clause. For more information I suggest you access the post ... where.html .
Location: São Paulo - SP

Fábio Prado

Return to DBA Tuning

Who is online

Users browsing this forum: No registered users and 1 guest