B-Tree indexes are superior to BitMap indexes.

B-Tree indexes are superior to BitMap indexes.

1. Experiment description:

Operating System: rhel 5.4x86

Database: Oracle 11g R2

Lab Description: This experiment aims to demonstrate that the B-Tree index performance is superior to the BitMap index.

Introduction to Oracle B-Tree indexes (B-Tree indexes)

Analysis of Oracle B-Tree index search principles

Comparison between B-Tree and Bitmap indexes of Oracle Indexes

Principle Exploration from the balance Tree to the oracle B-Tree Index

Ii. Experiment operations:

Create a t_btree table and create a B-Tree index. The index key is object_id:

SQL> create table t_btree as select * from dba_objects;

Table created.

SQL> create index ind_tree on t_btree (object_id );

Index created.

Run the following query statement twice and display the execution plan:

SQL> set autotrace traceonly;
SQL> select * from t_btree where object_id = 9899;

Execution Plan
----------------------------------------------------------
Plan hash value: 447474086

Bytes ----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------
| 0 | select statement | 1 | 207 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | T_BTREE | 1 | 207 | 2 (0) | 00:00:01 |
| * 2 | index range scan | IND_TREE | 1 | 1 (0) | 00:00:01 |
Bytes ----------------------------------------------------------------------------------------

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

2-access ("OBJECT_ID" = 9899)

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

Statistics
----------------------------------------------------------
312 recursive cballs
0 db block gets
108 consistent gets
289 physical reads
0 redo size
1404 bytes sent via SQL * Net to client
419 bytes encoded ed 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_btree where object_id = 9899;

Execution Plan
----------------------------------------------------------
Plan hash value: 447474086

Bytes ----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------
| 0 | select statement | 1 | 207 | 2 (0) | 00:00:01 |
| 1 | table access by index rowid | T_BTREE | 1 | 207 | 2 (0) | 00:00:01 |
| * 2 | index range scan | IND_TREE | 1 | 1 (0) | 00:00:01 |
Bytes ----------------------------------------------------------------------------------------

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

2-access ("OBJECT_ID" = 9899)

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

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

Run the preceding statement twice:

SQL> select * from t_bmap where object_id = 9899;

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

Bytes ----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------
| 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 |
Bytes ----------------------------------------------------------------------------------------

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

3-access ("OBJECT_ID" = 9899)

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

Statistics
----------------------------------------------------------
312 recursive cballs
0 db block gets
98 consistent gets
266 physical reads
0 redo size
1404 bytes sent via SQL * Net to client
419 bytes encoded ed 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

Bytes ----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ----------------------------------------------------------------------------------------
| 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 |
Bytes ----------------------------------------------------------------------------------------

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

3-access ("OBJECT_ID" = 9899)

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

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

From the preceding query, we can see that when we perform queries for two tables with the same content, the second query is soft parsing:

In terms of consistent reading, the consistent gets of the B-Tree index is 4, and the BitMap is 68;

From the perspective of Cost consumption, the COST of B-Tree indexes is 2, while that of BitMap is 110.

B-Tree indexes are more efficient than BitMap indexes when the index key is a primary key or a unique constraint.