Hi,

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

<snip>

>
> and second table, "B_ODBFAK", with exactly the same definition.
>
> then, I have this union - view:

<snip>

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

The problem is that the Firebird optimizer cannot distribute the comparisons
to the inner selects of the unions. The same for aggregate queries in a
VIEW.

> 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.

I think so, but can't say without the SQL, metadata and retrieved PLAN.

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://newsgroups.firebirdsql.info