Oracle9i New Feature Series: Index Key Compression

Index key compression introduced in Oracle9i lets you compress the leading columns of an index (or index organized table) to save space. Oracle compresses only non-partitioned indexes that are non-unique or unique indexes of at least two columns. Bitmap indexes cannot be compressed.

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.

Key compression is achieved by breaking the index entry into two pieces – a prefix entry (or the grouping piece) and the suffix entry (the unique piece). 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.

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.

Example creating a compressed index-organized table:

CREATE TABLE BIJU_TEST_C

(OWNER VARCHAR2(30),

TABLE_NAME VARCHAR2(30),

TABLESPACE_NAME VARCHAR2 (30),

PRIMARY KEY (OWNER, TABLE_NAME))

ORGANIZATION INDEX

COMPRESS;

 Example creating a compressed index:

CREATE INDEX BIJU_IND1

ON BIJU_TAB1 (COUNTRY, STATE, SEX)

TABLESPACE INDX_TS

COMPRESS;

You can specify an integer along with the COMPRESS clause, which specifies the number of prefix columns to compress. For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default is the number of key columns minus 1. For non-unique indexes, the valid range is from 1 to the number of key columns. The default is the number of key columns.