The Dutch Prutser's Blog

By: Harald van Breederode

  • Disclaimer

    The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.
  • Subscribe

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 309 other followers

A few words on Index Compression

Posted by Harald van Breederode on April 5, 2010

For the last couple of months I have been quite busy delivering Oracle11g New Features courses and one of the topics covered is table compression. To introduce this topic I ask my students in which version the first compression feature was introduced. Strangely enough almost no one seems to know the correct answer, which is Oracle8i where index compression was introduced. Because table compression is very much like index compression I explain to my students what index compression is all about and quite often they are completely shocked by the fact that such an important feature is missing in their skill set. So it is about time that I post something about index compression.

Index compression works by eliminating duplicated column values in the index leaf blocks thereby allowing the storage of more index entries per leaf block. To demonstrate this feature I will start by creating a non-compressed index on a few columns on the CUSTOMERS table in the SH sample schema:

SQL> connect sh/sh
SQL> create index i1 on customers(cust_first_name,cust_last_name);

Index created.

SQL> select leaf_blocks from user_indexes where index_name = 'I1';


As shown above the just created index consists of a total of 193 leaf blocks. To get an estimate about the percentage of leaf blocks that can be saved, by compressing the index, and the optimal compression factor we can use the information Oracle stores in the INDEX_STATS view after validating an index as shown below:

SQL> validate index i1;

Index analyzed.

SQL> select opt_cmpr_count,opt_cmpr_pctsave from index_stats;

-------------- ----------------
             2               47

The above output reveals that the optimal compression factor for this index is 2 and that this will most likely reduce the number of leaf blocks by 47%. So, let’s rebuild the index with compression enabled and verify the given estimate:

SQL> alter index i1 rebuild compress 2;

Index altered.

SQL> select leaf_blocks from user_indexes where index_name = 'I1';


The above shows that the number of leaf blocks in the index is now 101. We started with 193 leaf blocks and we were expecting a 47% reduction in the number of leaf blocks. 47% of 193 is 91 and 193 minus 91 is equal to 102. So Oracle estimated the reduction pretty good I would say ;-) Executing the validate index command again assures that 2 is indeed the optimal compression factor and no leaf blocks can be eliminated because the index is already compressed as shown below:

SQL> validate index i1;

Index analyzed.

SQL> select OPT_CMPR_COUNT,OPT_CMPR_PCTSAVE from index_stats;

-------------- ----------------
             2                0

Hopefully this little post is enough to trigger you to dive into the world of compressed indexes! For a much more thorough discussion on index compression see Richard Foote’s fabulous Oracle blog. (part 1, part 2, part 3 And part 4).


10 Responses to “A few words on Index Compression”

  1. Noons said

    Actually index compression started way back in release 4. But it was a different kind of compression, it did not adress duplicate elimination.

  2. […] This post was mentioned on Twitter by Oracle University. Oracle University said: A few words on Index Compression by #Oracle's star instructor Harald van Breederode […]

  3. maclean said

    It’s cool that validating index can provide you a compress opt advise.
    I’d like what can provide another advise for table compression !

  4. Harald van Breederode said


    There is a DBMS_COMPRESSION supplied PL/SQL package in Oracle 11gR2 which can help you in making compression related decisions.
    Quoting the docs:
    “The DBMS_COMPRESSION package gathers compression-related information within a database environment. This includes tools for estimating compressibility of a table for both partitioned and non-partitioned tables, and gathering row-level compression information on previously compressed tables. This gives the user with adequate information to make compression-related decision.”

    Hope this helps,

  5. maclean said

    11g introduces some new technology in dbms_compression, and provide more compression type : COMP_FOR_QUERY_HIGH,COMP_FOR_QUERY_LOW,COMP_FOR_ARCHIVE_HIGH,COMP_FOR_ARCHIVE_LOW

  6. Wierd said

    Hi Harald,

    Nice piece of information but what happens with inserts and updates. Is the compression only applied for those rows indexed at the time the compression was implemented or will the new or updated columns directly be compressed?

  7. Harald van Breederode said

    Hi Wierd,

    I am pretty sure that the compression is maintained during DML but I haven’t verified this.

  8. Charles said

    Hello Harald,

    When I read this I was really wondering about the ‘validate index’ command but it proved to be another example of your long experience.
    The ‘validate index’ command is not shown in the manuals for many years now. In fact the last time it seemed mentioned was in the Oracle7 Server SQL Reference Manual where it was mentioned deprecated. Please refer to
    Quoting from that:
    “Validating indexes is now also performed by the new ANALYZE command. Oracle Corporation recommends that you use the ANALYZE command rather than the VALIDATE INDEX command. The VALIDATE INDEX command may not be supported in future versions of Oracle. For information on the VALIDATE INDEX command, see the SQL Language Reference Manual for Oracle Version 6.”


  9. […] a.… 0.000000 0.000000 Rate this:Like this:LikeBe the first to like this. […]

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: