1. 在一个大表上创建唯一索引

scott@TEST>create unique index idx_test on test(empno) nologging;
 
Index created.

2.分析该索引

Elapsed: 00:00:58.58
scott@TEST>exec dbms_stats.gather_index_stats(user,'IDX_TEST');
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:42.44
scott@TEST>select index_name,blevel,num_rows from user_indexes
  2  where index_name='IDX_TEST';
 
INDEX_NAME                         BLEVEL   NUM_ROWS
------------------------------ ---------- ----------
IDX_TEST                                2    7390971
 
Elapsed: 00:00:00.73

可见，该索引的blevel为2，

为了查看height值，需要进行analyze分析。

scott@TEST>analyze index idx_test validate structure;
 
Index analyzed.

scott@TEST>select height,blocks,name,lf_rows,br_rows,used_space,btree_space from index_stats;

    HEIGHT     BLOCKS NAME          LF_ROWS    BR_ROWS USED_SPACE BTREE_SPACE
---------- ---------- ---------- ---------- ---------- ---------- -----------
         3      16384 IDX_TEST      7340032      16207  116548505   129799868

3. 执行查询，查看io情况。
 
scott@TEST>set autot on
scott@TEST>select empno from test where empno=100;
 
     EMPNO
----------
       100
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   INDEX (UNIQUE SCAN) OF 'IDX_TEST' (UNIQUE)
 
 
 
 
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        376  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
     这种情况下，查询只访问索引，没有扫描表，该索引beleve值为2，height值为3，要找到叶子节点需要2个io，加上访问叶子节点本身的1个io，共需3个io。

    可见，在这种情况下，B*tree索引是多么的有效。