On Sun, Jun 3, 2012 at 7:17 PM, Alec Swan <alecswan@...> wrote:

>
> I would like to note that the fact that LEFT JOIN can generate an optimal
> plan where INNER JOIN fails indicates that all our index statistics are
> up-to-date

Actually, it doesn't. The order of joins is determined by the optimizer in
the case of an inner join, but outer joins determine the order
semantically. Consider this case:

select c.city, s.stateName
from cities c
inner join states s on c.stateCode = s.stateCode
where c.population > 1000000 and s.population < 5000000

The optimizer could either choose to look up cities larger than a million
then look up the matching states, or it could choose to find states with
population less than five million then look up cities. The choice would
depend on what indexes are available for city and state populations and
stateCodes and the selectivity of those indexes.

This case is different.

select c.city, s.stateName
from cities c
inner join states s on c.stateCode = s.stateCode
where c.population > 1000000

The only join order possible is to look up cities first and use the city to
find matching states, if any. Starting with states would miss all the
cities that are not in states.

Good luck,

Ann


[Non-text portions of this message have been removed]