Re-analyze Table After New Index ?

[userjohn]

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

[fitzj...@cox.net]

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

[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 --

[userjohn]

"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 !

[puroh...@gmail.com]

> 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

[Mark D Powell]

> -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.