Optimizing Storage Structures - Oracle Database 11gR2 Performance Tuning - page 124

There are two other fields on which we have to concentrate:
F OPT_CMPR_COUNT : It indicates how many fields can be compressed in the index to
obtain the maximum benefit from index key compression
F OPT_CMPR_PCTSAVE : It indicates the percentage of saved space if the previous
parameter is used in rebuilding the index
We have rebuilt the index with the recommended value for the COMPRESS parameter,
reanalyzed the index, and compared the results. We have saved 55 percent of database
blocks, as estimated previously. After the index was rebuilt, we have no further improvement
in compressing the index, as shown by the results of the query on INDEX_STATS .
There's more...
Compressing an index allows us to store the index in fewer database blocks, as in the
presented example, so we have fewer database blocks to read to navigate the index.
The value used when we execute a CREATE INDEX or ALTER INDEX REBUILD command
with the COMPRESS parameter indicates how many fields of the key fields—in the order they
are listed in the index creation statement—will be compressed.
We can compress any nonunique index with a value equal to the number of fields in the index,
as in our example. If the index is unique (that is, no duplicate values can be stored in the key
fields), we can use a value equal to the number of key fields minus one. By default, the prefix
length—this is another way to name this value—is the number of key columns.
Prefix length limitation does not allow us to compress a unique index with only one field, but
this isn't a problem. In this situation every key value is different—due to the uniqueness of the
index—so there won't be any improvement in compressing index keys because there aren't
duplicates.
From the performance point of view, when we use compressed indexes we make use of
slightly more CPU to manage the compress/decompress work.
Using reverse key indexes
In this recipe, we will introduce reverse key indexes. We will look at when to use them and
how they are related to performance.