Thursday, April 10, 2008

Honey, I shrunk the indexes

Ver este articulo en Español


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:

---------- ------------ ------------ ------------- ------ -----------
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:

Index Rebuild

After that statement our figures are now the following:

---------- ------------ ------------ ------------- ------ -----------
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

Add to Technorati Favorites

Ver este articulo en Español/Look for this content in spanish

Subscribe to Oracle Database Disected by Email

No comments:

Custom Search