Databases Reference
In-Depth Information
Key-Compressed Indexes
Often, an index includes multiple occurrences of key column prefix values. Oracle Database's key
compression feature lets you separate an index key into two entries, a prefix and a suffix. Key
compression lets the suffix entries in an index block share the prefix entries, thus letting you store more
keys per index block. Of course, you save on storage and you improve performance as a result.
Performance may be improved since there are fewer leaf blocks that may need to be accessed due to the
compression. While it's obvious that compression saves storage space, it's natural to wonder if the
overhead of compression is going to negate the benefits of compressing data. In Oracle Database 11g,
the new online transaction processing (OLTP) table compression feature enables the compression of
data during all DML operations and minimizes overhead during write operations, thus making it usable
in online environments. Oracle doesn't have to uncompress the compressed data before reading it and
the data stays in a compressed form in the cache, thus taking up a smaller amount of cache space.
You can use key compression to compress parts of primary key column in either a regular B-tree
index or an index-organized table. Each index key has two components: a grouping piece and a unique
piece. When you compress index keys, the database breaks the index key into a prefix entry (grouping
piece) and a suffix entry (unique piece). The number of key columns determines the maximum prefix
length in a nonunique index. In a unique index, it is the number of key columns minus one.
When Key Compression is Useful
Key compression is useful in any situation where the database needs to deal with duplicate values in the
index keys. For example, if you have a unique index on two columns such as
STOCK_TICKER
and
TRANSACTION_TIME
, you can envisage numerous rows with the same stock ticker, such as
NYT
, but with
different
TRANSACTION_TIME
values. When you compress this index, the database stores the value of the
STOCK_TICKER
column only once in each index block, as a prefix entry. It stores the
TRANSACTION_TIME
column values as suffix entries that reference the same
STOCK_TICKER
prefix entry.
In the case of a non-unique index, the database appends a
ROWID
to duplicate keys to distinguish
between the rows. When you compress such an index, the database stores the duplicate key as a prefix
entry in the index block. All the duplicate entries are stored as suffix entries, which consist only of a
ROWID
.
You can use key compression when dealing with indexing a
VARRAY
or a
NESTED TABLE
data type
because the database repeats the same object ID for each of a collection data type's elements. In this
case, you can employ key compression to reduce storage for the repeating object ID values.
Note
Oracle Database will compress only leading columns in an index. In the case of a non-unique index, this
can be all columns in an index. In the case of a unique index, it can be all but the last column.
There are cases when key compression will actually have a negative impact on index storage. Note
that the prefix table stores the unique compressed column values in an index leaf block. If your index's
leading column or the compressed columns are extremely selective, the prefix table will have many
distinct values. Oracle Database is forced to create a large prefix table to store the individual column
values. The prefix entries aren't shared by many index row entries. Compressing such an index is
actually counterproductive because the compression factor and thus the storage savings are very low.
