BitMap indexes are more efficient than B-Tree indexes.

BitMap indexes are more efficient than B-Tree indexes.

1. Experiment description:

Operating System: rhel 5.4x86

Database: Oracle 11g R2

Ii. Procedure:

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

SQL> create table t_btree as select * from dba_objects;

Table created.

SQL> create index status_btree on t_btree (status );

Index created.

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

SQL> set autotrace traceonly;
SQL> select count (*) from t_btree where status = 'valid ';

Execution Plan
----------------------------------------------------------
Plan hash value: 2400455617

Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 1 | 5 | 49 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 5 |
| * 2 | index fast full scan | STATUS_BTREE | 74307 | 362K | 49 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------

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

2-filter ("STATUS" = 'valid ')

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

Statistics
----------------------------------------------------------
32 recursive cballs
0 db block gets
261 consistent gets
458 physical reads
0 redo size
424 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 count (*) from t_btree where status = 'valid ';

Execution Plan
----------------------------------------------------------
Plan hash value: 2400455617

Bytes --------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes --------------------------------------------------------------------------------------
| 0 | select statement | 1 | 5 | 49 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 5 |
| * 2 | index fast full scan | STATUS_BTREE | 74307 | 362K | 49 (0) | 00:00:01 |
Bytes --------------------------------------------------------------------------------------

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

2-filter ("STATUS" = 'valid ')

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

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
180 consistent gets
0 physical reads
0 redo size
424 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

Create a t_bmap table like the t_btree table and create a BitMap index.

SQL> create table t_bmap as select * from dba_objects;

Table created.

SQL> create bitmap index status_bmap on t_bmap (status );

Index created.

Run the preceding statement twice:

SQL> select count (*) from t_bmap where status = 'valid ';

Execution Plan
----------------------------------------------------------
Plan hash value: 516980546

Bytes ---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ---------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 5 | 3 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 5 |
| 2 | bitmap conversion count | 62928 | 307K | 3 (0) | 00:00:01 |
| * 3 | bitmap index fast full scan | STATUS_BMAP |
Bytes ---------------------------------------------------------------------------------------------

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

3-filter ("STATUS" = 'valid ')

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

Statistics
----------------------------------------------------------
32 recursive cballs
0 db block gets
72 consistent gets
266 physical reads
0 redo size
424 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 count (*) from t_bmap where status = 'valid ';

Execution Plan
----------------------------------------------------------
Plan hash value: 516980546

Bytes ---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes ---------------------------------------------------------------------------------------------
| 0 | select statement | 1 | 5 | 3 (0) | 00:00:01 |
| 1 | sort aggregate | 1 | 5 |
| 2 | bitmap conversion count | 62928 | 307K | 3 (0) | 00:00:01 |
| * 3 | bitmap index fast full scan | STATUS_BMAP |
Bytes ---------------------------------------------------------------------------------------------

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

3-filter ("STATUS" = 'valid ')

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

Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
6 consistent gets
0 physical reads
0 redo size
424 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:

From consistent reading, the consistent gets of the B-Tree index is 180, and the BitMap is 6;

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

When the index key is a high repetition rate key value (status), BitMap indexes are more efficient than B-Tree indexes.

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