modify query for indexspace scan

ashimer · Posted: Wed Mar 12, 2008 12:58 am

hi all,

i have a cursor with the where clause as

where

(trn_isin = :smx-isin

or

trn_isin = :ws-und-isin

or

trn_cusip = : smx-cusip )

here trn_isin is index ..

now the selection criteria is either of the 3 values should be present in the table

if im using the above where clause the query is going for a tablespace scan
maybe because of OR ... can anyone modify this query to do a indexspace scan ?

thanks,
ashimer

dick scherrer · Posted: Wed Mar 12, 2008 9:27 am

Hello,

It appears that 2 of your 3 predicates are covered by the index but not the third. If all 3 predicates must be or'ed, this (trn_cusip = : smx-cusip) cannot be satisfied by the index and requires the full table scan.

ashimer · Posted: Wed Mar 12, 2008 5:23 pm

thnks for ur ans dick .. now wht if i add trn_cusip also to the index group ?

i tried this too but still it is doing a tb scan ...in this case can you help with an alternate query ?

thanks

Craq Giegerich · Posted: Wed Mar 12, 2008 5:38 pm

After you added the trn_cusip to the index did you update the table statistics?

ashimer · Posted: Wed Mar 12, 2008 6:51 pm

the table is a global temporary table ....

ashimer · Posted: Wed Mar 12, 2008 11:55 pm

as OR predicates are not indexable can this where clause be changed so as to use IN predicate ?

ashimer · Posted: Thu Mar 13, 2008 3:02 am

TRN_REFERENCE TRN_VERSION
---------+---------+--------
200503070007 3
200503070007 3

---------+---------+---------+---------+--
TRN_CCT_SEC_ID TRN_ISIN TRN_CUSIP
---------+---------+---------+---------+--
470228 AU000000BHP4
479447 US0886061086 088606108

the data in the table for

smx-isin = AU000000BHP4 and smx-cusip = 088606108
and ws-und-isin = US0886061086

now my above where clause will give me these 2 rows ..

now i need to change the where clause to get these
2 rows and without using OR predicate ...

the indexes are trn_isin , trn_cusip and trn_cct_sec_id ..

unions can be used but still it wont go for a indexscan ... so only
possibility is joins ...

pls help