Oracle-MySQL Databases Dissected: Honey, I shrunk the indexes

Ver este articulo en Español

Introduction

There is an Oracle feature that may provide savings in space and IO, have you heard of "index key compression"? well, this feature is with us since 8i, but for many, it's obscure or unknown.

Despite the 10g storage management optimizations, always there is gain from index maintenance. If you do index checks regularly you're a good DBA... but if you don't, better take care from now on.

Adding the gains from index rebuild or shrink, you may consider compressing some well-picked indexes, for which the space savings and IO reductions overcome the slight(?) cpu overhead it causes. I wrote a question mark after 'slight' because we will try to estimate that cost in the short term.

I'll propose this starting questions:
* How do you use index key compression?
* What are the first sight results?
* How to pick the best candidates for compression?
* Index compression is good or evil... or both?
* What is the benefit/cost after the shrinking?
* What are the "inside" results or how to analyze the effect on my present queries?

If you have more questions, please feel free to drop a comment and we (all of you and I, because as far as I know I don't have multiple personality disorder) will try to tackle and provide a satisfactory answer.

How do you use index key compression?

There are two ways to accomplish this:
1) drop the index, create it again with COMPRESS
2) rebuild the index with COMPRESS

I will try the second method, with this huge index I've on a test database. These are the starting figures:


TABLE_ROWS   TABLE_BLOCKS   INDEX_BLOCKS   INDEX_BYTES    BLEVEL  LEAF_BLOCKS
----------   ------------   ------------  -------------   ------  -----------
7,331,706        459,210        155,648  1,275,068,416        3      149,394

Now that we have our baseline, it's time to issue the DDL sentence that will reorganize the index:


SQL> ALTER INDEX idx_big_comp_test REBUILD COMPRESS 2;
Index Rebuild

After that statement our figures are now the following:

TABLE_ROWS   TABLE_BLOCKS   INDEX_BLOCKS   INDEX_BYTES    BLEVEL  LEAF_BLOCKS
----------   ------------   ------------  -------------   ------  -----------
7,331,706        459,210        139,904   1,146,093,568        3     133,682

A quick comparison yields less index blocks and leaf blocks (which is logical and obvious), accounting for 10.5% of space savings.

Let our imagination fly, we're showing our boss the way to extend the out-of-disk due date or justifying a well earned salary rise derived from storage savings. Back to reality... in this life everything has a price, don't rush and compress every index in your databases until we talk about pros and cons, and learn how to choose good candidates for compression.

Jump to Part II Honey, I shrunk the indexes - Part 2: How to pick the perfect candidates