You are hereHoney, I shrunk the indexes

Honey, I shrunk the indexes


By IgnacioRuiz - Posted on 14 April 2008

Introduction

There is an Oracle feature that may provide savings in space and IO, have you heard of "index compression"? well, this feature is with us since 8i, but for many of, it's oscure 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 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 compression?
There are two ways to acomplish 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 comparisson yields less index blocks and leaf blocks (which is logical and obvious), accounting for 10.5% of space savings.

Let our immagination 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.

Wait for next part: How to pick the best candidates for compression


View my page on Oracle Community
View my blog on Blogger

Subscribe in a reader



Syndicate

Syndicate content

Follow DatabasesLA on Twitter

Who's online

There are currently 0 users and 3 guests online.

Estadisticas

Locations of visitors to this page

hidden hit counter