Re: Index Information used_space and dba_segments

From: Jonathan Lewis <>
Date: Mon, 29 Apr 2013 16:59:07 +0100
Message-ID: <>

<> wrote in message
| Hi,
| 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).

Nothing wrong with using analyze index to find the optimum compression count - though you can probably work it out logically if you're very familiar with the data.
After the rebuild with compression, what was the space_used figure when you re-analyzed.

Could you give us the full before and after figures from index_stats. What do you have set for pctfree for the index ?

There were a couple of anomalies with a mismatch between dba_extents and dba_segments in some versions of Oracle - related in particular to parallel rebuilds I think - have you cross checked the two views ?



Jonathan Lewis

Author: Oracle Core (Apress 2011)
Received on Mon Apr 29 2013 - 17:59:07 CEST

Original text of this message