ralf-henning glomb

Dear Community.


i have a problem with views on DB2 using no indexes on db2-tables.


I have two Tables and one View:
V001 is an base-view: "Select * from T001"
T001, T002 are tables.


Now I have two select-statements:

1)
Select V001.A, V001.B, T002.C, T003.D .....
from V001 , T002, T003
where V001.a = T002.a
and V001.c > 10000;


2)
Select T001.A, V001.B, T002.C, T003.D .....
from T001 , T002, T003
where T001.a = T002.a
and T001.c > 10000;

The only difference between the two statements is that the first one
uses the view "V001" to acces table "T001" whereas the second accesses
the table "T001" directly.

The results are the same, BUT the execution-time of the statements are
totally different.

The first Statement didn't use any index of T001 and made an
tabelspace-scan.
The second Statement used the index of T001 and is much more faster than
the first one.
The statements are passed to DB2 using dynamic-sql.


How could this be ?
Anny suggestions ?
Could this be an error of DB2 ?


Thanks in advance
Ralf-Henning Glomb


--
Ralf-Henning Glomb
BMW AG
80788 München

ralf-henning glomb

Hi Knut,

Knut Stolze schrieb:

you are right.

Yes i have.
The first one shows an table-scan.
The second one shows an index scan.


Ralf-Henning


--
Ralf-Henning Glomb
BMW AG, Abteilung FZ-400
80788 München
Tel.: +49 89 382 43226
mailto:ralf-henning.glomb@bmw.de

serge rielau

Can you provide the DDL for the views involved?

Cheers
Serge
--
Ralf-Henning Glomb
BMW AG, Abteilung FZ-400
80788 München
Tel.: +49 89 382 43226
mailto:ralf-henning.glomb@bmw.de