Tuesday, October 21, 2014

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>
 
You can read more about this starting on page 69 of the book advertised below:


No comments: