How to See the Height of an Index

This example shows where to find the height of an index. I tested it on Oracle 11.2. First I deleted the index's statistics:

SQL> exec dbms_stats.delete_index_stats -

> ('uimsmgr','ubbchst_serv_index');

 

PL/SQL procedure successfully completed.

 

SQL>

 

Then I checked its BLEVEL was null:

SQL> select nvl(blevel,999)

  2  from dba_indexes

  3  where owner = 'UIMSMGR'

  4  and index_name = 'UBBCHST_SERV_INDEX'

  5  /

 

NVL(BLEVEL,999)

---------------

            999

 

SQL>

 

I recalculated the index's statistics:

 

SQL> exec dbms_stats.gather_index_stats -

> ('uimsmgr','ubbchst_serv_index');

 

PL/SQL procedure successfully completed.

 

SQL>

 

After that, the BLEVEL column in DBA_INDEXES showed the index’s height:

SQL> select nvl(blevel,999)

  2  from dba_indexes

  3  where owner = 'UIMSMGR'

  4  and index_name = 'UBBCHST_SERV_INDEX'

  5  /

 

NVL(BLEVEL,999)

---------------

              3

 

SQL>

 

I used to calculate an index's height by analyzing it then looking in INDEX_STATS. First I checked that INDEX_STATS was empty (you will see why I showed the username later):

SQL> select name, height from index_stats

  2  /

 

no rows selected

 

SQL> show user

USER is "ORACLE"

SQL>

 

Then I analyzed the index:

SQL> analyze index uimsmgr.ubbchst_serv_index

  2  validate structure

  3  /

 

Index analyzed.

 

SQL>

 

... and checked its height in INDEX_STATS again. This height comes out as BLEVEL + 1. I guess Oracle starts to count it from a different place as this happened in Oracle 9 too:

 

SQL> select name, height from index_stats

  2  /

 

NAME                               HEIGHT

------------------------------ ----------

UBBCHST_SERV_INDEX                      4

 

SQL>

 

INDEX_STATS is a public synonym for the SYS.INDEX_STATS view. It is far too complicated for me to follow but I know that it never has more than one row, which holds details for the index you analyzed most recently. It is also session specific so, if you start a new session, it will be empty again:

 

SQL> conn / as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> select name, height from index_stats

  2  /

 

no rows selected

 

SQL>