Indexes can consume a lot of database storage space.

Not shocked by this statement, there’s a good reason that you are not surprised.

Indexes are used extensively in OLTP and mixed workload environments, as they are capable of efficiently supporting a wide variety of access paths to the data stored in relational tables. Often, indexes take upward of 50% of the total database space and it is not uncommon to have many indexes on a single table – all of which will need storage space.

If you also believe it is important to store and manage these indexes as efficiently as possible, from both storage and efficient access perspectives, then read on.

Advanced Index Compression, a feature of Oracle Advanced Compression, is an enabling technology for multiple index compression levels – LOW and HIGH. Typical compression ratios can range from up to 2x to 5x depending on which compression level is implemented. This means that the amount of space consumed by an uncompressed index will be two to five times larger than that of a compressed index.

Advanced Index Compression

Using Advanced Index Compression, you may be able to reduce or possibly eliminate the need to purchase or lease new storage. Choosing the level of Advanced Index compression, LOW or HIGH, will help ensure you are getting the optimal level of compression, based upon your current usage.

We will briefly discuss each of the index compression levels in the remainder of this blog.

Advanced Index Compression LOW

Advanced Index Compression LOW is ideal for applications with active data modification, such as OLTP.

When compression is enabled for an index using Advanced Index Compression LOW, the database determines the ideal prefix column count within compressed indexes, computing an optimal prefix count for every index leaf block in the index. Prefix compression can be very beneficial when the prefix columns of an index are repeated many times, within a leaf block.

The correct and most optimal numbers of prefix columns are computed automatically on a block-by-block basis, and thus produce the best compression ratio possible. This means it is now 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.

Compare this to Oracle’s Prefix Index Compression feature, included with Enterprise Edition, where users had to manually choose the prefix count. Advanced Index Compression has removed this burden from users, making index compression much simpler.

Example of creating index with index compression low enabled:

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

Advanced Index Compression HIGH

Advanced Index Compression HIGH is ideal for applications that are primarily read-mostly or read-only, such as reporting and analytical Data Warehouse applications.

Advanced Index Compression HIGH is geared towards dramatically improving index compression ratios. The decision on which compression algorithms, and there are numerous algorithms for the database to choose from, are applicable to an index is made real-time by the database and can differ from index-to-index and block-to-block.

Advanced Index Compression HIGH introduces many additional compression techniques, which improves the compression ratios significantly while still providing efficient access.

Example of creating index with index compression high enabled:

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

Limitations

There are some limitations of Advanced Index Compression, these include:

  • Advanced Index Compression is not supported for Bitmap Indexes
  • Advanced Index Compression HIGH is not supported for Index Organized Tables (IOTs)
  • Advanced Index Compression is not supported for Functional Indexes

Why Use Advanced Index Compression?

  • Optimal Compression for Your Indexes

             The decision on how best to compress the leaf block is made locally for individual blocks in order to achieve the optimal compression ratio for the entire index segment

  • Part of an Overall Compression Solution

             Using Advanced Index Compression, along with other Advanced Compression features, allows you to efficiently manage increasing database storage needs for your tables, indexes, LOBs and backups

Estimate your Index Compression Ratios

An easy way to get started, with Advanced Index Compression, is by using compression advisor.

The “DBMS_COMPRESSION” PL/SQL package (commonly called compression advisor) gathers compression-related information within a database environment. Compression advisor provides organizations with the storage reduction information needed to make compression-related usage decisions.

The output of running compression advisor is an estimation of the compression ratio for the specific index that was the target of compression advisor. The output indicates the “COMPRESSION RATIO” presented as a number such as 2.1. This number indicates that, for this specific index, the estimated compression ratio is 2.1x, which represents up to a 50% reduction in the footprint of the index, should compression be enabled.

Compression Advisor is included with Oracle Database Enterprise Edition.

More Information

  • See the Oracle Database Concepts documentation for more information about Advanced Index Compression
  • See the Oracle Index Compression Tech Brief here