RE: How does 9.2i pick a path in rule mode
- From: "Naveen, Nahata (IE10)" <Naveen.Nahata@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 7 May 2004 05:19:05 -0700
>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@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:
