Index Prefix Compression in Oracle With Introduction of a New Advanced Index Compression Capability with Oracle 23c

Index Prefix Compression in Oracle With Introduction of a New Advanced Index Compression Capability with Oracle 23c

Alireza Kamrani 

  02/10/2025

Index Key Compression, also referred to as Index Prefix Compression, in a compression feature included with Oracle Database Enterprise Edition that has the potential to help reduce the overall size of indexes and helps both multi-column unique indexes and non-unique indexes alike. 

As a result, it is one of the most critical index optimization features available to DBAs for effectively managing the space used by the indexes.

Index Key Compression allows for compressing portions of the key values in an index segment (or Index Organized Table (IOT)), by reducing the storage inefficiencies of storing repeating values multiple times. 

It compresses the data by splitting the index key into two parts:

• Prefix Entries: the leading group of columns, which are potentially shared across multiple key values
• Suffix Entries: the suffix columns, which are unique to every index key.

As the prefixes are potentially shared across multiple keys in a block, these can be stored more optimally (only once per block) and shared across multiple suffix entries, resulting in the index data being compressed.

Index Key compression is performed in the leaf blocks of a B-Tree index. 

The keys are compressed locally within an index leaf block, meaning that both the prefix and suffix entries are stored within same block. 

Suffix entries make up the compressed representation of the index key. Each one of these compressed rows refers to the corresponding prefix, which is stored in the same block. 

By storing the prefixes and suffixes locally in the same block, each index block is self-contained and it is possible to construct the complete key without incurring any additional block IO. Re-constructing the key is a very inexpensive memory only operation. 

For new indexes and index partitions, enabling Index Key Compression is easy - simply CREATE the index or index partition and specify the index compression clause. 

See the example below:

CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS;

An existing index or index partition can be REBUILT compressed using the syntax shown below:

ALTER INDEX idxname REBUILD COMPRESS;

By default, the prefix consists of all indexed columns for non-unique indexes, and all indexed columns excluding the last one for unique indexes. 

Alternatively, it is possible to specify the prefix length as part of the index compression clause, which is the number of columns in the prefix entries:

CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS2;

The number, prefix column length, after the COMPRESS keyword denotes how many columns to compress. 

The maximum prefix length for a non-unique index is the number of columns in the index key, and for a unique index is the number of key columns minus one.

Prefix entries are written to the index block only if the index block does not already contain that prefix. 

They are available for sharing across multiple suffix entries immediately after being written and remain available until the last referencing suffix entry is deleted from the block. 

Although key compression reduces the storage requirements of an index by sharing parts of keys across multiple entries, there is a small CPU overhead to reconstruct the key column values during index lookup or scans, which is minimized by keeping the prefixes locally in the block.

Index Key Compression achieves a more optimal representation of an index, and ensures that it stays permanently compressed without any subsequent overhead on the maintenance operations. 

As a result, it has a positive impact on the storage and space savings, but also achieves secondary benefits such as better cache efficiency, fewer leaf blocks and less deep tree resulting in potentially fewer logical IOs and cheaper execution plans. 

In many cases the overhead to construct the complete user row is offset by more efficient representation of the block, ability to fit many more user rows in a given block, reduction in IO required to read the index rows and better buffer cache efficiency, such that the applications sees improvement in overall performance.

Index Key compression can be extremely useful in many different scenarios, a few of which are listed below:

• Index Key Compression can be used with a non-unique index where ROWID is appended to make the key unique. If such an index is compressed using key compression, the duplicate key is stored only once as a prefix entry in the index block without the ROWID. The remaining rows become suffix entries consisting of only the ROWID
• Index Key Compression can be used with a unique multicolumn index (key compression is not possible for unique single column index because there is a unique piece but there are no prefix grouping pieces to share)
• Index Key Compression can be used with Index Organized Tables. The same considerations as unique multicolumn indexes apply

The key to getting good index compression is identifying which indexes will benefit from it and correctly specifying the prefix column length for those indexes. This requires an understanding of the data in order to choose the most optimal prefix column count. 

If you want to estimate the ideal compression ratio and the percentage of leaf block space that could be saved, you need to look at INDEX_STATS view after ANALYZING the index, for example:

ANALYZE INDEX indexname VALIDATE structure; 

SELECT name, height, blocks, opt_cmpr_count, opt_cmpr_pctsave FROM  index_stats 
WHERE name = index name; 

“OPT_CMPR_COUNT” indicates the number of columns to compress in the index to get maximum space savings in the leaf blocks (prefix column length).

 “OPT_CMPR_PCTSAVE” indicates the percentage reduction in leaf block space used if index is compressed using this prefix length.

Compression can be very beneficial when the prefix columns of an index are repeated many times within a leaf block. 

However, if the leading columns are very selective, or if there are not many repeated values for the prefix columns, then index prefix compression may not be the best solution. 

In these scenarios, Oracle still creates prefix entries storing all unique combinations of compressed column values within a leaf block. The index rows will refer to the prefix entry, which are not shared (if at all) by other index rows. 

Thus, it is possible that compression in these cases is not beneficial, and could end up increasing the index size due to the overhead of storing all of the prefix entries.

For index compression to be beneficial, ensure that low cardinality columns are the leading columns in a concatenated index. 

Otherwise, there is a risk of getting negative compression such that leaf blocks can no longer store as many keys as their non-compressed counterparts. 

Additionally, there is no point in compressing a single column unique index or compressing every column in a concatenated, multi-column unique index. 

In these cases, compression will result in an index structure that increases in size rather than decreasing (negative compression) due to all the overhead associated with having prefix entries for every index row.

The key to getting good index compression is identifying which indexes will benefit from it and correctly specifying the prefix column length. 

The discussion above on how to figure out the optimal Prefix Column Length can help, but also please note the following:

• Implementing compression requires a deep understanding of the data in order to choose the most optimal prefix column count
• Specified prefix column count may not be optimal to produce the best compression ratio for every block in the index
• Requires running ANALYZE INDEX to obtain an optimal prefix column count, which produces the optimal count for the index as a whole. This is not at the granularity of a block, so it may not yield the best compression ratio. Additionally, running ANALYZE INDEX takes an exclusive lock on the table, effectively making the table “offline” for this period
• Possible to get negative compression, as pointed out earlier, such as in the case where the specified prefix columns are unique in a block

Application developers and DBAs need to be very selective on which indexes to compress and correctly set the prefix column count for these indexes. 

Oracle protects you under certain obvious conditions, but it is your responsibility to compress the indexes in the right manner.

For more information about Index Organized Tables (IOTs) and compression, as well as how to find the optimal index key compress level for indexes, please see these MOS notes:

Doc ID 1555637.1 – Index Organized Tables (IOTs) and Compression
Doc ID 601690.1 – How to find the optimal Index Key COMPRESS level for indexes

New Advanced Index Compression Capability with Oracle Database 23c

Advanced Index Compression, a feature of Advanced Compression, simplifies index compression. 

Advanced Index Compression enables the highest levels of data compression and provides enterprises with storage savings and query performance improvements due to reduced I/O. 

Advanced Index Compression is an enabling technology for multiple compression levels, LOW and HIGH. 

This discussion will focus on the LOW level of index compression. 

Advanced Index Compression LOW computes the prefix column count for compressed indexes. Rather than using a static prefix count for all index leaf blocks, it aims to compute an optimal prefix count for every index leaf block. 

The correct and most optimal numbers of prefix columns are calculated automatically on a block-by-block basis and thus produce the best compression ratio possible. 

It is possible to have different index leaf blocks compressed with different prefix column counts or not be compressed at all if there are no repeating prefixes.

So, what is new with Advanced Index Compression LOW with Oracle Database 23c? 

Advanced Index Compression LOW for Index-Organized Tables (IOTs)

An index-organized table is a table stored in a variation of a B-tree index structure. 

In contrast, a heap-organized table inserts rows where they fit.

In an index-organized table, rows are stored in an index defined on the primary key for the table. Each index entry in the B-tree also stores the non-key column values. 

Thus, the index is the data, and the data is the index. 

Applications manipulate index-organized tables just like heap-organized tables, using SQL statements.

IOTs are popular because they provide fast random access by primary key without duplicating primary key columns in two structures – a heap table and an index. 

Index-Organized Tables can now be compressed with Advanced Index Compression (LOW). 

Advanced Index Compression LOW can be enabled easily by specifying the COMPRESS option for indexes. 

For example:

SQL> create table tiot (c1 number, c2 number, c3 number, c4 number, primary key (c1, c2)) organization index compress advanced low;

In earlier releases, IOTs only supported Oracle’s prefix key compression for index compression. 

Usage of prefix key compression required user analysis and had the possibility of negative compression (where the overhead of compression outweighed the compression benefits). 

This new feature extends Advanced Index Compression (LOW) to IOTs, allowing users to enable compression for all IOTs without the possibility of negative compression and without any user analysis required. 

So that does this mean for your organization?

Average IOT storage reduction can range from 2x to 5x. 

Using 2x as an example, this means that the amount of space consumed by uncompressed data will be two times larger than that of the compressed data. 

By reducing their IOT storage requirements, IT managers can reduce, and sometimes eliminate their need to purchase new storage. 

The cost of decompressing a block compressed with Advanced Index Compression LOW is compensated by the fact that in most scenarios, the database would be scanning a smaller number of blocks. 

So, in general, IOT compression typically won’t compromise query performance (no significant degradation).

Use the Free Compression Advisor to see how well your indexes will compress

The “DBMS_COMPRESSION” PL/SQL package (commonly called compression advisor) is included with Oracle Database Enterprise Edition and gathers compression-related information within a database environment. 

The output of running compression advisor is an estimation of the compression ratio for the specific table or partition that was the target of compression advisor. 

Compression advisor provides organizations with the storage reduction information needed to make compression-related usage decisions.

Alireza Kamrani