Oracle index compression: eliminating duplicate column values

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