Hi Ann,

Ann W. Harrison wrote:

> Alexandre Benson Smith wrote:
>
>> I understand the diference between inner and outer joins, but sometimes
>> I use a little trick to get better plans
>>
>
> Yes, you can force a table to be processed after other tables
> by making it the right side of a left join.

If I got it right you are saying that the process order of the table on
an outer join are just relevant to the two involved tables and has no
significance to the other ? (I thought it was that way, but as far as I
have experienced it's not the case).

An example:
select * from A join B on (B.A = A.A) join C on (C.B = B.B)
the join order could be
A B C
A C B
B A C
B C A
C A B
C B A

if I do
select * from A left join B on (B.A = A.A) join C on (C.B = B.B)
the join order could be
A B C
A C B
C A B
The outer join has just two tables A and B, A *must* be before B, but
nothing should prohibit C to be before A or B

right ?
Before I saw the behaviour I explained in my previous post (all tables
before outer join could be tested for join order, but can mix tables
before the outer join with tables after the outer join even if the
tables after the outer join are in fact inner joins with the tables
before the outer join) I expected the above to be true, but in practice
what I got is
the join order could be
A B C
A C B

Table C could not be used in the test for the best order because it's
after an outer join even if it's an inner join to table A.

if I do
select * from A join C on (C.B = B.B) left join B on (B.A = A.A)
the join order could be
A C B
A B C
C A B

That gives the optimizer all the option it could get.

> That's a better
> practice than using explicit plans, but even better would be
> to understand why the optimizer is choosing a bad join order
> without the outer join.
>
>> After I have "discovered" this I start to put the outer joins ate the
>> end of the query but didn't know why... :-(
>>
>>
> My guess is that you've got bad statistics on the index
> FK_PEDIDOVENDAITEM_PRODUTO which is causing the optimizer
> to under value it when processing the statement.
>
> Regards,
>
> Ann
>
>
>

No, the statistics are up-to-date, I just updated it before doing the
message I sent.

Do you wish any information about the involved indices ?

If you wish I could try to provide a simple test case.

Another reason that leads me to think that the statistics are the
culprit is that if I use all inner joins the plan is perfect, i.e. the
index FK_PEDIDOVENDAITEM_PRODUTO is used and the table PEDIDOVENDAITEM
is the first on the join order, look this:

select
*
from
PedidoVenda PV join
Usuario U on (U.UsuarioID = PV.AnalistaFinanceiroID) join
PedidoVendaItem PVI on (PVI.PedidoVendaID = PV.PedidoVendaID)
where
PVI.ProdutoID = 123

the plan is:
PLAN JOIN (PVI INDEX (FK_PEDIDOVENDAITEM_PRODUTO),PV INDEX
(PK_PEDIDOVENDA),U INDEX (PK_USUARIO))


Thanks for your comments.

see you !

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