4 Replies Latest reply on Nov 8, 2009 4:32 AM by damorgan

    Index compression

      I am going to implement AC, but when looking at my largest tables, I found that the number of indexes (15 sometimes) is causing the indexes to use 2x the amount of space as the tables.. SO along with AC, I'm going to implement index compression.

      The first index I chose is a single column index with "distinct rows" of 1. I figured this is where I'm going to get the biggest gain (a unique single column index would have no gain right?).

      After first rebuilding the index nocompress I got my baseline of the index . 409,600 Blocks (from dba sements and index stats).

      I then compressed the index and verified it was compressed by looking at dba_indexes (compressed=enabled). I can also see that the used space in index_stats is different when it is compressed (it is using less space)...

      The size of the index was 409,600 blocks.

      Why, when I am compressing an index with a distinct value of 1, am I get no gain in blocks from dba_segments ? The column is a number(20) and contains the same 7 digit number for all cases.

      This is on

      Edited by: Bryan Grenn on Sep 14, 2009 5:15 PM
        • 1. Re: Index compression
          Perhaps you didn't read the docs. <g> You can not compress a single column index.


          Note where the options are:
          1. You have a non-unique index where ROWID is appended to make the key unique.
          2. You have a unique multicolumn index.

          But based on your description of your table ... I wonder if you shouldn't be dropping indexes.
          1 person found this helpful
          • 2. Re: Index compression
            I have all the indexes to support RI.. they all are child columns in support of RI to a parent table. They are to prevent locking when updating the parent table. I really wish I could rid of them as they will never be used for lookups.. I don't know how else to handle the integrity issues and contention issues when updating the parent.

            For the same database I compressed another index on a 405 Million row table.. The original index took up 27G of space.. I rebuilt it uncompressed and it took 19G of space. I rebuilt it compressed it took up 12G of space.. There were 20,000 distinct values for the key. Compression on this single column index saved me 33% of my space.

            I also looked through the docs and the example was a single column index being compressed. so what happens with space when you "compress duplicate duplicate occurrences of a key in the index leaf block" ?

            +"You enable key compression using the COMPRESS clause. The prefix length (as the number of key columns) can also be specified to identify how the key columns are broken into a prefix and suffix entry. For example, the following statement compresses duplicate occurrences of a key in the index leaf block:+

            +CREATE INDEX emp_ename ON emp(ename)+
            +TABLESPACE users+
            +COMPRESS 1;"+
            • 3. Re: Index compression

              how do i an alter an index and change it to compression?

              • 4. Re: Index compression
                You can't.

                Having reread this thread my recommendation would be to drop indexes or fix the design of the table. 15 indexes, unless this is a data warehouse or decision support system is too many and indicative of a table with a high probability of having too many columns.