Space used by Indexes

[DaPi]

Hi everyone, two questions

1) The Oracle doc says I can see how much of the space allocated to an index is actually used with:
SELECT pct_used FROM sys.index_stats WHERE . . .

ANALYZE . . . COMPUTE STATISTICS and
ANALYZE . . . VALIDATE STRUCTURE have been run, but the sys.index_stats view remains empty. How do I get it to be populated?

2) Does anyone have a reference on estimating storage for indexes?

Many thanks - DaPi

 

[karluk]

I'm afraid that this is normal behavior. Index_stats is volatile. It contains only information about the last index to be analyzed, overwriting previous results. In addition, information is not preserved between sessions so, if you log out and log back in, index_stats will be empty. That's what you are seeing, so I suspect that you are looking at it from a different session than the one which did the analysis.

 

[carp]

ANALYZE INDEX my_index VALIDATE STRUCTURE;
should populate index_stats.

Be advised that index_stats is unique to each session, so if you run the ANALYZE command, logout, then come back into a new session, the view will not be populated. Also, if another session runs the analyze command and you query index_stats, you will not see the results of the ANALYZE command.

 

[DaPi]

Thanks to both! Works like a charm.

 

[DaPi]

Just for the record, an index that could be rebuilt in less than 1% of the space (!!!!) intially returned pct_used as 99. Strange?

 

[DaPi]

Hello again,

I am having some trouble fully understanding index_stats (for standard B-tree).
- pct_used seems to be the ratio of used_space to btree_space (useful, but not what I was looking for - it explains my post of 21 Nov).
- index_stats contains lf_blks and br_blks - is that all of the space that is occupied? so will "blocks - (lf_blks + br_blks)" give free blocks?

Can anyone give me a reference on index physical structure to help with this? Searching on this forum & RTFMing have not helped.

TIA

 

[karluk]

Yes, blocks - (lf_blks + br_blks) yields the number of free blocks.

There is quite a bit of documentation on index_stats in Metalink. I would start there.

 

[DaPi]

Thanks karluk. (I should have looked in Meatalink first, that would have avoided starting this thread.)