Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Bitmap Indexes
Helen, Thanks. My confusion was not about bitmap index. Anyway Kirtis explanations helped to clarify my doubts. Shaibal >From: "Helen J Mitchell" >Reply-To: ORACLE-L@fatcity.com >To: Multiple recipients of list ORACLE-L >Subject: Re: Bitmap Indexes >Date: Fri, 08 Feb 2002 09:18:30 -0800 > >Hi, > >Here is an article about it bitmap indexes ... I hope this gives you information. > >Helen > ----- Original Message ----- > From: Shaibal Talukder > To: Multiple recipients of list ORACLE-L > Sent: Friday, February 08, 2002 6:58 AM > Subject: RE: Bitmap Indexes > > > Kirti, > > Just cureous. Normally bitmap indexes ae ae used for low cardinality columns. I am confused when you state - "Just one table uses it(bitmap index), with b-tree indexes > > >for PKry and one other non-unique index" > > If you mean - you use Bitmap index for the non unique index with b-tree index for PKey I am OK with that. > > Shaibal > > >From: "Deshpande, Kirti" > >Reply-To: ORACLE-L@fatcity.com > >To: Multiple recipients of list ORACLE-L > >Subject: RE: Bitmap Indexes > >Date: Thu, 07 Feb 2002 19:58:20 -0800 > > > >Ethan, > > I have been using bitmap and b-tree indexes on the same table in our data > >mart since 7.3.4. Never had a problem with query performance. Now we have > >partitioned the tables in 8.1.7.x and most tables that were using bitmaps do > >not need to use them anymore. Just one table uses it, with b-tree indexes > >for PKry and one other non-unique index. No problems so far. > > The only issue we had with bitmap indexes was the way it used up extents, > >if those were not droppped before the dataloads. The problem still exists > >with 8.1.7.x. Have not tried with 9i yet.. > > > >HTH, > > > >- Kirti > > > > > >-----Original Message----- > >Sent: Thursday, February 07, 2002 3:07 PM > >To: Multiple recipients of list 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@ps.net > >-- > >Please see the official ORACLE-L FAQ: http://www.orafaq.com > >-- > >Author: Deshpande, Kirti > > INET: kirti.deshpande@verizon.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 > >to: ListGuru@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). > > >------------------------------------------------------------------------------ > MSN Photos is the easiest way to share and print your photos: Click Here > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaibal Talukder INET: shaibal_talukder@hotmail.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 to: ListGuru@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). ><< Helenarticlerevised21_bitmap_index.doc >> MSN Photos is the easiest way to share and print your photos: Click Here -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaibal Talukder INET: shaibal_talukder@hotmail.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 to: ListGuru@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 Fri Feb 08 2002 - 13:43:46 CST