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:

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.