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.1 person found this helpful
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)+
how do i an alter an index and change it to compression?
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.