Re: How can I optimize a query on two indexed columns?

"Jose Lima Suarez" <pplima_at_jet.es> wrote (in <6hb9ca$2mg$1_at_diana.bcn.ibernet.es>)...

|
| Hi,
|
| I have a query like this:
|
| select col1, col2
| from tab1
| where col1 = 'x' and col2 = 'y'
|
| and both col1 and col2 have differents indexes, col1 has a lot of rows per
| value so if Oracle uses this index is slowly than if uses the col2 index
| (that has a few rows per value).
|
| is there any way to force the index to use in the query execution?
|
| Thanks...

Hello,
  If you do a calculation on a column then the optimiser will not use that column in determining the execution path to use. So, for character columns concatenate an empty string, for number columns add zero. This give, in your case:

select col1, col2
from tab1
where col1||'' = 'x' and col2 = 'y'
/

Note: you cannot force Oracle to use an index but you can remove indexes from its choices.

Another way is to use a hint.

graham Received on Sun Apr 19 1998 - 10:59:15 CDT