Using Bitmap Join Indexes to Avoid Join Operations

Doc ID: Note:149521.1 Type: BULLETIN
Last Revision Date: 28-AUG-2002 Status: PUBLISHED

PURPOSE
-------
Discuss Bitmap Join Index (BJI) Functionality.

SCOPE & APPLICATION
-------------------

Warehouse DBAs should be aware of this new Oracle9i feature that may open up
the possibility of improved performance.

Using Bitmap Join Indexes to Avoid Join Operations
==================================================

Bitmap Join Indexes (BJI) is new functionality provided by Oracle9i.
A Bitmap Join Index prestores the results of a join and thus can avoid a
join operation altogether at runtime.

Bitmap Join Indexes are especially useful in datawarehouse star schemas.
When a column in a dimension table is used to restrict the data
selected from a fact table (with Foreign Keys) and n dimension tables
(with Primary Keys), a Bitmap Join Index can avoid a join operation between
these tables.

The BJI is a space efficient way of reducing the volume of data that must be
retrieved in a query involving a join condition. Restrictions are performed
in advance and the data that would result from the join operation and
restrictions are permanently stored in a BJI.

The join condition is an equi-inner join between the primary key column/columns
of the dimension tables and the foreign key column/columns in the fact table
(i.e. dimension_pk_column = fact_fk_column).

BJIs an a more efficient (in terms of storage) alternative to materializing
joins in advance using Materialised Join Views. This is because Materialized
join views do not compress the rowids of the fact tables.

When to Create BJI
------------------

Consider the following 2 tables:

Fact Table Dimension Table
SALES CUSTOMERS
--------------------- --------------------
row | amount cust_id | | cust_id region |
|---------- ----------- | | ----------- ------- |
1 | 1000 .. 1 -----------&gt 1 East |
2 | 100 .. 1 / | | |
3 | 10 .. 2 | | | SELECT SUM(s.amount)
4 | 50 .. 2 | | 2 West | FROM sales s, customers c
5 | 200 .. 2 | | | WHERE s.cust_id = c.cust_id
6 | 20 .. 3 -----------&gt 3 East | AND c.region = 'East';
7 | 600 .. 4 | | 4 Central |
8 | 700 .. 5 | | |
9 | 200 .. 5 -----------&gt 5 East |
10 | 100 .. 5 / | | |
--------------------- --------------------

In order to find the total cost of all sales for the eastern customers,
Oracle has to perform a join between the SALES and CUSTOMERS tables.

If the information related to the region for each customer of the CUSTOMERS
table was stored in an index on SALES table, there would be no need to join
the tables at execution time.

How to Create BJI
-----------------
The creation of a BJI assumes that there is a Primary Key-Foreign Key
relationship between SALES and CUSTOMERS. This condition is mandatory
to be able to create a bitmap index.
An index is created on the fact table using a join with the dimension table.

CREATE BITMAP INDEX cust_sales_bji
ON sales (c.region)
FROM sales s, customers c
WHERE c.cust_id = s.cust_id;

SQL> select index_name, index_type,join_index from dba_indexes;

INDEX_NAME INDEX_TYPE JOIN_INDEX
---------------- ------------ -----------
CUST_SALES_BJI BITMAP YES

The information related to the region for each customer of the CUSTOMERS table
is stored in the index created on the SALES table :

Key Start End Bitmap
Rowid Rowid
----------------------------------
East, 1, 10, 1100010111
Central, 1, 10, 0000001000
West, 1, 10, 0011100000

The Bitmap Join Index contains different bitmaps, each corresponding to the
different possible values of the region column used for the index bitmap creation.
Each bit in one bitmap will correspond to a Fact table rowid.

When the user tries to find the total cost of all sales for the eastern
customers, Oracle9i need only use the BJI and the SALES table.
There is no need to join to the CUSTOMERS table. The intention is that
using the BJI is faster than evaluating the join at execution time.

Once the BJI is in place the following explain plan is achievable:

SQL> SELECT SUM(s.amount)
2 FROM sales s, customers c
3 WHERE s.cust_id = c.cust_id
4* AND c.region = 'East'
SQL> /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=12 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SALES' (Cost=12 Card=2000 Bytes=8000)
3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP INDEX (SINGLE VALUE) OF 'CUST_SALES_BJI'

Only the SALES table is accessed through the CUST_SALES_BJI bitmap join index.

3 Table Example
---------------

Fact Table Dimension Table
SALES CUSTOMERS
--------------------------------- --------------------
row | amount prod_id cust_id | | cust_id region |
|---------- ----------- ---------- | | ---------- ------- |
1 | 1000 .. 50 ---------- 1 ---------&gt 1 East |
2 | 100 .. 51 -------- | 1 / | | |
3 | 10 .. 51 | | 2 | | |
4 | 50 .. 52 | | 2 | | 2 West |
5 | 200 .. 53 | | 2 | | |
6 | 20 .. 50 ----------| 3 ---------&gt 3 East |
7 | 600 .. 53 | | 4 | | 4 Central |
8 | 700 .. 50 ----------| 5 | | |
9 | 200 .. 52 ------- | | 5 ---------&gt 5 East |
10 | 100 .. 53 ------ | | | 5 / | | |
------------------------- | | | | ---------- --------------------
| | | |
| | | | Dimension Table
| | | | PRODUCTS
| | | | ----------------------
| | | | | prod_id category |
| | | | | ------------ -------------|
| | | ------&gt | 50 CAR-1 |
| | ---------&gt | 51 CAR-2 |
| -----------&gt | 52 CAR-3 |
---------------&gt | 53 CAR-4 |
----------------------

In this example the user wishes to find the total cost of all sales of CAR-1
for the eastern customers. One possible select to achieve this is as follows:

SELECT SUM(s.amount)
FROM SALES s, CUSTOMERS c, PRODUCTS p
WHERE s.cust_id = c.cust_id AND
s.prod_id = p.prod_id AND
c.region = 'East' AND
p.category = 'CAR-1' ;

Without a BJI, this query would require 2 joins between SALES and the
CUSTOMERS and PRODUCTS tables.

Creating a BJI would mean that no joins are necessary, hopefully increasing
performance:

CREATE BITMAP INDEX c_s_p_bji
ON SALES (c.region, p.category)
FROM SALES s, CUSTOMERS c, PRODUCTS p
WHERE c.cust_id = s.cust_id
AND p.prod_id = s.prod_id;

Key Start End Bitmap
Rowid Rowid
-------------------------------------------
East CAR-1, 1, 10, 1000010100
East CAR-2, 1, 10, 0100000000
East CAR-3, 1, 10, 0000000010
East CAR-4, 1, 10, 0000000001
Central CAR-4, 1, 10, 0000001000
West CAR-2, 1, 10, 0010000000
West CAR-3, 1, 10, 0001000000
West CAR-4, 1, 10, 0000100000

SQL> SELECT SUM(s.amount)
2 FROM SALES s, CUSTOMERS c, PRODUCTS p
3 WHERE s.cust_id = c.cust_id AND
4 s.prod_id = p.prod_id AND
5 c.region = 'East' AND
6* p.category = 'CAR-1'

SQL> /

Execution Plan
----------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=7)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'SALES' (Cost=8 Card=2000 Bytes=14000)
3 2 BITMAP CONVERSION (TO ROWIDS)
4 3 BITMAP INDEX (SINGLE VALUE) OF 'C_S_P_BJI'

From the explain plan, it can be seen that no joins are required with the BJI
in place.

Restrictions
------------

Some restrictions limit the usage of this new functionality:

- The usage of Bitmap Join Index (as with ordinary Bitmap Indexes and indeed
any new optimizer feature) requires the use of the CBO. In addition, up to
date statistics must be gathered for all the objects in order for the CBO to
be able to make an accurate choice of access method.
- All joins are equi-inner joins, so they must use the equal operator without
the outer join (+) operator
- The join columns must be connected by ANDs only.
- No table can appear more than once in the FROM clause.
- For a composite primary key on the dimension table, each column of the
key needs to be involved in the join.
- Bitmap join index on IOT, functional bitmap join index and temporary
bitmap join index are not yet allowed (as of Oracle 9i).

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/193161/viewspace-50191/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/193161/viewspace-50191/