 |
|
Oracle Tips by Burleson |
Using the Oracle 10g Bitmap
Join Index
Oracle 10g has introduced a new method to
create speed join queries against very large data warehouse tables.
This new method is called the bitmap join index, and this new table
access method required the creation of an index that performs the join
at index creation time and creates a bitmap index of the keys that are
used in the join.
While Oracle markets this new feature with
great fanfare, the bitmap join index is only useful for table joins
that involve low-cardinality columns (e.g. columns with less than 300
distinct values). Bitmap join indexes are also not useful for OLTP
databases because of the high overhead associated with updating bitmap
indexes.
For our example, we will use a many-to-many
relationship where we have parts and suppliers. Each pert has many
suppliers and each supplier provides many parts (Figure 1)
Figure 1 – A many-to-many Oracle table
relationship
In this example, the Oracle database has 200
types of parts and the suppliers provide parts in all 50 states. The
idea behind a bitmap join index is to pre-join the low cardinality
columns together, thereby making the overall join faster:
While b-tree indexes are used in the standard
junction records, we can improve the performance of Oracle 10g queries
where the predicates involve the low cardinality columns. For
example, look at the query below where we want a list of all suppliers
of pistons in North Carolina:
select
supplier_name
from
parts
natural join
inventory
natural join
suppliers
where
part_type = ‘piston’
and
state = ‘nc’
;
(Note the use of the new Oracle 10g natural
join syntax, removing the need to specify table join criteria)
For queries that have additional criteria in
the WHERE clause that does not appear in the bitmap join index, Oracle
10g will be unable to use this index to service the query. The
following query will not use the bitmap join index:
select
supplier_name
from
parts
natural join
inventory
natural join
suppliers
where
part_type = ‘piston’
and
state = ‘nc’
and
part_color = ‘yellow’
;
Prior to Oracle 10g, this query would require
a nested loop join or hash join of all three tables. In Oracle 10g,
we can pre-join these tables based on the low cardinality columns:
To create a bitmap join index we issue the
following SQL. Note the inclusion of the FROM and WHERE clauses
inside the CREATE INDEX syntax.
create
bitmap index
part_suppliers_state
on
inventory( parts.part_type, supplier.state)
from
inventory i,
parts p,
supplier s
where
i.part_id = p.part_id
and
i.supplier_id = p.part_id;
Note that this bitmap join index specified the
join criteria for the three tables, and creates a bitmap index on the
junction table (inventory) with the type and state keys (Figure 2).
Figure 2 – A bitmap join index
Oracle claims that this indexing method
results in more than 8x improvement in table joins in cases where all
of the query data resides inside the index. However, this claim is
dependent upon many factors, and the bitmap join is not a panacea. In
many cases the traditional hash join or nested loop join may
out-perform a bitmap join. Some limitations of the bitmap join index
join include:
1. The indexed columns must be of low
cardinality – usually with less than 300 distinct values
2. The query must not have any references in
the WHERE clause to data columns that are not contained in the index.
3. The overhead when updating bitmap join
indexes is substantial. For practical use, bitmap join indexes are
dropped and re-built each evening about the daily batch load jobs.
Hence bitmap join indexes are only useful for Oracle data warehouses
that remain read-only during the processing day.
In sum, bitmap join indexes will tremendously
speed-up specific data warehouse queries, but at the expense of
pre-joining the tables at bitmap index creation time.
For more details, see the "Easy
Oracle Series" a set of books especially designed by Oracle
experts to get you started fast with Oracle database technology.
|
