Understanding Oracle Bitmap Join Indexes

By: Richard Niemiec


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:

0491_001

 

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.

 

0491_002

 

 

 

 

 

 

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.

 

0491_003

 

 

0492_001

 

 

 

 

 

 

 

 

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).

 

0492_002

 

 

 

 

 

 

 

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.

 

 

Leave a Reply