| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Bitmap Indexes
Posted on behalf of a friend...
Fellow Oracle data warehouse DBAs (if this isn't you, you can delete this
note now):
I have a question regarding Oracle's bitmap indexes. We have been DABBLING
with bitmap indexes with mixed results here. In
many cases, they are great solutions. In some cases, where we have a mix of
bitmap and b-tree indexes on the same table, we occasionally get into
trouble --- this has to do with the Oracle optimizer deciding, on the fly,
to convert a regular b-tree index into a bitmap. It does this so that it
can AND or OR the various bitmap indexes together. Sounds great on the
surface but when this occurs, response time goes in the toilet.
In one situation we have, we have a fact table with two bitmap indexes and a few other b-tree indexes. A particular query we run bogs down (NEVER COMPLETES) with this mix of indexes. Based on the access path that's being chosen, we know which b-tree index is being converted on the fly. If we convert that b-tree index into a bitmap (so we now have 3 bitmap indexes and Oracle does not need to create the third one on the fly), the query really runs well. If we replace the bitmap indexes with b-tree indexes (so we only have b-tree indexes), we get decent response times. (This is all on Oracle 8.1.7.2.0, by the way.)
I'm wondering if the rest of you data warehouse DBAs have gone "whole hog" with bitmap indexes. My testing shows that when Oracle doesn't have to create a bitmap index on the fly, the queries respond wonderfully. So, I'm wondering if our dabbling is actually a bad validation approach and, instead, we should be 'running' with LOTS bitmap indexes instead of 'crawling' with only a few of them. In other words, maybe we're not "taking all of our medication", as someone else put it recently.
Any insight would be most appreciated. I'm not looking for insight on the
query I have used as an example. I'm looking for a generalized answer that
says, "Yes, if you start using bitmap indexes, you should go TOTALLY to
bitmap indexes" or, "Hmmm, we're using some bitmap indexes and some b-tree
indexes and don't have the problem you have".
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Post, Ethan
INET: Ethan.Post_at_ps.net
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu Feb 07 2002 - 16:38:48 CST
 |
 |