Primary index not used

[rdevarajan]

SELECT ORRFNBR, ORMENBR, ORSHNBR, ORMORDER
FROM
ORDERS, ordext, SLOTRESERVATION, SLOTSERVICE
WHERE
srornbr = orrfnbr AND
orrfnbr = oeornbr AND
srstat IS NULL AND
srservice = ssrfnbr AND
srbrnbr = ? and
oereviewed = 0 and
orshnbr = srshnbr

ORDER BY SRDATE, SRSTARTTIME, SRENDTIME, SERVICE_SORT, SRSERVICE

In the above query, the primary index for ORDERS table is 'orrfnbr' . But this query goes on a table spacescan . There is an index on 'srornbr ' which is being used properly.
please help as to why the index is not being used.

 

[MrJRW]

Good Morning

Two (2) immediate thoughts:

1) Try defining aliases for your tables and add the aliases to your conditionals (ie, a.srornbr = b.orrfnbr, etc)

2) How up-to-date are your statistics ? Does the query optimizer "know" the index exists ?

JRW

 

[rdevarajan]

Thanks.

Even when i use aliases, it does not work. Yes i did run runstats to update everything beofre trying the query

Also if i add

orrfnbr = ?

in the where clause immedietly the accessplan picks up the index . But we cannot use this separately and hence are joining orrfnbr ( order number) with the srornbr ( order number in slot table) .

Please let me know if you need any further information.

 

[juliane26]

it is rather difficult to analyze without the ddl and statistics.

what kind of join do you get then ?
which optimize level are you using ?

 

[kkitt]

Are the datatypes/sizes the same for srornbr, orrfnbr and eornbr ?

Index may not be used if the datatypes and sizes not the same.

Which vesion of DB2, platform are you using.