OPTIMIZER / ANALYZE INDEX

From: <pfrancescani_at_compuserve.com>
Date: Wed, 10 Jun 1998 00:36:23 GMT
Message-ID: <6lkke7$8k3$1@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 - 19:36:23 CDT