Re: Statistics on Indexes

Yes thats excatly what I meant. I was confused by a report I got back from an implementation manager. A DBA told him that he had to update statistics on the index.

The default parameter for the optimizer is choose. As far as I am aware, no statistics were generated on any tables or indexes. However, the report I got back was that after updating statistics, the query went from 50+sec to 3 sec. It looks like somebody has been playing with things they don't understand. Grrr

Jaap W. van Dijk wrote in message ...
> "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 Fri Feb 04 2000 - 04:15:33 CST