RE: Index - Blevel

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';    

Statistics

          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  

Thanks,

Paul  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis
Sent: Tuesday, February 24, 2015 10:45 AM To: oracle-l_at_freelists.org
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.        

Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
_at_jloracle

From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Paul Harrison [cure_at_austin.rr.com] Sent: 24 February 2015 16:02
To: oracle-l_at_freelists.org
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
 
 
 

Thanks,
Paul

--
http://www.freelists.org/webpage/oracle-l