RE: Index - Blevel

From: Paul Harrison <>
Date: Tue, 24 Feb 2015 11:13:07 -0600
Message-ID: <004401d05055$2989bfc0$7c9d3f40$>

Thank you  

That makes sense now as the autotrace stats display only 1 consistent get as there is only one block(root block IS the leaf block) in the index as you indicated.  

select last_name from test5 where last_name = 'rick';    


          0 recursive calls

          0 db block gets

          1 consistent gets

          0 physical reads

          0 redo size

        333 bytes sent via SQL*Net to client

        513 bytes received via SQL*Net from client

          1 SQL*Net roundtrips to/from client

          0 sorts (memory)

          0 sorts (disk)

0 rows processed  



From: [] On Behalf Of Jonathan Lewis
Sent: Tuesday, February 24, 2015 10:45 AM To:
Subject: RE: Index - Blevel    

No, it means the root block IS the leaf block.  

There's only one block in the index; add a few more (indexable) rows to the table and the block will split, and become a root block with two leaf blocks below it.        

Jonathan Lewis

From: [] on behalf of Paul Harrison [] Sent: 24 February 2015 16:02
Subject: Index - Blevel

Hi ALL,  

BLEVEL has a value of 0 and LEAF_BLOCKS has a value of 1. Does this mean the index consists of the root block and 1 leaf block? The blevel doesn't include the root block in its value?    

SQL> select index_name,blevel,leaf_blocks   2 from dba_indexes
  3 where owner=user
  4 and index_name like '%TEST5_LAST_NAME_IDX%'   5 /    

INDEX_NAME                         BLEVEL LEAF_BLOCKS
------------------------------ ---------- -----------
TEST5_LAST_NAME_IDX                     0           1


Received on Tue Feb 24 2015 - 18:13:07 CET

Original text of this message