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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
