Index Partition Statistics

Something to be wary of when rebuilding partitioned indexes. When rebuilding a normal index, the statistics for that index are not affected as the example below shows

For "normal" indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things go astray when looking at function-based indexes.

SQL> create table test1 ( x number, y number )
  2  partition by range ( x )
  3  ( partition p1 values less than (500),
  4    partition p2 values less than (1000))
  5  /

Table created.

SQL> insert into test1 select rownum , rownum from all_objects
  2  where rownum < 1000;

999 rows created.

SQL> commit;

Commit complete.

SQL> create index test1_ix2 on test1 ( y ) global;

Index created.

SQL> analyze table test1 compute statistics;

Table analyzed.

SQL> select index_name, leaf_blocks from user_indexes;

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST1_IX2                                3

SQL> alter index TEST1_IX2 rebuild;

Index altered.

SQL> select index_name, leaf_blocks from user_indexes;

INDEX_NAME                     LEAF_BLOCKS
------------------------------ -----------
TEST1_IX2                                3

Thus no change to the statistics when the index has been rebuilt. However, if we examine an index which is partitioned then the statistics display a very different characteristic.

SQL> create index test1_ix on test1 ( x ) local;

Index created.

SQL> analyze table test1 compute statistics;

Table analyzed.

SQL> select index_name, partition_name, leaf_blocks from user_ind_partitions;

INDEX_NAME     PARTITION_NAME      LEAF_BLOCKS
-------------- ------------------- ------------ 
TEST1_IX       P1                  2
TEST1_IX       P2                  2

SQL> alter index TEST1_IX rebuild partition p1;

Index altered.

SQL> analyze table test1 compute statistics;

Table analyzed.

SQL> select index_name, partition_name, leaf_blocks from user_ind_partitions;

INDEX_NAME     PARTITION_NAME      LEAF_BLOCKS
-------------- ------------------- ------------ 
TEST1_IX       P1                    <=Gone!
TEST1_IX       P2                  2

Moral: Whereas its good practice to re-analyze an index whenever you rebuild it, it could be extremely important in the case of index partitions