bitmap join index 位图连接索引

Best Uses for the Oracle Bitmap Join Index

#LR15445

By Richard Niemiec

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.

 

 

 

 

Attribution:

This article is a complimentary excerpt from Oracle Database 11g Release 2 Performance Tuning Tips & Techniques, published by McGraw- Hill.Visit mcgrawhill.com to purchase the book.

Bitmap Join Index Restrictions and Requirements

Join results must be stored, therefore, bitmap join indexes have the following restrictions:

• Parallel DML is only supported on the fact table. Parallel DML on one of the participating dimension tables will mark the index as unusable.

• Only one table can be updated concurrently by different transactions when using the bitmap join index.

• No table can appear twice in the join.

• You cannot create a bitmap join index on a temporary table.

• The columns in the index must all be columns of the dimension tables.

• The dimension table join columns must be either primary key columns or have unique constraints.

• The dimension table column(s) participating the join with the fact table must be either the primary key column(s) or with the unique constraint.

• If a dimension table has composite primary key, each column in the primary key must be part of the join.

• The restrictions for creating a regular bitmap index also apply to a bitmap join index. For example, you cannot create a bitmap index with the UNIQUEattribute. See Oracle Database SQL Language Reference for other restrictions.