Understanding Oracle Bitmap Join Indexes

In a typical business relational database, you are often joining the same two or three tables over and over. The bitmap join index in Oracle can give you substantial gains when properly applied to many of these circumstances. In a bitmap join index, the ROWIDs from one table are stored along with the indexed column from the joined table. The bitmap join index in Oracle is a lot like building a single index across two tables. You must build a primary key or unique constraint on one of the tables. When you are looking for information from just the columns in the index or a count, then you will be able to access the single join index. Let’s look at a very simple example to learn how to use it. Then I’ll show you how to apply it to multiple columns and multiple tables.

Example

Let’s create two sample tables from our friendly EMP and DEPT tables, as shown in this listing:

 

You must then add a unique constraint (or have a primary key) to the DEPT1 table to use this type of index. You can then create the bitmap index on the EMP1 table that includes the columns of both tables.

 

 

 

 

 

 

 

You are now storing the ROWID to the DEPT1 table in the bitmap index that maps to the DEPTNO column in the EMP1 table. To test how well this works, you can perform a simple count(*) of the intersection rows between the two tables (you would generally have additional limiting conditions), forcing the use of the bitmap index with an INDEX hint.

 

 

 

 

 

 

 

 

 

 

 

You can see from the EXPLAIN PLAN output that the bitmap index was used (or you could use SET AUTOTRACE ON while in SQL*Plus; see below).

 

 

 

 

 

 

 

 

Although this simplistic example shows how to count an index (instead of the table) and applies some benefits of the bitmap join index, be sure to read this article on better uses of the bitmap join index by manipulating columns outside the join in the index.