Databases Reference
In-Depth Information
Within a star schema, the base elements for the bitmap join index are the large fact table and the
dimension table. In a normal star schema, there are foreign keys on the child fact table back to the
parent dimension table. The join piece of the bitmap join index is an equi-inner join between these
foreign key columns between the fact and dimension tables.
To illustrate the situation further, materialized views are also popular in the realm of the data
warehouse. They are typically created to store the results of a join or aggregation for easy and quick
repeated access by the user. Bitmap join indexes are, in essence, a materialized join of indexed columns
between two tables, processed and stored once. They can be accessed over and over by the user without
having to reprocess a given join condition.
Let's say you create an index between your sample billing fact table and the geography dimension.
The join column between these two tables would be the primary key for the geography dimension
(
GEO_ID
) and the associated foreign key column on the billing fact table (also called
GEO_ID
).
To illustrate an example of a bitmap join index, refer to Tables 3-4 and 3-5. In Table 3-4, a sample of
rows and values is shown for both the
BILLING_FACT
and
GEOGRAPHY_DIMENSION
tables. In Table 3-5, it
shows, based on the sample, the actual makeup of a bitmap join index based on their relationship based
on the
GEO_ID
column. Table 3-5 shows an example of how the actual bitmap join index would be stored
in the database. There are four unique
ROWID
values shown for the
BILLING_FACT
table and two associated
ROWID
values for the matching row on the
GEOGRAPHY_DIMENSION
table. If your query joins the
BILLING_FACT
and
GEOGRAPHY_DIMENSION
tables and is searching for the
GEO_ID
of 24010 by scanning the
bitmap index, the
ROWID
values for each table are stored together and the data can quickly be retrieved
from the two tables.
Table 3-4. Foreign Key Relationship Between Fact and Dimension Table
Billing Fact
Row
Prod_ID
Geo_ID
Bill_date_id
YYMMDD_DDDATE
Bill_Unit
Bill_Amt
1
107
10
11011
2011-06-28
US
124.47
2
108
14
10037
2011-06-24
US
252.14
3
109
14
12001
02011-07-01
US
83.16
4
110
12
11021
2011-06-28
US
99.45
5
111
11
10147
2011-06-26
US
157.15
