Automatic Index Statistics


Nice feature in 10g - index creation or rebuild automatically calculates statistics for the index

SQL> create table T
  2  as select * from dba_objects;

Table created.

SQL> create index TX on T ( object_id );

Index created.

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

DISTINCT_KEYS
-------------
        50613

SQL> analyze index TX delete statistics;

Index analyzed.

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

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


SQL> alter index TX rebuild;

Index altered.

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

DISTINCT_KEYS
-------------
        50613