Database Reference
In-Depth Information
Table 11-6.
A Representation of How Oracle Would Store the
JOB-IDX
Bitmap Index
Value/Row
1
2
3
4
5
6
7
8
9
10
11
12
13
14
ANALYST
0
0
0
0
0
0
0
1
0
1
0
0
1
0
CLERK
1
0
0
0
0
0
0
0
0
0
1
1
0
1
MANAGER
0
0
0
1
0
1
1
0
0
0
0
0
0
0
PRESIDENT
0
0
0
0
0
0
0
0
1
0
0
0
0
0
SALESMAN
0
1
1
0
1
0
0
0
0
0
0
0
0
0
Table
11-6
shows that rows 8, 10, and 13 have the value
ANALYST
, whereas rows 4, 6, and 7 have the value
MANAGER
.
It also shows us that no rows are null (bitmap indexes store null entries; the lack of a null entry in the index implies
there are no null rows). If we wanted to count the rows that have the value
MANAGER
, the bitmap index would do this
very rapidly. If we wanted to find all the rows such that the
JOB
was
CLERK
or
MANAGER
, we could simply combine their
bitmaps from the index as, shown in Table
11-7
.
Table 11-7.
Representation of a Bitwise
OR
Value/Row
1
2
3
4
5
6
7
8
9
10
11
12
13
14
CLERK
1
0
0
0
0
0
0
0
0
0
1
1
0
1
MANAGER
0
0
0
1
0
1
1
0
0
0
0
0
0
0
CLERK
or
MANAGER
1
0
0
1
0
1
1
0
0
0
1
1
0
1
Table
11-7
rapidly shows us that rows 1, 4, 6, 7, 11, 12, and 14 satisfy our criteria. The bitmap Oracle stores with
each key value is set up so that each position represents a rowid in the underlying table, if we need to actually retrieve
the row for further processing. Queries such as the following
select count(*) from emp where job = 'CLERK' or job = 'MANAGER';
will be answered directly from the bitmap index. A query such as this
select * from emp where job = 'CLERK' or job = 'MANAGER';
on the other hand, will need to get to the table. Here, Oracle will apply a function to turn the fact that the
i
'th bit is on
in a bitmap, into a rowid that can be used to access the table.
When Should You Use a Bitmap Index?
Bitmap indexes are most appropriate on
low distinct cardinality
data (i.e., data with relatively few discrete values
when compared to the cardinality of the entire set). It is not really possible to put a value on this—in other words,
it is difficult to define what low distinct cardinality is truly. In a set of a couple thousand records, 2 would be low
distinct cardinality, but 2 would not be low distinct cardinality in a two-row table. In a table of tens or hundreds of
millions records, 100,000 could be low distinct cardinality. So, low distinct cardinality is relative to the size of the
