Re: Indexes and the rule_based optimizer

In <4qnu4n$vn8_at_news00.btx.dtag.de>, willyk_at_t-online.de (Willy Klotz) writes:
>joe_at_access4.digex.net (Joe Nardone) wrote:
>
>>: The database use the rule-based optimizer. The world according to
>>: ORACLE7 Server Concepts Manual p13-33, the primary key must always be
>>: used in scenario explained above.
>>:
>>: Does anybody have an idea why this is happening?
>>:
 

>>The rule based optimizer has a "feature". When Oracle determines
>>that there are two indexes that are equal in returning the result
>>set (not by looking at data volumes, mind you, but some other way)
>>it breaks the tie by using the index that was CREATED MOST RECENTLY.
>>Oftentimes, the two indexes are not as equal as Oracle thinks they are.
>>In this situation, using the rule-based optimizer, one way to "solve"
>>the problem is to drop and recreate the index you want it to use.
>
>Are you sure about this "feature" ? Is it documented, and where ?
>
>I had the impression that, in the described case, the _smaller_ index
>is used....
>
>>--
>>
>>=-------------------------------------------------------------------------=
>>Joe Nardone <joe_at_access.digex.net>
>
>======================================================================
>Willys Mail FidoNet 2:2474/117 2:2474/118
> Mailbox: analog 06297 910104
> ISDN 06297 910105
> Internet: 0629791010_at_t-online.de
> -> No Request from 06.00 to 08.00 <-
>======================================================================
>
>

Came across the same problem myself, it isn't documented but Oracle support said the same thing as Joe - last created index is used.

Bye,
Steve

opinions expressed are mine and do not
necessarily represent those of my employer Received on Mon Jul 01 1996 - 00:00:00 CEST