Ann W. Harrison wrote:

> Alexandre Benson Smith wrote:
>
>> On FB 1.5 and earlier an outer join forces the optimizer to use the
>> table as the first relation on the join order.... Really don't know how
>> it will go with FB 2.0 :-/
>>
>>
>
> Unless I've lost the thread of the discussion (which happens
> pretty often) the order of table access with outer joins is
> not an optimizer issue but a logic issue.
>
> Regards,
>
>
> Ann
>
>

Ann,

Who am I to talk about optimizer to you :-)

I understand the diference between inner and outer joins, but sometimes
I use a little trick to get better plans

I will say what I have experienced.

I could not remember the exactly query that gives-me problem, but I will
try to show an example

take this query into account:
select
*
from
PedidoVenda PV left 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 (JOIN (PV NATURAL,U INDEX (PK_USUARIO)),PVI INDEX
(FK_PEDIDOVENDAITEM_PEDIDOVENDA))
about 1 second to execute and fetch all 73 records
30.172 natural reads on Table PedidoVenda
3.481 indexed reads on Table Usuario
45.731 indexed reads on Table PedidoVendaItem

Now, look at this variation, the query has exactly the same logic, but I
put the outer join at the end.

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

the plan is:
PLAN JOIN (JOIN (PVI INDEX (FK_PEDIDOVENDAITEM_PRODUTO),PV INDEX
(PK_PEDIDOVENDA)),U INDEX (PK_USUARIO))
70 ms to execute and fetch all 73 records
73 indexed reads on Table PedidoVendaItem
73 indexed reads on Table PedidoVenda
27 indexed reads on Table Usuario

FK_PEDIDOVENDAITEM_PRODUTO in an index on PedidoVendaItem.ProdutoID

As you can see, the query has exactly the same logic but the plan for
the second is far better.

After I have "discovered" this I start to put the outer joins ate the
end of the query but didn't know why... :-(

The same trick is used to do joins between tables and procedures

when one do

select * from A join SP_B(A.A) on (1=1)

there is an error, but if one do

select * from A left join SP_B(A.A) on (1=1)

the left join forces Table A to be the first relation on the join order

Sometimes I use the same trick to force some table to be the first in
the join order using a left join, I learned it from Arno when he was
trying to help me improve some queries, then he explainned to me why I
tend to get better plans when I put the outer joins at the end.

As I have understood is something like this:
Until the optimizer don't find an outer join it could try to get better
join order with the tables, but if it find an outer join it just can't
try to mix tables before and after the outer join even if the tables
after the outer join still is an inner join to prior tables (as shown in
the example above).

In the first query PedidoVenda is the table that should be first scanned
because it's the only one prior to the outer join, since the where
clause does not use any indexed field on PedidoVenda the only option is
a Natural Scan, then it join to Usuario using the PK and after this join
to PedidoVendaItem Using the FK index between PedidoVenda and
PedidoVendaItem.

In the second query the optimizer could try the above plan but could
also try to first scan PedidoVendaItem using the index on
PedidoVendaItem.ProdutoID because the where clause contains an indexed
field and then join to PedidoVenda using it's PK and after that join to
Usuario using it's PK too. (that is a better plan than the former)

This is how I see it, maybe I am completely wrong.

Wait for your comments about this.

see you !

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