Jorge Andres Brugger wrote:

> Hi all!
>
> Two tables:
>
> CREATE TABLE FACTURAS (
> SUCURSAL D_CODIGO_SUCURSAL NOT NULL /*
> D_CODIGO_SUCURSAL = SMALLINT */,
> ID_FACTURA INTEGER NOT NULL,
> FECHA D_FECHA /* D_FECHA = DATE */,
> ANULADA INTEGER DEFAULT 0 NOT NULL
> );
>
> ALTER TABLE FACTURAS ADD CONSTRAINT PK_FACTURAS PRIMARY KEY (SUCURSAL,
> ID_FACTURA);
>
>
> CREATE TABLE ITEMS_FACTURAS (
> SUCURSAL D_CODIGO_SUCURSAL NOT NULL /*
> D_CODIGO_SUCURSAL = SMALLINT */,
> ID_FACTURA INTEGER NOT NULL,
> NUMERO_ITEM SMALLINT NOT NULL,
> CODIGO_PRODUCTO D_CODIGO_PRODUCTO NOT NULL /*
> D_CODIGO_PRODUCTO = INTEGER NOT NULL CHECK (value between 1 and 999999) */,
> CANTIDAD SMALLINT NOT NULL
> );
>
> ALTER TABLE ITEMS_FACTURAS ADD CONSTRAINT PK_ITEMS_FACTURAS PRIMARY KEY
> (SUCURSAL, ID_FACTURA, NUMERO_ITEM);
>
> ALTER TABLE ITEMS_FACTURAS ADD CONSTRAINT FK_ITEMS_FACTURAS FOREIGN KEY
> (SUCURSAL, ID_FACTURA) REFERENCES FACTURAS (SUCURSAL, ID_FACTURA) ON UPDATE
> CASCADE;
>
> Now I want to create a View:
>
> CREATE VIEW VW_ITEMS_FACTURAS_NO_ANULADAS(
> SUCURSAL,
> ID_FACTURA,
> FECHA,
> NUMERO_ITEM,
> CODIGO_PRODUCTO,
> CANTIDAD)
> AS
> SELECT FACTURAS.SUCURSAL, FACTURAS.ID_FACTURA, FACTURAS.FECHA,
> ITEMS_FACTURAS.NUMERO_ITEM, ITEMS_FACTURAS.CODIGO_PRODUCTO,
> ITEMS_FACTURAS.CANTIDAD
> FROM FACTURAS INNER JOIN ITEMS_FACTURAS ON (FACTURAS.SUCURSAL =
> ITEMS_FACTURAS.SUCURSAL) and (FACTURAS.ID_FACTURA =
> ITEMS_FACTURAS.ID_FACTURA)
> where facturas.anulada = 0;
>
> Plan for this view is:
>
> PLAN JOIN (FACTURAS NATURAL, ITEMS_FACTURAS INDEX (FK_ITEMS_FACTURAS))
>
> ¿Why facturas plan is natural is there is a PK with the components used in
> join?
>
> ¿Is there a way to "fix" this?
>
> Thanks!
>
>
>

Hi Jorge !

As far as I can see there is no fix ! That plan is the best that FB
could to to a select on that view without a filter criteria.

I suppose you got this plan using this query:

Select * from VW_ITEMS_FACTURAS_NO_ANULADAS

Since there is no WHERE clause to filter out the records all facturas
should be read.

You could add an index on Facturas.Anulada, theorically this would be a
*very bad* index since it would have just to values, but if you have a
small number of facturas with anuladas = 0 it could help. (I don't think
it would give any performance benefit, but you could try.

The Facturas table would use an indexed (if you have defined the proper
index of course) plan if you put something on the WHERE clause like this:

Select * from VW_ITEMS_FACTURAS_NO_ANULADAS where Fecha between
'2007-06-01' and '2007-01-30'

The plan should be something similar to this:

PLAN JOIN (FACTURAS INDEX(IDX_FECHA), ITEMS_FACTURAS INDEX (FK_ITEMS_FACTURAS))


see you !

--
Alexandre Benson Smith
Development
THOR Software e Comercial Ltda
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br