Databases Reference
In-Depth Information
You use the
BITMAP
keyword to create a bitmap index. The next line of code creates a bitmap index on the
REGION
column of the
LOCATIONS
tsable:
SQL> create bitmap index locations_bmx1 on locations(region);
A bitmap index stores information about the
ROWID
of a row and a corresponding bitmap. You can think of the
bitmap as a combination of ones and zeros that point at multiple rows (this is quite different from a B-tree index, in
which one index entry points at one row). A 1 indicates the presence of a value, and a 0 indicates that the value doesn't
exist. Table
8-2
shows the resulting structure of the bitmap index.
Table 8-2.
Structure of the
LOCATIONS_BMX1
Bitmap Index
Value/Row
Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
EAST
0
1
0
0
1
0
0
NORTH
1
0
1
0
0
1
1
WEST
0
0
0
1
0
0
0
For each value of
REGION
(
EAST
,
NORTH
,
WEST
), an array of values is stored that indicates which rows contain a
value for a particular
REGION
. For instance, the
EAST
location has bit settings in row 2 and row 5 (meaning that the
EAST
location is present for those two rows).
Bitmap indexes are effective at retrieving rows when multiple
AND
and
OR
conditions appear in the
WHERE
clause.
For example, to perform the task
find all rows with a region of EAST or WEST
, a Boolean algebra
OR
operation
is performed on the
EAST
and
WEST
bitmaps to quickly return rows 2, 4, and 5. The last row of Table
8-3
shows the
OR
operation on the
EAST
and
WEST
bitmap.
Table 8-3.
Results of an
OR
Operation
Value/Row
Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
EAST
0
1
0
0
1
0
0
WEST
0
0
0
1
0
0
0
Boolean
OR
on
EAST
and
WEST
0
1
0
1
1
0
0
■
Bitmap indexes and bitmap join indexes are available only with the Oracle enterprise edition of the database.
Also, you can't create a unique bitmap index.
Note
Creating Bitmap Join Indexes
Bitmap join indexes store the results of a join between two tables in an index. Bitmap join indexes are beneficial
because they avoid joining tables to retrieve results. The syntax for a bitmap join index differs from that of a regular
bitmap index in that it contains
FROM
and
WHERE
clauses. Here is the basic syntax for creating a bitmap join index:
create bitmap index <index_name>
on <fact_table> (<dimension_table.dimension_column>)
from <fact_table>, <dimension_table>
where <fact_table>.<foreign_key_column> = <dimension_table>.<primary_key_column>;
