Index Key
Compression
Oracle Database can use key compression to compress
portions of the primary key column values in a B-tree
index or an index-organized table. Key compression can greatly reduce the space
consumed by the index.
In general, index keys have two pieces, a grouping
piece and a unique piece. Key compression breaks the index key into a prefix entry, which is the grouping piece,
and a suffix entry, which is the unique or nearly unique piece.
The database achieves compression by sharing the prefix entries among the
suffix entries in an index block.
Note:
If a key is not defined to have a
unique piece, then the database provides one by appending a rowid to the
grouping piece.
By default, the prefix of a unique index consists of all
key columns excluding the last one, whereas the prefix of a nonunique index consists
of all key columns. 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 in Example 3-3.
Example 3-3
Index Entries in Orders Table
online,0,AAAPvCAAFAAAAFaAAa
online,0,AAAPvCAAFAAAAFaAAg
online,0,AAAPvCAAFAAAAFaAAl
online,2,AAAPvCAAFAAAAFaAAm
online,3,AAAPvCAAFAAAAFaAAq
online,3,AAAPvCAAFAAAAFaAAt
In Example 3-3, 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.
Alternatively, you could specify a prefix length when
creating a compressed index. For example, if you specified
prefix length 1, then the prefix would be order_mode and the
suffix would be order_status,rowid. For the values in Example 3-3, the index
would factor out duplicate occurrences of online as follows:
online
0,AAAPvCAAFAAAAFaAAa
0,AAAPvCAAFAAAAFaAAg
0,AAAPvCAAFAAAAFaAAl
2,AAAPvCAAFAAAAFaAAm
3,AAAPvCAAFAAAAFaAAq
3,AAAPvCAAFAAAAFaAAt
The index stores a specific prefix once per leaf block at
most. 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.
See Also:
· Oracle Database Administrator’s Guide to learn how
to use compressed indexes
· Oracle Database VLDB and Partitioning
Guide to learn how to use key compression for partitioned
indexes
· Oracle Database SQL Language Reference for
descriptions of the key_compression clause of CREATE INDEX
key_compression Specify COMPRESS to enable key
compression, which eliminates repeated occurrence of key column values and may
substantially reduce storage. Use integer to specify the prefix length (number
of prefix columns to compress).
Oracle Database compresses indexes that are nonunique or
unique indexes of at least two columns. If you want to use compression for a
partitioned index, then you must create the index with compression enabled at
the index level. You can subsequently enable and disable the compression
setting for individual partitions of such a partitioned index. You can also
enable and disable compression when rebuilding individual partitions. You can
modify an existing non-partitioned index to enable or disable compression only
when rebuilding the index.
·
For unique indexes, the valid range of prefix length
values is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.
·
For nonunique indexes, the valid range of prefix length
values is from 1 to the number of key columns. The default prefix length is the number of key columns.
Restriction on Key Compression You cannot
specify COMPRESS for a bitmap index.
NOCOMPRESS Specify NOCOMPRESS to disable
key compression. This is the default.
Compressing an
Index: Example To create the ord_customer_ix_demo index with
the COMPRESS clause, you might issue the following statement:
CREATE INDEX ord_customer_ix_demo
ON orders (customer_id, sales_rep_id)
COMPRESS 1;
