|
|
Posted by Gert-Jan Strik on 07/02/07 19:49
"news.onet.pl" wrote:
>
> > For starters, an outer join (such as left join) will only return the
> > same result as an inner join if no rows from the outer table would be
> > eliminated when running the inner join.
>
> Yes, but when left and inner join returns same results - left is faster
> (probably because it doesn't check the dependencies - just join results)
> We use left join instead inner in situations we know that inner join will
> not cut results....
I understand your case, but still I disagree. I still claim that it is
not faster, not as a rule. As a rule, it is equally fast or slower.
However, I am aware that this is mostly an academic discussion. In most
situations I would expect the same performance.
If you specify Left Join instead of Inner Join, you are basically doing
two things:
1. you are reducing the number of potential access paths during
compilation
2. you are 'forcing' the access path between the two tables: from the
outer table to the inner table
The result of [1] is positive, because a full compile would require less
time and resources. Of course this is only relevant when the optimizer
actually performs a full compile.
The result of [2] is negative, because it disqualifies query plans that
might be more efficient than the 'forced' left to right access path.
Obviously, this is only relevant if there actually is a more efficient
query plan.
The smarter the optimizer gets, the smaller the performance advantage of
Inner Join will be ([2]), and the smaller the potentially added
compilation cost will be ([1]).
The bottom line is, that you could see suboptimal performance in such a
Left Join scenario when you know that no rows from the outer table will
be eliminated but the optimizer does not.
I think this Left Join trick is a very good query hint if the query
underperforms because of a bad query plan. But using query hints without
a reason (simply out of routine) still sounds inappropriate to me.
Of course, if you have an example where a Left Join performs better than
the Inner Join equivalent, then I would be most interested to see it! If
you could post or describe such an example, that would be great.
Thanks,
Gert-Jan
Navigation:
[Reply to this message]
|