Re: How does 9.2i pick a path in rule mode

On Sun, 09 May 2004 11:50:13 +0200 Stephane Faroult <sfaroult_at_oriole.com> wrote:

:>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?

:> 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.

Using index1, the query can be satisfied without accessing the data pages. All the data is in the index.

:> 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.

If all the data can be obtained from the index it is clearly more efficient than doing a table scan.

And, based on elapsed time and resources, the query did access the data pages.

--
Binyamin Dissen <bdissen_at_dissensoftware.com>
http://www.dissensoftware.com

Director, Dissen Software, Bar & Grill - Israel
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------