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

If you are using the cost-based optimizer (ie. if your tables are analyzed), the optimizer should pick the index for the column that has the highest selectivity. You must be using the rule-based optimizer, because it would pick the index for the first column listed in the WHERE clause. So:

1. Do a periodic "ANALYZE TABLE tab1 COMPUTE STATISTICS" (ie. use the cost-based optimizer), and let the problem fix itself.
2. Imbed a hint into the SQL statement like so " select /*+index(tab1 tab1_indexname)*/ col1, col2 from tab1 where col1 = 'x' and col2 = 'y' This also forces use of the cost-based optimizer (I believe).
3. Try creating an index on (col2, col1) [assuming col2 has the higher selectivity]. The CBO will most likely choose to use this index, which will probably go much faster than either of the others. The query can be satisfied by reading only the index, so no reads on the table are required.

Hope this helps,
Dave Sisk

José Lima Suárez wrote in message <6hb66o$s5b$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...
>
>
>
Received on Sat Apr 18 1998 - 20:55:53 CDT