Index Compression Part I (Low)

Index compression is perhaps one of the most under used and neglected index options available. It has the potential to substantially reduce the overall size of Non-Unique indexes and multi-column Unique indexes, in some scenarios dramatically so. A smaller index, especially if it stays permanently smaller without any subsequent expensive maintenance operations, it always a nice thing. Not only will it potentially save storage, but if the resultant index contains fewer leaf blocks, that’s potentially fewer LIOs and from the Cost Based Optimizer’s point of view, potentially a cheaper execution plan option.

All possible without a single index rebuild in sight …

However like most things Oracle, index compression also has the potential to be misused and to cause rather than solve problems. So it really helps to understand how index compression works and how it’s actually implemented before we rush into anything.

The first point to understand is that index compression is implemented at the index block level. Basically, Oracle stores each distinct combination of compressed column values found within a specific index leaf block in a “Prefix” table within the leaf block and assigns each combination a unique prefix number.

The more numbers of distinct compressed column values, the more entries in the prefix table and the larger the prefixed related data. The fewer numbers of distinct compressed column values, the fewer entries in the prefix table and the smaller the prefix related data. Generally, the fewer entries in the prefix table, the better the compression.

Oracle now no longer stores these compressed columns within the actual index row entries. These compressed columns are substituted and referenced with the details stored in the prefix table entry, which are shared by all index row entries with the same corresponding prefix value.

Only leading columns (which in a Non-Unique Index can potentially be all the columns in an index, in a Unique Index can potentially be all but the last column in the index) can be compressed. Therefore, the prefix table is in the same logical order as they would appear in the index row entries. The values of the prefix values always appear within the index row entries in a sequential manner, noting that (hopefully) several row entries share the same prefix number.

Let’s say we currently have a nocompress Non-Unique index on a NAME column with the following entries:

0: David Bowie

  : ROWID

1: David Bowie

  : ROWID

2: David Bowie

  : ROWID

3: David Bowie

  : ROWID

4: David Bowie

  : ROWID

5: David Jones

  : ROWID

6: David Jones

  : ROWID

7: David Jones

  : ROWID

After the index is compressed, the leaf block entries would look logically something like this:

Prefix

0: David Bowie

1: David Jones

—

0:0

  : ROWID

1: 0

  : ROWID

2: 0

  : ROWID

3: 0

  : ROWID

4: 0

  : ROWID

5: 1

  : ROWID

6: 1

  : ROWID

7: 1

  : ROWID

…

Importantly, each distinct combination of compressed column values is now stored just the once within an individual index leaf block. In the example above, we previously stored “David Bowie” 5 times. In the compressed index leaf block, we now only store “David Bowie” once, with the prefix value now being referenced by each index entry instead.

The net result being we can now (hopefully) store many more index entries per leaf block meaning we don’t need as many leaf blocks in our index.

To compress an index, simply specify the COMPRESS option:

CREATE INDEX bowie_table_i ON bowie_table(col1, col2) COMPRESS 2;

The number after the COMPRESS keyword denotes how many columns to compress. The default is all columns in a Non-Unique index and all columns except the last column in a Unique index.

This demo, Index Compression Part I shows how an appropriately compressed index can substantially reduce the overall size of an index. It also shows a couple of index leaf block dumps to highlight how index compression is implemented.

In Part II, I’ll show you how you can really stuff things up by implementing index compression totally inappropriately …