Skip navigation
1 2 3 4 Previous Next

Bert Scalzo's Blog

46 posts

There are two very distinct and unique types of bitmap indexes within Oracle: bitmap indexes and bitmap join indexes. Let’s examine the fundamental difference between them.

 

Let’s assume we’re indexing a star schema designed set of tables. To keep it simple let’s suppose the design has one fact table and two dimensions as show by the physical data model here in Figure #1.

 

img1.png

 

If we implement the above table design using traditional bitmap indexes then the fact table DDL would look as follows:

 

-- Create indexes for table FACT using traditional bitmap indexes

CREATE UNIQUE INDEX FACT_PK ON FACT ( FACT_DATE, DIM1_FK, DIM2_FK );

CREATE BITMAP INDEX DIM1_FK ON FACT ( DIM1_FK );

CREATE BITMAP INDEX DIM2_FK ON FACT ( DIM2_FK );

 

The FACT_PK index would most likely only be active (i.e. valid or online) during batch load operations to maintain the data fidelity. Then during predominately ad-hoc query only times the two bitmap indexes would be used in order to affect the Oracle “star transformation” explain plan. As for the FACT_DATE column, the table would most likely be partitioned by that value – therefore an index might or might not be advantageous (i.e. depends upon your data, its distribution, its skew and other factors).

 

If now we instead implement the same table design using the specialized bitmap join index then the fact table DDL would look as follows:

 

-- Create indexes for table FACT using specialized bitmap join index

CREATE UNIQUE INDEX FACT_PK ON FACT ( FACT_DATE, DIM1_FK, DIM2_FK );

CREATE BITMAP INDEX FACT_BJ1 ON FACT ( DIM1_FK )

FROM FACT, DIM1

WHERE FACT.DIM1_FK = DIM1.ATTRIBUTE1;

CREATE BITMAP INDEX FACT_BJ2 ON FACT ( DIM2_FK )

FROM FACT, DIM2

WHERE FACT.DIM2_FK = DIM2.ATTRIBUTE1;

 

In fact you might even decide that a single bitmap join index can handle all the queries, in which case the DDL would now look like this:

 

-- Create indexes for table FACT using specialized bitmap join index

CREATE UNIQUE INDEX FACT_PK ON FACT ( FACT_DATE, DIM1_FK, DIM2_FK );

CREATE BITMAP INDEX FACT_BJ ON FACT ( DIM1_FK, DIM2_FK )

FROM FACT, DIM1, DIM2

WHERE FACT.DIM1_FK = DIM1.ATTRIBUTE1

  AND FACT.DIM2_FK = DIM2.ATTRIBUTE1;

 

Let’s examine this last case to see just what a bitmap join index is and how it works. Back when data warehouses we first becoming popular there was a database vendor and platform called “Red Brick” founded by Ralph Kimball of data warehousing fame. A critical concept in many of these specialized data warehouse database engines was the concept of a “star index” – i.e. an index on the fact table which contains the row id’s for both the fact table rows and the dimension table rows. That’s exactly what we constructed in the last example using the bitmap join index. Here’s a simple diagram that displays what such a “star index” looks like – which is what a “bitmap join” including all dimensions implements.

 

img2.jpg