Alexandre Benson Smith wrote:

>>
>>... If you change all outer joins to inner joins, do
>> you get the same results?
>
> I have realized that queries with outer joins,
> in general, runs faster and get better plans if I put the left joins at
> the end of the query, but didn't know why. Arno explained me that a left
> join forces the join order of the tables.

Right. Inner joins can be done in any order, but once you introduce an
outer join, the rules get tricky. The other thing you can do with
inner joins is distribute conditions. For example, in this statement

select a1, b1, c1
from a join b on a1 = b1
join c on a1 = c1

you know that a1 = c1, so you can start with any one of the three
tables, look up matching values in either of the other two, and complete
the match with a lookup in the third table a->b->c or c->a->b or b->c->a
or ...

With this statement the options are much more limited.

select a1, b1, c1
from a left join b on a1 = b1
join c on a1 = c1


This query must be evaluated starting with a or c. If both joins are
outer, then evaluation has to start with a.

And, of course, outer joins are sensitive to parentheses. This query

select a1, b1, c1
from a left join (b join c on a1 = c1)
on a1 = b1

is quite different, and has lots of opportunities for an optimizer to
make bad decisions.

> So, lets try the same query
> with inner joins instead of outer joins.
>
> Bingo Ann !
> Perfect plan, didn't need to put any +0 and the table order doesn't
> interfere more

To revert to your problem, can you express your query as a series of
inner joins with the optional tables expressed as subqueries? Something
like this..

select a1, select (b1 from b where b1 = a1), c1
from a join c on a1 = c1

That should give you decent optimization and (counting on my fingers)
the same semantics.

Regards,


Ann