Re: Bitmap Indexes

From: <Cherie_Machler_at_gelco.com>
Date: Fri, 08 Feb 2002 04:31:35 -0800
Message-ID: <F001.00409E65.20020208041824@fatcity.com>

Ethan,

We are using all bitmap indexes on our main fact table. We also have a mix of bitmap and b-tree indexes on other tables. The bitmap indexes work best in SQL with multiple where clauses anded together where each column in the where clause has a bit-map index on it. The more of these bit-mapped indexes that you have in your where clause, the better. At least two is best.

We have quite a lot of pockets of poor performance. Many of those explain plans mix bit-mapped indexes with b-tree indexes. I've never seen the optimizer try to convert a bit-mapped index to a b-tree index but we are at 8.0.4. I've never tried to convert a bit-mapped index to a b-tree or vice-versa. Might be interesting to try. However it's tough because this is a production database that was already in place when I got here.

I should also note that our fact table and several others are partitioned and we have star_transformation_enabled set to true.

Please post a summary of your results to the list if you have time. This is an interesting thread.

Cherie Machler
Oracle DBA
Gelco Information Network.

                                                                                       
                           
                    "Post, Ethan"                                                      
                           
                    <Ethan.Post_at_ps       To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>      
                    .net>                cc:                                           
                           
                    Sent by:             Subject:     Bitmap Indexes                   
                           
                    root_at_fatcity.c                                                     
                           
                    om                                                                 
                           
                                                                                       
                           
                                                                                       
                           
                    02/07/02 03:06                                                     
                           
                    PM                                                                 
                           
                    Please respond                                                     
                           
                    to ORACLE-L

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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: Cherie_Machler_at_gelco.com

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