Re: Different index usage by Oracle* optimizer

There is a 'preferred mechanism' for replacing this trick in 8.0.5 upwards. There is a new init.ora parameter optimizer_index_caching, which is the percentage of the index (default 0) that is likely to be buffered.

Prior to this parameter, Oracle assumed that every index hit would be a physical read, which is sufficiently untrue in most cases that the costing could be seriously defective.

Set it to 100 for a really well buffered system, or close to you buffer hit ratio in more general purpose systems.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Keith Boulton wrote in message <37eb6505.90468537_at_news.globalnet.co.uk>...
>On Fri, 17 Sep 1999 11:11:55 GMT, broeni_at_my-deja.com wrote:
>
>I frequently find that Oracle 7 would perform a full table scan even
>when it was 1000x slower than an indexed search. I thought this had
>been improved in 8. My own preference is to put optimizer_mode =
>first_rows in init.ora. This may mean that a full table scan is
>avoided when it is more efficient but that generally does not cause a
>big performance problem.
>
Received on Sun Sep 19 1999 - 09:40:23 CDT