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

José,

There are a number of ways you could try to achieve this: 1) If you are using COST based optimiser, analyse the tables, and the optimiser should work out which index to use.

2) Using RULE based optimiser:
select col1, col2
from tab1
where col1 = 'x' and col2 = 'y'
should use the index on column 1

select col1, col2
from tab1
where col2 = 'y' and col1 = 'x'
should use the index on column 2

3) Prevent the use of the index on column 1 using a function: select col1, col2
from tab1
where col1|| '' = 'x' and col2 = 'y'

4) Use a hint to 'suggest' which index to use

HTH,
Steve

"José Lima Suárez" <pplima_at_jet.es> wrote:

>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...
Received on Sun Apr 19 1998 - 06:21:53 CDT