Index Statistics with Compressed Indexes


You can check Steve Adam's site for a justification for using compression on indexes. Similarly most Oracle user group sites have a copy of Dave Ensor's favourable investigation. One aspect worthy of note is that for the time being (8.1.6.2 and below, and probably 8.1.7), the optimizer statistics gathered by ANALYZE are somewhat dubious.

Consider the following example


SQL> create index FEOP_IND_1 on FEOP ( FID );

  Index created.

SQL> analyze index FEOP_IND_1 compute statistics;

  Index analyzed.

SQL> select index_name, leaf_blocks, distinct_keys
  2  from user_indexes;

INDEX_NAME   LEAF_BLOCKS DISTINCT_KEYS
------------ ----------  --------------
FEOP_IND_1   386         351

which is the correct number of distinct keys. However when the index is compressed and re-analyzed...

SQL> alter index FEOP_IND_1 rebuild compress 1;

  Index altered.

SQL> analyze index FEOP_IND_1 compute statistics;

  Index analyzed.

SQL> select index_name, leaf_blocks, distinct_keys
  2  from user_indexes;

INDEX_NAME   LEAF_BLOCKS DISTINCT_KEYS
------------ ----------  --------------
FEOP_IND_1   284         184495

This is a known bug which is fixed in 8.1.6.3. For single column indexes, this error does not seem to have a great impact, since the optimizer appears to make decisions on selectivity based the column statistics (from USER_TAB_COLUMNS). However, for multi-column indexes, incorrect optimizer decisions are possible so make sure you are fully patched up before using this feature.