modify query to use indexspace

[ashimer2007]

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

 

[ashimer2007]

if need arise i can make trn_cusip as another index ..
IN predicate should work or else joins ..

 

[fredericofonseca]

Doing a table scan on this particular case is the best option.

Think about it.

The (trn_isin = :smx-isin or trn_isin = :ws-und-isin) on their own will be doing a index access, then a table access. However as you also have the other field which is not an index, even those entries that were excluded from a index access will need to have a table access to determine if the record should be included.

If you know that the percentage of records returned by either condition will be a small part of the total of the table, then creating another index on trn_cusip , and using a union (all) will probably be the best way to do it.
If on the other hand those 3 conditions mean that you will be getting 80% or more of the records, then table scan access is faster.


Even if you add the other index, doing the "where" on the same select would still only use one of them, or none. Union would be the only option to use BOTH indexes.

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886