Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Re-analyze Table After New Index ?

Re: Re-analyze Table After New Index ?

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Wed, 29 Aug 2007 12:42:23 -0700
Message-ID: <1188416543.871204.228350@k79g2000hse.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US