Re: OPTIMIZER / ANALYZE INDEX

Probably the analyze index statistic estimate does not warrant the cost-based optimizer to effectively compute the cost. You should analyze the table too to make sure the stats are available.

Try to use rule based optimizer, it's more predictable. Try the hint /*+ rule */

Hope this helps.

Alan Lee
BSI In article <6lkke7$8k3$1_at_nnrp1.dejanews.com>,   pfrancescani_at_compuserve.com wrote:
>
> After creating an index on a date column on a 3 million row table, I select
> count(*) from table where date column > whatever. The optimizer uses the
> index. I run 'analyze index <index name> estimate statistics' . Now the
> optimizer does not use the index , even if I try to force it with a hint.
> This is happening in 7.3.3 and 8.0.4 on Sun. This does not happen on a
> similar table/index with fewer rows.
>
> If anyone can help, what I'd like to know is : a. Why doesn't the
optimizer
> choose the index after I've analyzed the index? My understanding is that
> analyzing objects should help the optimizer. b. Why wouldn't my hint work?
> c. How can I reset what the 'analyze index' statement does without dropping
> and re-creating the index?
>
> Thanks
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Fri Jun 12 1998 - 16:47:33 CDT