Hi Alexandre!

> select * from Empresa e
> join Cliente c 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 e.Nome = 'Alexandre Benson Smith'
>
> the plan is:
>
> PLAN JOIN (JOIN (JOIN (C NATURAL,LP INDEX (PK_LISTAPRECO),E INDEX
> (AK_EMPRESA_NOME)...
>
> I had expected the following plan:
>
> PLAN JOIN (JOIN (JOIN (E INDEX (AK_EMPRESA_NOME),
> C INDEX(PK_CLIENTE), LP INDEX (PK_LISTAPRECO)...
>
> If I force this plan I get far better result and of course just one
> read on the majority of the tables and 3 reads on table Empresa
> (that is used 3 times in this query)
>
> Why the optimizer don't priorize the unique index if I am searching
> for a specific value on that column ???

I don't know, maybe Arno can tell you.

> Any Ideas ?

Oh, sure, I always have plenty of ideas. Like you, I don't understand
why the optimizer chose the plan it did - other than knowing InterBase
often made a worse choice than the very best when there was a lot of
tables to be joined and also wasn't as good with outer joins as with
inner joins. The crazy changes I would recommend you to try, are
either

left join Endereco en on (en.EmpresaID = c.EmpresaID+0) ...

(it shouldn't help any, but Firebird seems confused and maybe it makes
it less tempting to use the index it currently uses)

or to experiment with parenthesis, e.g.

select * from (Empresa e
join Cliente c on e.EmpresaId = c.EmpresaId)
join SegmentoMercado s on s.SegmentoMercadoId = c.SegmentoMercadoId
...

or

select * from Empresa e
join Cliente (c on e.EmpresaId = c.EmpresaId
join ListaPreco lp on lp.ListaPrecoId = c.ListaPrecoId + 0)
join SegmentoMercado s on s.SegmentoMercadoId = c.SegmentoMercadoId
...

As you can see, I'm pretty confused as to where to put the parenthesis
(I have never had to do this). Hence, it would be interesting to see
the result if you find parenthesis any help.

HTH,
Set