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