Re: Indexes and the rule_based optimizer

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

>You can also use hints with the rule-based optimizer, specifically
>the INDEX hint. i.e. SELECT /*+INDEX (test pk_test)*/ ... (or
>similar; I don't remember the exact syntax at the moment.)
 

>Joe

>--
>
>=-------------------------------------------------------------------------=
>Joe Nardone <joe_at_access.digex.net>
> "The most beautiful thing we can experience is the mysterious. It is
> the source of all true art and all science. He to whom this emotion
> is a stranger, who can no longer pause to wonder and stand rapt in awe,
> is as good as dead: his eyes are closed." - Einstein

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