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

On Fri, 7 May 2004 03:40:28 -0700 "Naveen, Nahata (IE10)" <Naveen.Nahata_at_honeywell.com> 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_at_dissensoftware.com]
:>>Sent: Friday, May 07, 2004 3:16 PM
:>>To: oracle-l_at_freelists.org
:>>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_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
-----------------------------------------------------------------