Using Index Compression

Bitmap indexes are always stored in a patented, compressed manner without the need
of any user intervention. B-tree indexes, however, can be stored specifically in a
compressed manner to enable huge space savings, storing more keys in each index
block, which also leads to less I/O and better performance.

Key compression lets you compress a B-tree index, which reduces the storage
overhead of repeated values. In the case of a nonunique index, all index columns can
be stored in a compressed format, whereas in the case of a unique index, at least one
index column has to be stored uncompressed.

Generally, keys in an index have two pieces, a grouping piece and a unique piece. If
the key is not defined to have a unique piece, Oracle provides one in the form of a
rowid appended to the grouping piece. Key compression is a method of breaking off
the grouping piece and storing it so it can be shared by multiple unique pieces. The
cardinality of the chosen columns to be compressed determines the compression ratio
that can be achieved. So, for example, if a unique index that consists of five columns provides the uniqueness mostly by the last two columns, it is most optimal to choose the three leading columns to be stored compressed. If you choose to compress four columns, the repetitiveness will be almost gone, and the compression ratio will be worse.
Although key compression reduces the storage requirements of an index, it can
increase the CPU time required to reconstruct the key column values during an index
scan. It also incurs some additional storage overhead, because every prefix entry has
an overhead of four bytes associated with it.

Posted in: Index