Re: full table scan cheaper than index??

Jan,

Try setting the OPTIMIZER_MODE to FIRST_ROWS. When the OPTIMIZER_MODE is set to CHOOSE and there are statistics for the tables involved, it actually runs in the ALL_ROWS mode. The documentation says that ALL_ROWS will prefer full tables scans over using indexes. This is because ALL_ROWS is meant for batch type systems where the fastest overall throughput is desired. FIRST_ROWS, the documentation says, is for online type systems where the user is waiting to see a response. Because of this, FIRST_ROWS will tend to use indexes more. This is the mode we run in where I work and have found the documentation to correct.

Hope this helps,

DeVerne Coleman

Jan Haase <jh_at_informationsdesign.de> wrote in message news:3B4D90FF.6602DD82_at_informationsdesign.de...
> Hi.
>
> I've got a serious problem: Yesterday I found out that some queries to a
 table
> were very slow, so I gave the "analyze"-command (estimate) to get new
> information about the data.
> Now the optimizer seems to be completely nuts, it doesn't use any index at
> all!
> "Set autotrace on" tells me that the cost of using an index is much higher
> than a full table scan.
>
> The database (Oracle 7.3.4) runs in "CHOOSE"-Mode, so it uses cost-based
> analysis.
> I used "analyze table xxx estimate statistics sample 10 percent;" and
 another
> "analyze"-command for the index to be used didn't change oracle's
 behaviour.
> Even deleting the analysis-data (by typing "analyze table xxx delete
> statistics") didn't help.
>
> Please help me, for I don't see where I think in the wrong direction.
>
> Thanks in advance,
> Jan
>
>
Received on Sat Jul 21 2001 - 16:34:59 CDT