Re: How does 9.2i pick a path in rule mode
- From: Stephane Faroult <sfaroult@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 09 May 2004 11:50:13 +0200
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
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
