Bitmap-join indexes (BJI) are essentially a "materialized view" (to use
that term generally, not specifically) of the first stage of a "star
join operation. This is the stage where Oracle compiles an
result set of dimensional keys from filtering on dimension tables. Upon
completion of this first stage of a star transformation, the result set
is then used during the second stage to join to the fact table in a
bitmap-merge operation. In a normal star transformation, this
intermediate result set is stored either in a global temporary table
(i.e. STAR_TRANSFORMATION_ENABLED = TRUE) or in memory or a temporary
segment (i.e. STAR_TRANSFORMATION_ENABLED = TEMP_DISABLE). With a BJI,
all possible such intermediate result sets are "materialized" into an
index structure on the fact table -- a bitmap-join index.
Thus, a BJI is an optimization of a star transformation -- it is not a
"general-purpose" thing at all. B*Tree indexes are general-purpose.
Bitmap indexes are general-purpose. BJI are specific to a star schema.
So, as a special-purpose structure, a BJI used for any other purpose
can be a risky proposition. Stepping backward through this chain of
So, BJI and OLTP should not mix. Of course, someone's always willing
to play and experiment, hallelujah.... ;-)
- BJI --> optimization of star transformation
- Star transformation --> optimization of join between two (or
more) dimension tables and a fact table in a star schema
- Star schema --> optimal data model for analytic/reporting
applications (a.k.a. business intelligence, data warehousing, etc)
- Data warehousing <> OLTP
But, then there is the behavior of bitmap indexes in general, of which
BJI is a subset. The primary problem with bitmap indexes is two-fold:
From a processing perspective, modifications to bitmap
index entries consume a good deal of CPU and I/O, relative to B*Tree
index entries -- plain and simple. I have a simple test case to show
this, if you like? I didn't include it in this response because a lot
of it is "manual", but it involves creating a dummy table (populated
with data from DBA_OBJECTS), then performing updates (using ROWID)
against a number of rows (i.e. 1000) on columns indexed by B*Tree, then
bitmap indexes, then recording the before/after of statistics such as
"CPU used by this session", "consistent gets", and "db block changes".
I also tend to throw data of different cardinality into different
columns, then test B*Tree-vs-bitmap on different cardinality data.
Regardless, consistently the updates of bitmap indexes consume more CPU
and more logical I/O (i.e. consistent gets plus db block changes) than
- processing, and
Then, there is the concurrency problem. B*Tree index entries refer to
one table row; bitmap index entries can refer to dozens or hundreds of
table rows. When there are many concurrent transactions involving a
bitmap index, it is
at a huge disadvantage due to multiple transactions potentially
attempting to modify the same index entry, resulting in synchronization
issues because only one transaction can lock that index entry at a time.
All in all, to summarize, bitmap indexes are more expensive to update
than B*Tree (which isn't an insurmountable
problem, as it can be overcome with more hardware) and causes massive
concurrency problems when updated (which *nothing* can *ever*
resolve). So, if you use bitmap indexes (including BJI), just make
sure you can create them then just read 'em; don't
Hope this helps!
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO 80163-0791
website = http://www.EvDBT.com/
email = Tim@xxxxxxxxx
mobile = +1-303-885-4526
fax = +1-303-484-3608
Yahoo IM = tim_evdbt
Robert Freeman wrote:
like to ask about your experience with bitmap join indexes.
There has been a lot of discussion about not using bitmap indexes in
OLTP environments. However I'm wondering if anyone has seen anything
different with respect to bitmap join indexes. Do you still see the
same performance impacts with bitmap join indexes in OLTP systems or do
they tend to do better because of the nature of the join columns (Often
PK's that are much slower with respect to changes than other columns).
Any feedback or experience you would like to share?
Robert G. Freeman
OCP: Oracle Database 11g Administrator Certified Professional Study
Oracle Database 11g New Features (Oracle Press)
Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Oracle9i RMAN Backup and Recovery (Oracle Press)
Oracle9i New Features (Oracle Press)
Other various titles out of print now...
The LDS Church is looking for DBA's. You do have to be a Church member
good standing. A lot of kind people write me, concerned I may be
the law by saying you have to be a Church member. It's legal I promise!