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: Thu, 30 Aug 2007 09:54:19 -0700
Message-ID: <1188492859.341663.166200@y42g2000hsy.googlegroups.com>


On Aug 29, 7:38 pm, purohita..._at_gmail.com wrote:
> On Aug 29, 1:25 pm, "userjohn" <userj..._at_mailinator.com> wrote:
>
>
>
>
>
> > "Mark D Powell"
>
> > > 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 --
>
> > ah good to know that thanks !- Hide quoted text -
>
> > - Show quoted text -
>
> I believe dynamic sampling is turned on in 10G whereas it was optional
> till 9iR2.
> That could have caused automated stats gathering on the index in 10G
>
> -Atul- Hide quoted text -
>
> - Show quoted text -

Atul, dynamic sampling is not responsible.

>> From the 10g SQL manual entry for CREATE INDEX >>
COMPUTE STATISTICS In earlier releases, you could use this clause to start or stop the collection of statistics on an index. This clause has been deprecated. Oracle Database now automatically collects statistics during index creation and rebuild. This clause is supported for backward compatibility and will not cause errors. <<

HTH -- Mark D Powell -- Received on Thu Aug 30 2007 - 11:54:19 CDT

Original text of this message

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