X

Database Storage Optimization best practices, tips and tricks and guidance from Database Compression Product Management

  • August 28, 2020

Got Indexes? Want to Reduce Your Database Storage? Then You Need to Know About Advanced Index Compression!

Gregg Christman
Product Manager

Last month we discussed Prefix Compression, also referred to as Index Key Compression, an index compression feature that is included with Oracle Database Enterprise Edition.

For this Blog, we are going to discuss Advanced Index Compression, a feature of Advanced Compression that simplifies index compression and often can achieve higher compression ratios for indexes. Advanced Index Compression enables the highest levels of index compression and provides enterprises with tremendous cost-savings and performance improvements due to reduced I/O.

Advanced Index Compression is an enabling technology for multiple compression levels – LOW and HIGH. Average compression ratios can range from 2x to 5x+ depending on which compression level is implemented. With substantial storage savings from Advanced Index Compression, IT managers can drastically reduce and often eliminate their need to purchase new storage for several years. We will discuss each of the compression levels, in detail, as the next generation in index compression technology.

Advanced Index Compression LOW

Advanced Index Compression LOW simplifies index key compression. When compress is enabled for an index using Advanced Index Compression Low, the database determines the prefix column count within compressed indexes (no need to run Analyze Index as is needed for Prefix Compression). Additionally, rather than using a static prefix count for all index leaf block (as is done with Prefix Compression), it aims towards computing an optimal prefix count for every index leaf block in the index.

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. It is now possible to have different index leaf blocks compressed with different prefix column count or not be compressed at all, if there are no repeating prefixes.

Enabling Advanced Index Compression LOW

Advanced Index Compression LOW can be enabled easily by specifying the COMPRESS option for indexes. New indexes can be automatically created as compressed, or the existing indexes can be rebuilt compressed.

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

Note that there is no need to provide the number of columns in the prefix entries with Advanced Index Compression (compared to Prefix Compression) as this will be computed automatically for every leaf block.

Advanced Index Compression HIGH

Advanced Index Compression HIGH is geared towards dramatically improving index compression ratios. It introduces many additional compression techniques, which improves the compression ratios significantly while still providing efficient OLTP access.

With Advanced Index Compression HIGH, every index leaf block can contain compressed and uncompressed rows. The compressed index key entries are stored physically as Compression Units (a concept similar to Oracle Hybrid Columnar Compression), utilizing more complex compression algorithms on a potentially larger number of index keys to achieve higher levels of compression. While the recently inserted keys and modified keys are stored in the non-compressed region of the leaf block.

Advanced Index Compression uses an internal threshold, similar to that used by Advanced Row Compression, to trigger (re) compression of the leaf block. Recently inserted rows are buffered uncompressed in the block, which is then compressed as the block fullness approaches this threshold. This ensures that the cost of compression is amortized over multiple DML operations and that not every operation incurs compression overhead. With indexes, this internal threshold is geared towards avoiding index block splits and alleviating the need to allocate additional leaf blocks to the index structure.

Advanced Index Compression supports full concurrency and row level locking with compressed rows ensuring no deadlocks and complete application transparency.

Enabling Advanced Index Compression HIGH

Advanced Index Compression HIGH can be enabled easily by specifying the COMPRESS option for indexes. New indexes can be automatically created as compressed, or the existing indexes can be rebuilt compressed.

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

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

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

With Advanced Index Compression, it is now possible to simply enable compression for all your B-Tree indexes, and Oracle will automatically compress every index leaf block when beneficial, while taking care of computing the optimal prefix column length for every block. This makes index compression truly local at a block level, where both the compression prefix table as well as the decision on how to compress the leaf block is made locally for every block and aims towards achieving the most optimal compression ratio for the entire index segment, while still providing efficient access to the indexes.

Using Advanced Index Compression, along with other Oracle Advanced Compression features, enterprises can efficiently manage their increasing data requirements with minimal administrative intervention – minimizing database storage costs while continuing to achieve the highest levels of application performance.

For more information about Prefix Compression and Advanced Index Compression, please see this Oracle Technical Brief.

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.