Re: Your bitmap join index experience

From: Tim Gorman <tim_at_evdbt.com>
Date: Mon, 05 Jan 2009 15:24:22 -0700
Message-ID: <49628896.6060602_at_evdbt.com>




  


Robert,

Bitmap-join indexes (BJI) are essentially a "materialized view" (to use that term generally, not specifically) of the first stage of a "star transformation" join operation.  This is the stage where Oracle compiles an intermediate 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 reasoning...
  1. BJI --> optimization of star transformation
  2. Star transformation --> optimization of join between two (or more) dimension tables and a fact table in a star schema
  3. Star schema --> optimal data model for analytic/reporting applications (a.k.a. business intelligence, data warehousing, etc)
  4. Data warehousing <> OLTP
So, BJI and OLTP should not mix.  Of course, someone's always willing to play and experiment, hallelujah.... ;-)

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:
  1. processing, and
  2. concurrency
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 B*Tree indexes.

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 insert/update/delete 'em.

Hope this helps!
Tim Gorman
consultant - Evergreen Database Technologies, Inc.
P.O. Box 630791, Highlands Ranch CO  80163-0791
website   = http://www.EvDBT.com/
email     = Tim_at_EvDBT.com
mobile    = +1-303-885-4526
fax       = +1-303-484-3608
Yahoo IM  = tim_evdbt


Robert Freeman wrote:
I'd 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?

RF

 
Robert G. Freeman
Author:
OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
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...
Blog: http://robertgfreeman.blogspot.com
The LDS Church is looking for DBA's. You do have to be a Church member in
good standing. A lot of kind people write me, concerned I may be breaking
the law by saying you have to be a Church member. It's legal I promise! :-)


-- http://www.freelists.org/webpage/oracle-l Received on Mon Jan 05 2009 - 16:24:22 CST

Original text of this message