How Much Space an Index is Using

In order to gather the necessary index statistics, you need to 'validate
the structure' of the index. This is achieved using the following command:

analyze index validate structure;

Analyze index validate structure checks the structure of the index and
populates a table called index_stats. The command (in this form) does not
gather statistics for use by the Cost Based Optimizer. The index_stats table
can only hold 1 row at a time. So, if you are planning to store index data for
multiple indexes or for historical comparison purposes, you will need to
insert the data into a more permanent table. Statistics are also lost at the
end of each session.

This analyze command only checks the structure of the index and populates the
index_stats table. It does not gather statistics for use by the Cost Based
Optimizer.

Once you have gathered the statistics, they can be retrieved as follows:

column name format a15
column blocks heading "ALLOCATED|BLOCKS"
column lf_blks heading "LEAF|BLOCKS"
column br_blks heading "BRANCH|BLOCKS"
column Empty heading "UNUSED|BLOCKS"
select name,
blocks,
lf_blks,
br_blks,
blocks-(lf_blks+br_blks) empty
from index_stats;

Example output
==============

SQL> analyze index draw1 validate structure;

Index analyzed.

SQL> select name, blocks, lf_blks, br_blks, blocks-(lf_blks+br_blks) empty
2 from index_stats;

ALLOCATED LEAF BRANCH UNUSED
NAME BLOCKS BLOCKS BLOCKS BLOCKS
--------------- ---------- ---------- ---------- ----------
DRAW1 433 426 6 1

It is also possible to gather statistics on the use of space within the
btree itself:

select name,
btree_space,
used_space,
pct_used
from index_stats;

Example output
==============

SQL> select name, btree_space, used_space, pct_used
2 from index_stats;

NAME BTREE_SPACE USED_SPACE PCT_USED
--------------- ----------- ---------- ----------
DRAW1 810624 236284 30