Re: Statistics on Indexes

 "Indexes using statistics" is a meaningless phrase, maybe you mean "the optimizer uses the statistics on the indexes". If yours is really a rule based optimizer then the optimizer will not look at any statistics and therefore will not be affected by them. So if the optimizer IS affected by them, the only conclusion that is left is that the optimizer is cost based.

Check the parameter 'optimizer_mode' in the view v$parameter which can have values 'RULE', meaning just that, and 'CHOOSE', meaning rule based if none of the involved tables and indexes has values in the statistics fields, otherwise cost based. In the latter case default values for statistics are assumed for tables and indexes without them.

Jaap.

Keith Jamieson heeft geschreven in bericht <87c65d$lpf$1_at_kermit.esat.net>...
>I am slightly confused by some information that I got today. My
>understanding is that all our customers are using the rule-based optimizer.
>Recently, we added some indexes to a couple of tables to improve
>performance. Approx a week later the performance was as bad as before we
put
>the indexes on. The information fed back to me was that the statistics on
>the index had to be updated.
>So the immediate question which comes to mind is:
>Do indexes use statistics even if the rule based optimizer is being used?.
>If this is the case, would dropping and recreating the index not have the
>same effect?
>
>
>
Received on Thu Feb 03 2000 - 11:08:55 CST