Hi Guys,

Using FB 1.5.1 SS on Windows XP

I have a query (the * was in the query just to make it short I have a
column list there) and was intrigued by the choosen plan:

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) 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'

I get this plan:
PLAN JOIN (JOIN (JOIN (LP NATURAL,C INDEX (FK_CLIENTE_LISTAPRECO),ED
INDEX (PK_EMPRESA),E INDEX (AK_EMPRESA_NOME),S INDEX
(PK_SEGMENTOMERCADO),V INDEX (PK_VENDEDOR),ET INDEX (PK_EMPRESA)),EN
INDEX (FK_EMPRESA_ENDERECO)),ES INDEX (PK_ESTADO))

C.ListaPrecoID will be a very poor index, so I tried to avoid his use
with this query (using +0):
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),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))

I have expected the following plan:


PLAN JOIN (JOIN (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))

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)

This query returns just one record, and I have a unique index on E.Nome
that is a varchar(40)

the rdb$statistics for that index is 9,64041246334091E-5

if I force a "fake left join" (to force table Empresa to be the first
scanned table) I get a plan similar to what I expected:

select
*
from
Empresa e left 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'

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

Even if I just change the first 2 table order I get a bad plan again
select
*
from
Cliente c join
Empresa e on (e.EmpresaId =
c.EmpresaId) left 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'

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

The statistics are updated, I have dropped and recreated the index, have
backup/restore the db, but nothing changes the plan.

the above query is a view, I cant force the fake plan, because I could
search on other fields too (V.Nome, S.Descricao, ES.Sigla, etc.)

Why the optimizer don't priorize the unique index if I am searching for
a specific value on that column ???

:-/

Any Ideas ?

Any more info I could supply to help you understand what's happenning ?

Thank you !

--

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.2 - Release Date: 11/03/2005