Hi Ann !
Ann W. Harrison wrote:

>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 ...
>
>
>

Yep, I understand this

>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.
>
>
>

As far as I understand what Arno tolds me, this is not quite true, it
should be, but actually it's not the case.

The left join force the join order to be a -> b -> c, this could be done
a -> c -> b or c -> a-> b, but actually the optimizer don't try order
with other inner joins if it's after a left join, the left join stop the
choices at the tables that preceeds it. This is why I have found that if
I put the left join at last I got better plans (the optimizer has more
options to try).

I think that if the above query was written this way the optimizer can
work with more join orders.

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

>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.
>
>
>
>

...snip...

>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.
>

I have done it with some of my queries (when I have one or two fields on
the outer table), but in this case I select a bunch of columns (6
columns ) from the outer tables then I think that it could lead to a lot
of more reads (6 times more on one table), I will try this later and
report the results.
my query would look like this:

select
a1, b2, c1, (select d2 from d where d1 = a1), (select d3 from d
where d1 = a1),
(select d4 from d where d1 = a1), (select d5 from d where d1 = a1),
(select d6 from d where d1 = a1), (select e3 from e where e2 =
(select d2 from d where a1 = d1))
from
a join b on a1 = b1
join c on a1 = c1

>Regards,
>
>
>Ann
>
>

thanks again !

--

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.3 - Release Date: 15/03/2005