| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: How does 9.2i pick a path in rule mode
Binyamin Dissen wrote:
>
> I am new to Oracle, with DB2 experience.
>
> A table X has more than one index,
> index1 being A,B,C
> index2 being A,C
> other indices
>
> I do a SELECT COUNT(b) FROM x WHERE a = value;
>
> Explain shows that index2 is used and a table scan is performed. And the query
> takes a long time.
>
> SELECT COUNT(b) FROM x WHERE a = value and b > 0 (all b's are positive
> numbers)
>
> Explain shows index1 is used and it can get all the information from an index
> range scan. And the query is relatively quick.
>
> I also tried SELECT COUNT(b) FROM x WHERE (a = value and b > 0) OR
> (a = value and b < 1)
> but Oracle "optimized" it to the first case.
>
> Why would Oracle choose index2 over index1 for the first form of the query?
>
Binyamin,
Oracle in rule mode has no clue about any imbalance or whatever. All it knows is basically whether indices are unique or not. It prefers equality to any other kind of comparison, and constants to columns. As far as concatenated indices are concerned, it considers that the more references to column from the index, the better. When you write
a = value
you refer to 50% of index2 columns, and 33[.33333...] % of columns of
index1. Index2 therefore looks better.
When you refer to both a and b, you still refer to 50% of columns of
index2, but to 67% of columns of index1, which suddenly becomes very
tempting.
I don't understand your 'index2 is used and a table scan is performed'.
You're probably accessing through the index but it isn't necessarily
more efficient than a table scan.
HTH Stephane Faroult
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Sun May 09 2004 - 04:49:15 CDT
 |
 |