Best Uses for the Oracle Bitmap Join Index

This article examines three targeted areas where you may discover excellent uses for bitmap join indexes In Oracle, including: bitmap join indexes on columns other than the join; bitmap join indexes on multiple columns; and bitmap join indexes on multiple tables.

Be sure to also read these articles about understanding bitmap indexes and bitmap join indexes.

Bitmap Join Indexes on Columns Other Than the Join

Consider this example where the EMP1 and DEPT1 tables are once again joined on the DEPTNO column. In this example, you want to index the LOC column instead of the join column. This allows you to select the location column from the DEPT1 table by directly accessing only the index and the EMP1 table. Remember, the join condition must be on the primary key or unique column. The example in the following listing assumes that the unique constraint on DEPT1.DEPTNO from the example in the earlier listing (where I added a unique constraint to the DEPT1 table) exists.

 

 

The query shown next can now use the bitmap join index appropriately:

 

Bitmap Join Indexes on Multiple Columns

Consider an example where you want an index on multiple columns. The syntax is still the same, but now you include multiple columns in the index. The next example assumes that the unique constraint on DEPT1.DEPTNO from the example in the earlier listing (where I added a unique constraint to the DEPT1 table) exists.

 

 

The query in the following listing is able to use the bitmap join index appropriately:

 

 

Bitmap Join Indexes on Multiple Tables

As you become more familiar with using the bitmap join index, you will be able to solve complex business problems that involve multiple tables. The following example shows how to apply the bitmap join index to multiple tables. The syntax is still the same, but it has now been expanded to include multiple columns in the index and multiple tables being joined for the index. The example shown next assumes that the unique constraint on DEPT1.DEPTNO from the example in the earlier listing (where I added a unique constraint to the DEPT1 table) exists and, additionally, that it exists on SALES1.EMPNO (creation not shown).

 

 

The query in this next listing is now able to use the bitmap join index appropriately:

 

Bitmap Join Index Caveats

Because the result of the join is stored, only one table can be updated concurrently by different transactions, and parallel DML is supported only on the fact table. Parallel DML on the dimension table marks the index as unusable. No table can appear twice in the join, and you can’t create a bitmap join index on an index-organized table (IOT) or a temporary table.

 

Another Nice Use for the Bitmap Join Index

A nice tuning trick when you are counting rows is to try to count the index instead of the table. Consider the following large table example used for counting. These tables each contain roughly two million rows, so that you can see the possible impact on a larger scale. The new tables, EMP5 and EMP6, each have 2 million rows with EMPNO indexes on them.

To add the constraint and run a join without the bitmap index:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

There is an index on the EMP5 table, but there is no correlation or index back to the EMP6 table because the index on EMP6 contains only EMPNO as the second part of a concatenated index. The result is a relatively slow query. If you make EMPNO the only part or the leading part of the concatenated index, you solve this problem. Use the new bitmap join index, as shown here:

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Performing a count of the bitmap join index makes this very fast. I chose this example for a reason. The real problem with the original slow query was not that it took a minute to execute, but that it performed over 6 million memory block reads and over 7000 disk block reads. You may not receive any wait events, but you have a poorly written query that will cause problems when you have volumes of users on the system. Take a step up to expert level by finding queries with large memory and disk reads and start doing proactive tuning now so you don’t get to wait states and need to tune things reactively. Using a bitmap join index is one way to improve performance.