Re: OPTIMIZER / ANALYZE INDEX

ESTIMATE STATISTICS without a sample size clause collects statistics for just a small fraction of the records. Try using ANALYZE INDEX index_name ESTIMATE STATISTICS SAMPLE 20 PERCENT (or more). Better yet COMPUTE STATISTICS if you can spare the time. My guess is the optimizer just does not have enough information to make efficient use of the index.

Venkat

pfrancescani_at_compuserve.com wrote in message <6lkke7$8k3$1_at_nnrp1.dejanews.com>...
>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
Received on Tue Jun 09 1998 - 20:51:30 CDT