FROM clause - table order

[ca9mbu]

I understand that the order in which the tables are placed in the FROM clause of a SELECT statement can have a significant effect on performance. However, what I don't understand is how you decide on which is the most efficient order in which to place the tables. I have a query that will join 5 tables together, one of which has ~1.5 million records. I looked at the following article: http://www.oracle.com/oramag/webcolu...rf_082100.html, but it wasn't entirely clear how he got his "NSR" figure. If anyone could point me to any other online resources, or describe the procedure step by step I'd be very grateful.

Thanks,

Matt

[tamilselvan]

Select ....
from TabA, TabB, TabC
where TabA.col1 = TabB.col2 and
TabB.col3 = TabC.Col4;

Most of the time CBO deteremines the execution path correctly if the tables and indexes are analyzed regularly. However, at some time the developer knows the distribution of data in the tables better than CBO. So, he can modify the execution path by adding HINTS.

Example:
Select /*+ ORDERED */ .........
from TabA, TabB, TabC
where TabA.col1 = TabB.col2 and
TabB.col3 = TabC.Col4;
Here the driving table is TabA, followed by TabB and then TabC. In general, the table which has fewer rows should be written first (TabA) , and the big table last (TabC).
In the above example, I assumed TabA has 10,00 rows, TabB has 100,000 rows and the TabC has 1,000,000 rows.

[ca9mbu]

Writing the smallest table first and the biggest table last would seem the obvious way to do this, apart from one thing. The article I referred to in my original post says that the tables are processed in reverse order - hence in this case the largest table would be processed first. This means that all 1 million rows would be read immediately! Is this really the correct way (or am I just getting confused with optomising the WHERE clause now?)

Thanks again,

Matt

[jmodic]

First of all, the article you are referring to neglect to mention one very important thing: The order of tables in the FROM clause might only influence the execution plan if the *rule based optimizer* (RBO) is used. Othervise (if the cost based optimizer (CBO) is used) the order of tables is totaly irrelevant. The only exception to this rule is if you use a hint /* + ORDERED*/ where usage of CBO is forced, however the order in which tables are processed in joins is explicitely suggested to the optimizer.

If you use RBO, then indeed the order of tables in FROM is relevant. The parcer processes the SQL statement "in the reverse order", meaning that tables in the FROM caluse will be processed from bottom up, right to left. So the table that is listed at the end of the list will be the driving table of the join. So in Tamilselvan's first example (the one without the hint) TabC will be processed first. However in the second example with the hint optimizer will very probably obey the suggestion and process the tables in the order as listed in the FROM clause, meaning that TabA will be processed first.

Once again: if you use CBO you generaly don't need to wory about the order of the tables in the FROM clause.