Re: How does 9.2i pick a path in rule mode
- From: Binyamin Dissen <bdissen@xxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 07 May 2004 14:40:30 +0300
On Fri, 7 May 2004 03:40:28 -0700 "Naveen, Nahata (IE10)"
<Naveen.Nahata@xxxxxxxxxxxxx> wrote:
:>AFAIK RBO picks up the index with the highest object_id if more than one
:>index can be used.
:>In the first case, since the predicate is only on column a, both the indexes
:>can be used. Just check if the object_id of composite index on (a,c) is
:>higher than that of index on (a,b,c)
:>In the second query, composite index on (a,b,c) is selected since the
:>predicate is on both column a and column b, and hence the composite index on
:>(a,c) cannot be used.
:>If you want the index on (a,b,c) always be used, drop and re-create the
:>indexes in the right order i.e. on (a,c) first and then on (a,b,c)
In other words, Oracle does not check to see which is the best index?
If I flip-flop the order, a
SELECT COUNT(c) FROM x WHERE a = value
would use the worse index index1 (worse because it is bigger and would require
more index I/O's).
As a side point, would cost based help in this case?
Or does it still unconditionally use the latest defined index that matches the
predicates?
:>>-----Original Message-----
:>>From: Binyamin Dissen [mailto:bdissen@xxxxxxxxxxxxxxxxxx]
:>>Sent: Friday, May 07, 2004 3:16 PM
:>>To: oracle-l@xxxxxxxxxxxxx
:>>Subject: How does 9.2i pick a path in rule mode
:>>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.
--
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:
