Index compression comes from Oracle 8i and has quite the same mechanism as table compression. Index compression eliminates duplicate column values in the leaf blocks. Let's see how it works:

We start by creating an index on an uncompressed table:

SQL> create index nocomp_i1 on nocomp (object_name,object_id);

Index created.

Let's have a look at how many leaf blocks the newly created index has:

SQL> select leaf_blocks from user_indexes where index_name = 'NOCOMP_I1'; 

LEAF_BLOCKS

        407

If you want to estimate the ideal compression factor and the percentage of leaf blocks that can be saved, you need to consult INDEX_STATS view after validating the newly created index (watch out, this will lock the index):

SQL> validate index nocomp_i1; 

Index analyzed. 

SQL> select opt_cmpr_count,opt_cmpr_pctsave from index_stats; 

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

-------------- ----------------

             1               15

"opt_cmpr_count" = number of columns to compress in the index to get maximum space savings in the leaf blocks. 

"opt_cmpr_pctsave" = percentage reduction in leaf block space used if we apply this compression count.

The previous result shows that the ideal compression factor is 1 and will reduce the number of leaf blocks by 15 %.

SQL> alter index nocomp_i1 rebuild compress 1; 

Index altered.

SQL> select leaf_blocks from user_indexes where index_name = 'NOCOMP_I1'; 

LEAF_BLOCKS

        345

The result is correct : 407 * 15 % = 61 and 407-61 = 346!

If we now validate the index again, we can verify that the compression factor of 1 is fine and no leaf blocks can be eliminated anymore:

SQL> select opt_cmpr_count,opt_cmpr_pctsave from index_stats; 

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

-------------- ----------------

             1                0