Re: Bitmap Indexes

Ethan, i'd have to go with the later as most cases we are ok with bitmap indexes(not a huge but not bad, 100G warehouse).

Our fact tables have pk as btree and most others are bitmap, and any that point back to dimensions are definitely bitmap indexes.

Now we have had a few that went bonkers but usually a /*+ FIRST_ROWS */   hint has taken of the psycho explain plans.

hth, joe

Post, Ethan wrote:

> 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".
>

-- 
Joe Testa, Oracle DBA
Nothing new to put here, hmmmmmm






-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Testa
  INET: teci_at_the-testas.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).