Re: Index Information used_space and dba_segments

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Mon, 29 Apr 2013 16:59:07 +0100
Message-ID: <aMCdnaODZqA9BePMnZ2dnUVZ7tudnZ2d_at_bt.com>



<basis_consultant_at_hotmail.com> wrote in message news:cd6f7a8c-bf39-4686-a896-3f1a172f43aa_at_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
Received on Mon Apr 29 2013 - 17:59:07 CEST

Original text of this message