Re: Exactly what are "leaf_blocks"??? Script included.

Hi Steve,
Indexes are in the form of a B+ tree. The leaf blocks are the lowest level of the tree, so the blocks containing the actual keys + rowids in the table.
Your query is incorrect, regrettably. To my best knowledge there is only one method to check this out:
analyze index < index name> validate structure followed by
select * from index_stats.
This table will show you the actual size of the B-tree, and how much is really in use. NB: deleted rows are NOT reclaimed.

Hth

Sybrand Bakker, Oracle DBA
(postbus_at_sybrandb.demon.nl)

Steve Orr wrote:

> The DBA_INDEXES view has a column called "leaf_blocks." What EXACTLY is
> that? Is that the number database blocks occupied by index data? If so
> does the following query give the number of unused blocks left in the
> current storage allocation for the index? (Assuming compute statistics
> has been performed.)
>
> select dba_indexes.index_name,
> dba_segments.blocks - dba_indexes.leaf_blocks -1
> from dba_segments, dba_indexes
> where dba_indexes.index_name = dba_segments.segment_name
> and dba_indexes.owner = dba_segments.owner
> and dba_segments.segment_type='INDEX'
> and dba_indexes.index_name = <whatever>;
>
> ----------------------------------
> Any ideas??? Thanks in advance...
Received on Fri Aug 07 1998 - 13:39:51 CDT