Re: query using different indexes now

From: <mark.powell_at_eds.com>
Date: Thu, 04 Feb 1999 01:45:00 GMT
Message-ID: <79au2p$e9e$1@nnrp1.dejanews.com>

In article <79a09h$m64$1_at_nnrp1.dejanews.com>, gplagge_at_my-dejanews.com wrote:
> Hi,
> We have some selects that have changed which indexes they are using, according
> to Explain Plan, and also according to a big decrease in performance.
>
> The queries we are concerned about use a RULE hint in them, so underlying
> changes in number of rows, etc. shouldn't affect them, right?
>
> The queries have not been modified (they are in stored procedures). The schema
> has not changed for these tables and indexes. And yet, explain plan says they
> are using slower indexes now.
>
> We are running 8.0.5.1.0 on NT.
>

I have seen Oracle switch indexes before and I have noticed that it seems to choose the most recently rebuilt index when it does this. Obviously both indexes meet the rules and Oracle somehow breaks the tie. Try dropping and re- creating the index you want it to use and see what happens.

We have coded the /*+ INDEX(label INDEX_NAME) */ hint in queries where this happens to us to point Oracle to the better preforming index.

The cause of the switch may differ from the above, but I think using the hint is a valid approach. You may need to add another hint to help it choose the same or very similiar access path to what it was doing under rule.

Mark D. Powell -- The only advice that counts is the advice that you follow so follow your own advice --

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 03 1999 - 19:45:00 CST