Automatic INDEX statistics

Nice feature in 10g and onward - index creation or rebuild automatically calculates statistics for the index. let's check it practically here.


SQL> conn /as sysdba
Connected.


SQL> create table T as select * from dba_objects;

Table created.


SQL> create index tx on T(object_id);

Index created.


SQL> select distinct_keys from user_indexes where index_name='TX';

DISTINCT_KEYS
-------------
        72455



Above you can see statistics is gathered for the index TX created in earlier statement.

Let's now delete the statistics for this index and try to rebuild to see if it gathers the state again automatically by just rebuidling the index.


SQL> analyze index tx delete statistics;

Index analyzed.


SQL> select distinct_keys from user_indexes where index_name='TX';

DISTINCT_KEYS
-------------


Note :- You can see statistisc is no longer available for the index.


Now let's rebuild the index now.

SQL> alter index tx rebuild;

Index altered.



SQL>  select distinct_keys from user_indexes where index_name='TX';

DISTINCT_KEYS
-------------
        72455



Statistics for the index gathered automatically by rebuilding it.