What is Index Compression?
Oracle introduced a compression option for indexes in Oracle 8.1. You can create an index as compressed, or rebuild it to compress it.

--create index with compress keyword

Key compression lets you compress portions of the primary key column values in an index or index-organized table, which reduces the storage overhead of repeated values.

--对索引键值的一部分进行压缩，只能作用在B树索引或者索引组织表上。

Key compression breaks the index key into a prefix entry (the grouping piece) and a suffix entry (the unique piece,nearly the unique piece). Compression is achieved by sharing the prefix entries among the suffix entries in an index block. Only keys in the leaf blocks of a B-tree index are compressed. In the branch blocks the key suffix can be truncated, but the key is not compressed. Key compression is done within an index block but not across multiple index blocks. Suffix entries form. the compressed version of index rows. Each suffix entry references a prefix entry, which is stored in the same index block as the suffix entry.

--索引键值有grouping piece和Unique piece两部分组成，而键值压缩则将索引键值分成prefix entry和suffix entry，前者就是grouping piece，后者则可能是或者接近是Unique piece。Oracle 键值压缩是通过在索引块中suffix entry共享prefix entry来实现的。键值压缩只能在一个数据块中进行，不能跨block。

The maximum prefix length for a nonunique index is the number of key columns, and the maximum prefix length for a unique index is the number of key columns minus one. Prefix entries are written to the index block only if the index block does not already contain a prefix entry whose value is equal to the present prefix entry. Prefix entries are available for sharing immediately after being written to the index block and remain available until the last deleted referencing suffix entry is cleaned out of the index block.

--默认情况下，唯一性索引的prefix包括出去最后一列的所有的索引列，而非唯一性索引的prefix则包括所有的索引列。如果索引列的个数为n，则前者prefix长度是n-1，后者则为n。该数值可在指定压缩属性的同时指定prefix length。

Performance and Storage Considerations

The benefits of compression come from the fact that a properly compressed index uses a smaller number of leaf blocks – which tends to mean that less I/O is involved when the index is used, there is a reduced amount of buffer cache flushing, and the optimizer is likely to calculate a lower cost for using that index for range scans. But the number of branch blocks, and the index height may not be reduced.

--键值压缩可以减少I/O

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 4 bytes associated with it.

--键值压缩同时带来CPU的消耗

For example, suppose that you create a composite index on the oe.orders table as follows:

CREATE INDEX orders_mod_stat_ix ON orders ( order_mode, order_status );

Many repeated values occur in the order_mode and order_status columns. An index block may have entries as shown here:

online,0,AAAPvCAAFAAAAFaAAa

online,0,AAAPvCAAFAAAAFaAAg

online,0,AAAPvCAAFAAAAFaAAl

online,2,AAAPvCAAFAAAAFaAAm

online,3,AAAPvCAAFAAAAFaAAq

online,3,AAAPvCAAFAAAAFaAAt

The key prefix would consist of a concatenation of the order_mode and order_status values. If this index were created with default key compression, then duplicate key prefixes such as online,0 and online,2 would be compressed. Conceptually, the database achieves compression as shown in the following example:

online,0

AAAPvCAAFAAAAFaAAa

AAAPvCAAFAAAAFaAAg

AAAPvCAAFAAAAFaAAl

online,2

AAAPvCAAFAAAAFaAAm

online,3

AAAPvCAAFAAAAFaAAq

AAAPvCAAFAAAAFaAAt

Suffix entries form. the compressed version of index rows. Each suffix entry references a prefix entry, which is stored in the same index block as the suffix entry.