In the documentation I read these (/B19306_01/server.102/b14211/optimops.htm#i82080).
The optimizer uses nested loop joins when joining small number of rows, with a good driving condition between the two tables. You drive from the outer loop to the inner loop, so the order of tables in the execution plan is important.
The outer loop is the driving row source. It produces a set of rows for driving the join condition. The row source can be a table accessed using an index scan or a full table scan. Also, the rows can be produced from any other operation. For example, the output from a nested loop join can be used as a row source for another nested loop join.>
I need some help to understand the bold line, i.e.
so the order of tables in the execution plan is important.
There are various conflicting opinion about the driving table (some says smaller and some says larger table is good option) and unfortunately I did not understand any of those logic.
I read these threads/blogs also.
2195178
http://hoopercharles.wordpress.com/2011/03/21/nested-loops-join-the-smaller-table-is-the-driving-table-the-larger-table-is-the-driving-table/
In practice, I have seen
explain plan for select e.ename,d.dname
2 from emp e, dept d
3 where e.deptno=d.deptno;
usages emp as driving table (I only understood part of Aman's logic that dept table access would be faster when there would be an index available over it and hence it is in the inner loop)
Whereas,
SQL> explain plan for
2 select e.ename,d.dname
3 from emp e, dept d
4 where e.deptno=d.deptno
5 and e.deptno=20;
usages dept as driving table.
I have use USE_NL_WITH_INDEX with LEADING hint to change it, but it is giving some adverse effect (sometimes with optimizer_mode ALL_ROWS it is ignoring the hint completely).
so the order of tables in the execution plan is important. How can I determine it ? I have read Tom's effective oracle by design but there is also no solution.