Index Information used_space and dba_segments

[basis_co...@hotmail.com]

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).



Thanks,
QZ

[Jonathan Lewis]

<basis_co...@hotmail.com> wrote in message
news:cd6f7a8c-bf39-4686...@googlegroups.com...

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 ?

--

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com/all-postings

Author: Oracle Core (Apress 2011)
http://www.apress.com/9781430239543

[Mark D Powell]

On Monday, April 29, 2013 11:59:07 AM UTC-4, Jonathan Lewis wrote:
> <basis_co...@hotmail.com> wrote in message news:cd6f7a8c-bf39-4686...@googlegroups.com... | 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 ? -- Regards Jonathan Lewis http://jonathanlewis.wordpress.com/all-postings Author: Oracle Core (Apress 2011) http://www.apress.com/9781430239543

On our 10.2.0.5 environments the extent information in dba_segments is often different from what is shown via dba_extents which seems to be correct. I do not have a bug number since we have never bothered to report the issue to support, but I am pretty sure this was fixed by 11.2

What kind of tablespace storage allocation is in use: dictionary, uniform extents, or auto-allocate? What degree of parallelism was used on the index rebuild? Use of parallel rebuilds can cause an index allocation to grow well beyound what would be used via a non-parallel rebuild operation.

HTH -- Mark D Powell --

[ddf]

11.2.0.3 does show a match between dba_segments.extents and a count(*) from dba_extents grouped by owner and segment_name. So you are correct in that the issue is fixed in at least that release.


David Fitzjarrell