B-Tree索引性能优于BitMap索引实例(二)

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_ID"=9899)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
312 recursive calls
0 db block gets
98 consistent gets
266 physical reads
0 redo size
1404 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select * from t_bmap where object_id=9899;

Execution Plan
----------------------------------------------------------
Plan hash value: 3763176822

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 207 | 110 (0)| 00:00:02 |
| 1 | TABLE ACCESS BY INDEX ROWID | T_BMAP | 1 | 207 | 110 (0)| 00:00:02 |
| 2 | BITMAP CONVERSION TO ROWIDS| | | | | |
|* 3 | BITMAP INDEX SINGLE VALUE | IND_MAP | | | | |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("OBJECT_ID"=9899)

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
68 consistent gets
0 physical reads
0 redo size
1404 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

从上面的查询中，我们可以得到，分别给两张内容一样的表做查询的时候，在执行第二次的时候是属于软解析：

从一致性读上比较，B-Tree索引的consistent gets是4，BitMap的是68；

从Cost的消耗上看，B-Tree索引的COST是2，而BitMap的是110。

在索引键是主键或者唯一性约束的情况下B-Tree索引的效率要优于BitMap索引。