Using Bitmap Join Indexes in Data Warehouses

In addition to a bitmap index on a single table, you can create a bitmap join index,
which is a bitmap index for the join of two or more tables. In a bitmap join index, the bitmap for the table to be indexed is built for values coming from the joined tables. In a data warehousing environment, the join condition is an equi-inner join between the primary key column or columns of the dimension tables and the foreign key column or columns in the fact table.
A bitmap join index can improve the performance by an order of magnitude. By
storing the result of a join, the join can be avoided completely for SQL statements
using a bitmap join index. Furthermore, since it is most likely to have a much smaller number of distinct values for a bitmap join index compared to a regular bitmap index on the join column, the bitmaps compress better, yielding to less space consumption than a regular bitmap index on the join column.
Bitmap join indexes are much more efficient in storage than materialized join views,
an alternative for materializing joins in advance. This is because the materialized join views do not compress the rowids of the fact tables.
Four Join Models for Bitmap Join Indexes
The most common usage of a bitmap join index is in star model environments, where a
large table is indexed on columns joined by one or several smaller tables. We will refer
to the large table as the fact table and to the smaller tables as dimension tables. The
following section describes the four different join models supported by bitmap join
indexes. See Chapter 19, "Schema Modeling Techniques" for schema modeling
techniques.
Example 6–3 Bitmap Join Index: One Dimension Table Columns Joins One Fact Table
Unlike the example in "Bitmap Index" on page 6-2, where a bitmap index on the
cust_gender column on the customers table was built, we now create a bitmap
join index on the fact table sales for the joined column customers(cust_gender).
Table sales stores cust_id values only:
SELECT time_id, cust_id, amount_sold FROM sales;
TIME_ID CUST_ID AMOUNT_SOLD
--------- ---------- -----------
01-JAN-98 29700 2291
01-JAN-98 3380 114
01-JAN-98 67830 553
01-JAN-98 179330 0
01-JAN-98 127520 195
01-JAN-98 33030 280
...
To create such a bitmap join index, column customers(cust_gender) has to be
joined with table sales. The join condition is specified as part of the CREATE
statement for the bitmap join index as follows:

ON sales(customers.cust_gender)
FROM sales, customers
WHERE sales.cust_id = customers.cust_id
LOCAL NOLOGGING COMPUTE STATISTICS;
The following query shows illustrates the join result that is used to create the bitmaps
that are stored in the bitmap join index:
SELECT sales.time_id, customers.cust_gender, sales.amount_sold
FROM sales, customers
WHERE sales.cust_id = customers.cust_id;
TIME_ID C AMOUNT_SOLD
--------- - -----------
01-JAN-98 M 2291
01-JAN-98 F 114
01-JAN-98 M 553
01-JAN-98 M 0
01-JAN-98 M 195
01-JAN-98 M 280
01-JAN-98 M 32
...

Following table illustrates the bitmap representation for the bitmap join index in this
example.

CREATE BITMAP INDEX sales_cust_gender_bjix



You can create other bitmap join indexes using more than one column or more than
one table, as shown in these examples.
Example 6–4 Bitmap Join Index: Multiple Dimension Columns Join One Fact Table
You can create a bitmap join index on more than one column from a single dimension
table, as in the following example, which uses customers(cust_gender, cust_
marital_status) from the sh schema:
CREATE BITMAP INDEX sales_cust_gender_ms_bjix
ON sales(customers.cust_gender, customers.cust_marital_status)
FROM sales, customers
WHERE sales.cust_id = customers.cust_id
LOCAL NOLOGGING COMPUTE STATISTICS;
Example 6–5 Bitmap Join Index: Multiple Dimension Tables Join One Fact Table
You can create a bitmap join index on multiple dimension tables, as in the following,
which uses customers(gender) and products(category):

CREATE BITMAP INDEX sales_c_gender_p_cat_bjix
ON sales(customers.cust_gender, products.prod_category)
FROM sales, customers, products
WHERE sales.cust_id = customers.cust_id
AND sales.prod_id = products.prod_id
LOCAL NOLOGGING COMPUTE STATISTICS;

Example 6–6 Bitmap Join Index: Snowflake Schema
You can create a bitmap join index on more than one table, in which the indexed
column is joined to the indexed table by using another table. For example, you can
build an index on countries.country_name, even though the countries table is
not joined directly to the sales table. Instead, the countries table is joined to the
customers table, which is joined to the sales table. This type of schema is
commonly called a snowflake schema.

CREATE BITMAP INDEX sales_co_country_name_bjix
ON sales(countries.country_name)
FROM sales, customers, countries
WHERE sales.cust_id = customers.cust_id
AND customers.country_id = countries.country_id
LOCAL NOLOGGING COMPUTE STATISTICS;

Bitmap Join Index Restrictions and Requirements
Join results must be stored, therefore, bitmap join indexes have the following
restrictions:
■ Parallel DML is only supported on the fact table. Parallel DML on one of the
participating dimension tables will mark the index as unusable.
■ Only one table can be updated concurrently by different transactions when using
the bitmap join index.
■ No table can appear twice in the join.
■ You cannot create a bitmap join index on an index-organized table or a temporary
table.
■ The columns in the index must all be columns of the dimension tables.
■ The dimension table join columns must be either primary key columns or have
unique constraints.
■ The dimension table column(s) participating the join with the fact table must be
either the primary key column(s) or with the unique constraint.
■ If a dimension table has composite primary key, each column in the primary key
must be part of the join.
■ The restrictions for creating a regular bitmap index also apply to a bitmap join
index. For example, you cannot create a bitmap index with the UNIQUE attribute.

Posted in: Index