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;
