Hallo, I am asking kindly for help with this problem:

I have table:

CREATE TABLE "A_ODBFAK"
(
"CISLOOF" INTEGER NOT NULL,
"CISLOODB" VARCHAR(6) CHARACTER SET WIN1250 NOT NULL COLLATE
PXW_CSY,
"DATUMZP" TIMESTAMP,
"CENANET" DOUBLE PRECISION,
CONSTRAINT "B_ODBFAK_PK" PRIMARY KEY ("CISLOOF")
);

/* Index definitions for A_ODBFAK */

CREATE INDEX "A_ODBFAK_CISLOODB" ON "A_ODBFAK"("CISLOODB");
CREATE INDEX "A_ODBFAK_DATUMZP" ON "A_ODBFAK"("DATUMZP");
CREATE INDEX "A_ODBFAK_PRIMARYKEY" ON "A_ODBFAK"("CISLOOF");



and second table, "B_ODBFAK", with exactly the same definition.



then, I have this union - view:



CREATE VIEW "ODBFAKU" (
"CISLOOF", "DATUMZP", "CISLOODB", "CENANETA", "CENANETB") AS

SELECT CisloOF, DatumZP, CisloOdb, CenaNet as CenaNetA, CAST(0 as
DOUBLE PRECISION) as CenaNetB FROM A_OdbFak
UNION ALL SELECT CisloOF, DatumZP, CisloOdb, CAST(0 as DOUBLE
PRECISION) as CenaNetA, CenaNet as CenaNetB FROM B_OdbFak;




now the problem:

1. select * from a_odbfak where datumzp = '10/13/1997'
is using index:
PLAN (A_ODBFAK INDEX (A_ODBFAK_DATUMZP))

2. select * from odbfaku where datumzp = '10/13/1997'
is NOT using any indexes:
PLAN (ODBFAKU A_ODBFAK NATURAL)
PLAN (ODBFAKU B_ODBFAK NATURAL)


why ? Is there a way to make the union to use the indexes from underlying tables?
(I feel this to be an optimization bug, but maybe I am wrong).

Second situation: I JOINed this view with another table on field CISLOOF, the JOIN
is again running "NATURAL" (not using indexes for field CISLOOF). But maybe this
is the same problem.

(note: tables *_ODBFAK have many other columns, but I hope they are not
neccesary to be listed here)
(using FB 1.5.0. SS on Linux Slackware 10.0. If you need any more info, please ask
me)

Thank you.


Radovan Bukoci / Auto-part's