Hi Ann !

Ann W. Harrison wrote:

>Alexandre Benson Smith wrote:
>
>
>>>Alexander, out of curiosity, what happens if you put the e.name
>>>restriction into the "on" clause of the first join?
>>>
>>> JOIN Cliente c
>>> ON (e.EmpresaId = c.EmpresaId
>>> and e.Nome = 'Alexandre Benson Smith')
>>>
>>>
>>>
>>The good plan !
>>
>>
>>
>
>Sorry it doesn't solve your problem, but it may be a clue as to why the
>optimizer is confused. If you change all outer joins to inner joins, do
>you get the same results? My hypothesis is that the query compiler is
>being a bit too conservative about distribution equalities across all
>join terms in input stream in the presence of am outer join.
>
>

I am glad that it could be a hint, in fact, my main reason to post this
is the hope that someone could figure out why the optimizer didn't
priorized the use of the unique index and the joins off all tables using
the PK of each of them. 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. 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

select
*
from
Cliente
c join
Empresa e on (e.EmpresaId =
c.EmpresaId) join
SegmentoMercado s on (s.SegmentoMercadoId =
c.SegmentoMercadoId) join
Vendedor v on (v.VendedorId =
c.VendedorId) join
Empresa et on (et.EmpresaId =
c.TransportadoraId) join
Empresa ed on (ed.EmpresaId =
c.DistribuidorId) join
ListaPreco lp on (lp.ListaPrecoId =
c.ListaPrecoId) join
Endereco en on ((en.EmpresaID = c.EmpresaID)
and (en.Tipo = 1)) join
Estado es on (es.EstadoID = en.EstadoID)
where
E.Nome = 'Alexandre Benson Smith'

PLAN JOIN (E INDEX (AK_EMPRESA_NOME),C INDEX (PK_CLIENTE),LP INDEX
(PK_LISTAPRECO),S INDEX (PK_SEGMENTOMERCADO),V INDEX (PK_VENDEDOR),ET
INDEX (PK_EMPRESA),ED INDEX (PK_EMPRESA),EN INDEX
(FK_EMPRESA_ENDERECO),ES INDEX (PK_ESTADO))

a sligthly modified query:

select
*
from
Cliente
c join
Empresa e on (e.EmpresaId =
c.EmpresaId) join
SegmentoMercado s on (s.SegmentoMercadoId =
c.SegmentoMercadoId) join
Vendedor v on (v.VendedorId =
c.VendedorId) join
Empresa et on (et.EmpresaId =
c.TransportadoraId) join
Empresa ed on (ed.EmpresaId =
c.DistribuidorId) join
ListaPreco lp on (lp.ListaPrecoId =
c.ListaPrecoId) join
Endereco en on ((en.EmpresaID = c.EmpresaID)
and (en.Tipo = 1)) join
Estado es on (es.EstadoID = en.EstadoID)
where
V.Nome = 'Eliane'

PLAN JOIN (LP NATURAL,V INDEX (AK_VENDEDOR_NOME),C INDEX
(FK_VENDEDOR_CLIENTE),ED INDEX (PK_EMPRESA),E INDEX (PK_EMPRESA),EN
INDEX (FK_EMPRESA_ENDERECO),ES INDEX (PK_ESTADO),S INDEX
(PK_SEGMENTOMERCADO),ET INDEX (PK_EMPRESA))


select
*
from
Cliente
c join
Empresa e on (e.EmpresaId =
c.EmpresaId) join
SegmentoMercado s on (s.SegmentoMercadoId =
c.SegmentoMercadoId) join
Vendedor v on (v.VendedorId =
c.VendedorId) join
Empresa et on (et.EmpresaId =
c.TransportadoraId) join
Empresa ed on (ed.EmpresaId =
c.DistribuidorId) join
ListaPreco lp on (lp.ListaPrecoId =
c.ListaPrecoId) left join
Endereco en on ((en.EmpresaID = c.EmpresaID)
and (en.Tipo = 1)) left join
Estado es on (es.EstadoID = en.EstadoID)
where
V.Nome = 'Eliane'

PLAN JOIN (JOIN (JOIN (LP NATURAL,C INDEX (FK_CLIENTE_LISTAPRECO),ED
INDEX (PK_EMPRESA),E INDEX (PK_EMPRESA),S INDEX (PK_SEGMENTOMERCADO),V
INDEX (AK_VENDEDOR_NOME),ET INDEX (PK_EMPRESA)),EN INDEX
(FK_EMPRESA_ENDERECO)),ES INDEX (PK_ESTADO))

bad index choosen (FK_CLIENTE_LISTAPRECO)
Let's try using +0 to avoid that index

select
*
from
Cliente
c join
Empresa e on (e.EmpresaId =
c.EmpresaId) join
SegmentoMercado s on (s.SegmentoMercadoId =
c.SegmentoMercadoId) join
Vendedor v on (v.VendedorId =
c.VendedorId) join
Empresa et on (et.EmpresaId =
c.TransportadoraId) join
Empresa ed on (ed.EmpresaId =
c.DistribuidorId) join
ListaPreco lp on (lp.ListaPrecoId =
c.ListaPrecoId + 0) left join
Endereco en on ((en.EmpresaID = c.EmpresaID)
and (en.Tipo = 1)) left join
Estado es on (es.EstadoID = en.EstadoID)
where
V.Nome = 'Eliane'

PLAN JOIN (JOIN (JOIN (C NATURAL,LP INDEX (PK_LISTAPRECO),E INDEX
(PK_EMPRESA),S INDEX (PK_SEGMENTOMERCADO),V INDEX (AK_VENDEDOR_NOME),ET
INDEX (PK_EMPRESA),ED INDEX (PK_EMPRESA)),EN INDEX
(FK_EMPRESA_ENDERECO)),ES INDEX (PK_ESTADO))

bad, bad, bad

The 3 above queries are very similar, the last two uses left joins, the
first one inner joins, the first plan is perfect, the last 2 bad ones.

>Regards,
>
>
>Ann
>
>

Hope this finding can help.

thank you very much !

--

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