This content has been marked as final. Show 4 replies
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.
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)+