Hi All,

I´m a litle confused with the PLAN. In my DB I had a procedure with
many unions all that was very slow. Then I found this SQL is the problem:

SELECT 'VEND' AS Tipo, V.Data,
P.Descricao as Descricao,
I.PrecoTotal AS Valor, CAST(V.Moeda AS VARCHAR(40)), V.CodCliente,
SN.codvendedor AS CodVendedor, I.CodProduto, I.Qtde
FROM vendas V
INNER JOIN ItensVendas I ON V.CodVenda = I.CodVenda
INNER JOIN Produtos P ON I.CodProduto = P.CodProduto
INNER JOIN saidanotas SN ON V.codsaidanota = SN.codsaidanota
WHERE (V.CodPeriodo = :Codper)

CodPer is aN input parameter of the procedure. This SQL take more than
5 sec to run.
This is the PLAN generated by the SQL:
Plan
PLAN JOIN (I NATURAL, P INDEX (RDB$PRIMARY27), V INDEX
(RDB$PRIMARY34), SN INDEX (RDB$PRIMARY29))

Adapted Plan
PLAN JOIN (I NATURAL, P INDEX (INTEG_87), V INDEX (INTEG_94), SN INDEX
(INTEG_89))

I don´t understand why the optimizer do a NATURAL in I table.

if I disable the Produtos table less than one sec to run...

SELECT 'VEND' AS Tipo, V.Data,
-- P.Descricao as Descricao,
I.PrecoTotal AS Valor, CAST(V.Moeda AS VARCHAR(40)), V.CodCliente,
SN.codvendedor AS CodVendedor, I.CodProduto, I.Qtde
FROM vendas V
INNER JOIN ItensVendas I ON V.CodVenda = I.CodVenda
-- INNER JOIN Produtos P ON I.CodProduto = P.CodProduto
INNER JOIN saidanotas SN ON V.codsaidanota = SN.codsaidanota
WHERE (V.CodPeriodo = :CodPer)

the plan:
PLAN JOIN (V INDEX (RDB$FOREIGN65), SN INDEX (RDB$PRIMARY29), I INDEX
(RDB$FOREIGN59))

Well, I do a merge in this plans and finaly correct the SQL to:

SELECT 'VEND' AS Tipo, V.Data,
P.Descricao as Descricao,
I.PrecoTotal AS Valor, CAST(V.Moeda AS VARCHAR(40)), V.CodCliente,
SN.codvendedor AS CodVendedor, I.CodProduto, I.Qtde
FROM vendas V
INNER JOIN ItensVendas I ON V.CodVenda = I.CodVenda
INNER JOIN Produtos P ON I.CodProduto = P.CodProduto
INNER JOIN saidanotas SN ON V.codsaidanota = SN.codsaidanota
WHERE (V.CodPeriodo = :CodPer)
PLAN JOIN (V INDEX (RDB$FOREIGN65), SN INDEX (RDB$PRIMARY29), I
INDEX (RDB$FOREIGN59), P INDEX (RDB$PRIMARY27))

that take less than an sec to run...

Can anyone say why I needed to do this in this case? It is not a very
complex Select it is?
why Optimizer don´t understand that table "I" can take INDEX
RDB$FOREIGN59? And why when I disable table P it run fast?
My server is FB 2.0.4 Win32.

TIA