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 05:19:05 -0700
Message-ID: <77ED2BF75D59D1439F90412CC5B109740B99A81F@ie10-sahara.hiso.honeywell.com>

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

Yes, RBO doesn't cosider costs at all. For an RBO all indexes are same, since it doesn't consider the cost factor. So if RBO thinks that 2 indexes can be used based on the heuristics, it will choose either one of them. And AFAIK, it chooses the one with a higher object_id

CBO should definitely help here, since CBO looks at statistics like "No. of leaf blocks" which would definitely be higher in case of a 3 column index as compared to a 2 column index. So CBO will evaluate both the indexes.

You said CBO is not an option. But you can always force a particular query to be evaluated by the CBO by using a hint.

But remember that in the absence of statistics, CBO will assume default costs and your plan might be even worse. And if you analyze the objects involved and have set init.ora parameter OPTIMIZER_MODE=CHOOSE, then all the queries which are accessing any of the analyzed objects but are optimized for RBO will start using CBO instead.

Regards
Naveen

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 - 07:43:21 CDT