Hi Alexandre,

> >I'm afraid you hit one of the limitations of the current optimizer (FB1.5).
> >For some reason (bad estimated values) it has calculated wrong join order, but i
> >can't explain why because when E is the first table all other relations are
> >joined by unique indexes which do get a higher priority in the JOIN order
> >calculation. Are there very small (number of records) tables and large tables in
> >this query?
> >
> >
> Very small yes, large no, this database are pretty small.
>
> the record count for each table are:
>
> TableName COUNT
> =================================================== =====
> Empresa 10373
> Cliente 10309
> SegmentoMercado 6
> Vendedor 25
> ListaPreco 1
> Endereco 10735
> Estado 28

You've 4 very small tables (SegmentoMercado, Vendedor, ListaPreco and Estado). I assume they use all
1 data-page, that meant the optimizer estimate the number of records to be page-size/record-size. In
your situation i guess (almost know for sure) that the estimated number is much higher as the real
number of records and this could be the problem. Another thing is that you've more than 5 inner
joins. The FB1.5 optimizer (and previous versions) is limited to not try all combinations possible,
because that would be to expensive in time compared with actual execute/fetch.

> >Forcing JOIN order can indeed be done by adding + 0 or using LEFT JOIN:

> :-)
> This gave me the exact plan I expected for the query
> PLAN JOIN (JOIN (JOIN (E INDEX (AK_EMPRESA_NOME),C INDEX (PK_CLIENTE),ED
> INDEX (PK_EMPRESA),LP INDEX (PK_LISTAPRECO),S INDEX
> (PK_SEGMENTOMERCADO),V INDEX (PK_VENDEDOR),ET INDEX (PK_EMPRESA)),EN
> INDEX (FK_EMPRESA_ENDERECO)),ES INDEX (PK_ESTADO))
>
> But I can't create my view this way, beacause I could filter on other
> columns and the join order could be reversed for table Empresa and

Then this isn't a solution for you.


Just out of curiousity what does this query (spliting so that we have 5 inner joins) ?

SELECT
*
FROM
Empresa e
JOIN Cliente c ON (c.EmpresaId = e.EmpresaId)
JOIN ListaPreco lp ON (lp.ListaPrecoId = c.ListaPrecoId)
JOIN SegmentoMercado s ON (s.SegmentoMercadoId = c.SegmentoMercadoId)
JOIN Vendedor v ON (v.VendedorId = c.VendedorId)
LEFT JOIN Endereco en ON ((en.EmpresaID = c.EmpresaID) and (en.Tipo = 1))
JOIN Empresa et ON (et.EmpresaId = c.TransportadoraId)
JOIN Empresa ed ON (ed.EmpresaId = c.DistribuidorId)
LEFT JOIN Estado es ON (es.EstadoID = en.EstadoID)
WHERE
e.Nome = 'Alexandre Benson Smith'

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.7.2 - Release Date: 11-3-2005