Mombu the Programming Forum sponsored links

Go Back   Mombu the Programming Forum > Programming > Strange: Index not used by view but by direct access.
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 30th April 12:05
ralf-henning glomb
External User
 
Posts: 1
Default Strange: Index not used by view but by direct access.


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
  Reply With Quote


  sponsored links


2 30th April 12:05
ralf-henning glomb
External User
 
Posts: 1
Default Correction of the statements:


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
  Reply With Quote
3 30th April 12:05
serge rielau
External User
 
Posts: 1
Default Correction of the statements:


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
  Reply With Quote
Reply


Thread Tools
Display Modes




Copyright © 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666