Rick Debay wrote:

> I would expect the first query to be the best, as within the parenthesis
> I explicitly state that the three tables can use an inner join.
> Instead, the looser second query where all items use a left join
> performs much better, and the plan reflects this by only having one
> NATURAL scan. The first query has a NATURAL scan for each of the two
> table groupings.
> Does the optimizer know how to handle parenthesized groupings, or is it
> not a special case?

It's a known issue with the mixed inner/left join handling. If you have
two inner join sets separated by a left join, you shouldn't expect the
query (actually, its right part) to be optimized really well. Once
you've explicitly made the problematic streams linked via left joins,
the problem disappears.


Dmitry