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> validate index nocomp_i1;  Index analyzed.
SQL> select opt_cmpr_count,opt_cmpr_pctsave from index_stats; 
OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
             1                0