Databases Reference
In-Depth Information
Figure 10-3
illustrates the use of a bitmap index in a compound WHERE clause. Bitmap
indexes can be used together for even faster performance. The bitmap indexes are es‐
sentially stacked together, as a set of punch cards might be. Oracle simply looks for those
parts of the stack with all the bits turned on (indicating the presence of the value), in
the same way that you could try to stick a knitting needle through the portions of the
card stack that were punched out on all of the cards.
Figure 10-3. Bitmap index operation in a compound WHERE clause
In Oracle, star-query performance is improved when bitmap indexes are created on the
foreign-keys columns of the fact table that link to the surrounding dimension tables. A
parallel bitmap star join occurs in which the bitmaps retrieve only the necessary rows
from the fact table and the rows are joined to the dimension tables. During the join,
sparseness (i.e., a large quantity of empty values) is recognized inherently in the bitmaps,
and the number of dimension tables isn't a problem. This algorithm can also efficiently
handle a
snowflake schema
, which is an extension of a standard star schema in which
there are multiple tables for each dimension.
To further speed queries, Oracle9
i
added a bitmap join index from fact tables to di‐
mension tables. A bitmap join index is simply the bitmap index of a join of two or more
tables. The speedup in performance comes from avoiding actual table joins or reducing
the amount of data joined by taking into account restrictions in advance of the joining
