Index Information used_space and dba_segments

In Oracle 11g, I analyzed an index and checked index_stats.

dba_segments indicates that the size of an index was about 420MB.

used_space in index_stats indicated that the index used about 30MB, and that a compression of 2 columns would save about 15%.

I then compressed the index (Rebuild..compress 2), expecting that the size of the index in dba_segments to decrease. However, it did not (It was about 430MB after the compression).

Leaving aside the issue of compressing the index..I thought that the fact that used_space was much lower than the index's space in dba_segments indicated that the index was likely sparse.

If so, I expected rebuilding this index to decrease its size in dba_segments. Why did it not?

Regarding the argument that analyzing an index is outdated...I found that index_stats is generally accurate in predicting the amount of space a compression will save (At least, for an estimation of a space savings of some 40%+ or more for a compression).

Thanks,
QZ Received on Mon Apr 29 2013 - 17:24:19 CEST