Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Re-analyze Table After New Index ?
On Aug 29, 3:00 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> On Aug 29, 1:14 pm, "userjohn" <userj..._at_mailinator.com> wrote:
>
> > 10gR2
>
> > If a couple of (non-unique) indexes are added to a table...
> > is the table supposed to be re-analyzed (immediately) to be useful to the
> > CBO ?
>
> > thanks
>
> Immediately, no, as the presence of the indexes may be enough for
> Oracle to know to use them. However the CBO might gain some added
> information with the updated statistics. I'll defer to Jonathan Lewis
> to give a definitive answer.
>
> David Fitzjarrell
Try the following experiment. Create a simple table. Populate it. Create statistics on it. Now add an index and then query:
select leaf_blocks, distinct_keys,
AVG_LEAF_BLOCKS_PER_KEY, AVG_DATA_BLOCKS_PER_KEY
from dba_indexes
where index_name = 'Your index'
On 9.2.0.6 I see no statistics. On 10gR2 (using Oracle Express) I see statistics.
I do believe that on create index and alter index rebuild that compute statistics is now the default.
HTH -- Mark D Powell -- Received on Wed Aug 29 2007 - 14:42:23 CDT