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

From: Naveen, Nahata (IE10) <Naveen.Nahata_at_honeywell.com>
Date: Fri, 7 May 2004 03:40:28 -0700
Message-ID: <77ED2BF75D59D1439F90412CC5B109740B999BE1@ie10-sahara.hiso.honeywell.com>

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)

Regards
Naveen

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

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

Received on Fri May 07 2004 - 05:58:00 CDT