| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re:Bitmap Indexes
Ethan,
Our data warehouse has both bit map and b-tree indexes and we normally do not have a problem. One headache we do have is that one of the indexes is on a column that accumulates a lot on new values each time the warehouse is loaded which really makes a mess of the load process, namely it takes way toooooo long. The answer is to drop the bitmap indexes before loading new data and then rebuilding them afterwards. Come to think of it that may be part of the reason why the bit maps don't cause a problem. Since a bit map index is only usable by the cost based optimizer and the user who built the warehouse refuses to compute the statistics on those indexes, so their not in use.
Interesting, I'll have to do some experimenting now!!
Dick Goulet
____________________Reply Separator____________________ Author: "Post; Ethan" <Ethan.Post_at_ps.net> Date: 2/7/2002 1:06 PM
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).
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 - 17:13:03 CST
 |
 |