This content has been marked as final. Show 5 replies
yash_08031983 wrote:Answer depends upon Oracle version.
I trying to under stand the explain plan , I read the link
Example 3: The inefficiency is coming from the wrong join order, where the bigger row source is taken as the driving table.
What matters is the order in which a join is executed, not the order in which you specify join conditions. When joining table A to table B, you can either start with table A and then for each row find a matching row in B, or do it in the reverse order. Depending on the size of tables, generally one way would be more efficient than the other.
Hmmm. Not really in all cases.
What matters is the order in which a join is executed, not the order in which you specify join conditions
As jonathan Lewis pointed it out in the following article
With multi-column joins, the order of the join predicates can make a big difference to the way Oracle operates a merge join
Jonathan's post header says "Join *surprise*", and the first tag is "bugs". Predicate order shouldn't make a difference in performance, and the fact that in some rare cases it does, doesn't change things fundamentally.
Please also note that the OP is clearly struggling to understand the basics of SQL tuning, so talking about some rare bugs (how often do you face multicolumn merge join in your everyday life, really?) will do nothing but confuse him.
Execution plan is all derived from what Oracle estimates, how many rows are in the tables, how selective the predicates are, etc, etc
There all sorts of inputs into those estimates but the most important thing is how accurate they are.
These estimates affect the order of the tables in the execution plan, the join mechanisms - nested loop, hash join, etc, etc, the access mechanisms - indexes used, etc.