Hi Alexandre,

> I have a problem with the following view:
>
> CREATE VIEW VPEDIDOVENDAPARCELA (

<snip>

> ) AS
>
> select

<snip>

> from
> PedidoVenda p join
> Moeda m on (m.MoedaId = p.MoedaId) join
> PedidoVendaItem i on (i.PedidoVendaId = p.PedidoVendaId) join
> PedidoVendaParcela pa on (pa.PedidoVendaId = p.PedidoVendaId) join
> MultiEmpresa me on (me.MultiEmpresaID = p.MultiEmpresaID)
> group by

<snip>

>
> I use to do the following with it:
>
> Select * from vPedidoVendaParcela where PedidoVendaID = 10
>
> This query are slow, about 8 seconds, returned me 2 rows, the plan is
> PLAN SORT (JOIN (VPEDIDOVENDAPARCELA ME NATURAL,VPEDIDOVENDAPARCELA P
> INDEX (FK_PEDIDOVENDA_MEMPRESA),VPEDIDOVENDAPARCELA M INDEX
> (PK_MOEDA),VPEDIDOVENDAPARCELA I INDEX
> (FK_PEDIDOVENDAITEM_PEDIDOVENDA),VPEDIDOVENDAPARCELA PA INDEX
> (AK_PEDIDOVENDAPARCELA_PARCELA)))


> I have figured I have bad indexes due to FK's

<snip also aggregate query>

> if I do this:
>
> select

<snip>

> from
> PedidoVenda p join
> Moeda m on (m.MoedaId = p.MoedaId) join
> PedidoVendaItem i on (i.PedidoVendaId = p.PedidoVendaId) join
> PedidoVendaParcela pa on (pa.PedidoVendaId = p.PedidoVendaId) join
> MultiEmpresa me on (me.MultiEmpresaID = p.MultiEmpresaID)
> where
> P.PedidoVendaID = 10
> group by

<snip>

> what should be what I expected that my select on view was "converted "
> the plan is
> PLAN SORT (JOIN (P INDEX (PK_PEDIDOVENDA),ME INDEX (PK_MULTIEMPRESA),I
> INDEX (FK_PEDIDOVENDAITEM_PEDIDOVENDA),PA INDEX
> (AK_PEDIDOVENDAPARCELA_PARCELA),M INDEX (PK_MOEDA)))
> execution time = 6ms


> I tried this too:
>
> recreate VIEW VPEDIDOVENDAPARCELA (

<snip>

> ) AS select

<snip>

> from
> PedidoVenda p left join
> Moeda m on (m.MoedaId = p.MoedaId) join
> PedidoVendaItem i on (i.PedidoVendaId = p.PedidoVendaId) join
> PedidoVendaParcela pa on (pa.PedidoVendaId = p.PedidoVendaId) join
> MultiEmpresa me on (me.MultiEmpresaID = p.MultiEmpresaID)
> group by

<snip>

>
> I hoped the left join will force "PedidoVenda P" to be the first
> searched table, and that this will enable the use of the PK index on
> PedidoVenda.PedidoVendaID
>
> the plan is:
> PLAN SORT (JOIN (JOIN (VPEDIDOVENDAPARCELA P NATURAL,VPEDIDOVENDAPARCELA
> M INDEX (PK_MOEDA)),JOIN (VPEDIDOVENDAPARCELA I INDEX
> (FK_PEDIDOVENDAITEM_PEDIDOVENDA),VPEDIDOVENDAPARCELA ME INDEX
> (PK_MULTIEMPRESA),VPEDIDOVENDAPARCELA PA INDEX
> (AK_PEDIDOVENDAPARCELA_PARCELA))))
>
> execution time around 9secs.
>
> What I can think is that FB is unable to use an index to filter a view
> with aggregate query, I think the engine should perform the full table
> aggragate and then filter it.

Yes, you can read that Firebird will put the condition into the HAVING
clause and thus be performed after the aggregation. Why the HAVING clause?
Because you could use a aggregate-function from the VIEW and that cannot be
translated to the WHERE clause and must be in the HAVING clause. This is 1
of the optimalizations that need to be done in the optimizer.

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