下面来做一个实验,在一张表上建一个B*tree索引,查到索引的高度为2(BLEVEL+1,BLEVEL是叶子节点到根节点),此时逻辑读应该为3,先读取索引的根,然后是叶子节点,最后通过叶子节点的rowid找到表的记录,一共是3个逻辑读,但是实验做出来是4个逻辑读,是什么原因呢?
再次实验,用unique index,结果逻辑读是3,这样才是对的。
可以看出是unique index和普通的索引之间是有区别的,普通的索引读取逻辑读多1的原因是根据条件读取后,因为索引是顺序的,还要往下读一次,如果不符合条件则算完成。但unique index不一样,因为是独一无二的,读取完满足条件后不需要再读下一个记录。
SQL> create table test as select * from dba_objects;
表已创建。
SQL> create index ind_objec_id on test(object_id);
索引已创建。
SQL> select s.index_name,s.blevel from user_indexes s where s.index_name ='IND_OBJEC_ID';
INDEX_NAME BLEVEL
------------------------------ ----------
IND_OBJEC_ID 1
SQL> select * from test where object_id = 20;
执行计划
----------------------------------------------------------
Plan hash value: 189664166
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJEC_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1393 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> drop index ind_objec_id ;
索引已删除。
SQL> create unique index ind_objec_id on test(object_id);
索引已创建。
SQL> select * from test where object_id = 20;
执行计划
----------------------------------------------------------
Plan hash value: 2936784753
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 207 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | IND_OBJEC_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=20)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1297 bytes sent via SQL*Net to client
405 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed