Bitmap Join Indexes

Bitmap indexes were introduced back in Oracle 8. Now with Oracle 9i, we can create bitmap join indexes. Bitmap join indexes represent the join of columns in two or more tables. With a bitmap join index, the value of a column in one table (generally a dimension table) is stored with the associated ROWIDs of the like value in the other tables that the index is defined on. This provides fast join access between the tables-if that query uses the columns of the bitmap join index.  In a data-warehouse environment, a bitmap join index might be a more efficient way of accessing data than a materialized view join. When using a bitmap join index in a warehouse or EIS environment, you would create the join using an equi-inner join between the primary key column(s) of the dimension tables and the foreign key column(s) of the fact table.
You create a bitmap join index using the CREATE BITMAP INDEX command. Here is an example of the creation of a bitmap index:

CREATE BITMAP INDEX my_bitmap_index
ON fact_table(dimension_table.col_2)
FROM dimension_table, fact_table
WHERE dimension_table.col1=fact_table.col1;

There are a few restrictions on bitmap join indexes that you should be aware of. These include the following:

• The bitmap join index is built on a single table. In the previous example, the bitmap join index is built on the FACT_TABLE table.
   
• Oracle will allow only one of the tables of a bitmap join index to be updated, inserted, or deleted from at a time.
   
• You cannot join the same table to itself, that is, no table can appear twice in the FROM clause.
   
• A bitmap join index cannot be created on an index-organized table or a temporary table.
   
• Every column in the bitmap join index must be present in one of the associated dimension tables. The joins in the bitmap index must form either a star or snowflake schema.
   
• Either primary key columns or unique constraints must be created on the columns that will be join columns in the bitmap join index.
   
• All the primary key columns of the dimension table must be part of the join criteria of the bitmap join index.
   
• All restrictions on normal bitmap indexes apply to bitmap join indexes.
   
• You cannot create a bitmap join index on an IOT.

This query can use the bitmap index on the GENDER column to filter out approximately half of the rows. The bitmap index on the REGION column can be used to further filter the rows down. In cooperating together, bitmap indexes achieve their greatest benefits.