Re: How does 9.2i pick a path in rule mode
- From: Binyamin Dissen <bdissen@xxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sun, 09 May 2004 13:57:46 +0300
On Sun, 09 May 2004 11:50:13 +0200 Stephane Faroult <sfaroult@xxxxxxxxxx>
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@xxxxxxxxxxxxxxxxxx>
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@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:
