Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Bitmap Indexes

Re: Bitmap Indexes

From: Igor Neyman <ineyman_at_perceptron.com>
Date: Fri, 08 Feb 2002 11:09:52 -0800
Message-ID: <F001.0040A76D.20020208102325@fatcity.com> 







Helen,


 



Attachments do not make it to the list.


 



Igor Neyman, OCP DBA<A 


href="mailto:ineyman_at_perceptron.com">ineyman_at_perceptron.com  



<BLOCKQUOTE dir=ltr 


style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">





  Sent: Friday, February 08, 2002 12:18 
  PM


  Subject: Re: Bitmap Indexes
  


  Hi,


   


  Here is an article about it bitmap indexes 
  ...  I hope this gives you information.
   


  Helen


  <BLOCKQUOTE 


  style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">





    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_at_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_at_ps.net 
    >-- 
    >Please see the official ORACLE-L FAQ: http://www.orafaq.com 
    >-- 
    >Author: Deshpande, Kirti 
    > INET: kirti.deshpande_at_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_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). 
    
    


    MSN Photos is the easiest way to share and print your photos: <A 
    href="http://go.msn.com/bql/hmtag3_etl_EN.asp">Click Here-- Please 
    see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Shaibal 
    Talukder INET: shaibal_talukder_at_hotmail.com Fat City Network Services -- 
    (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet 
    access / Mailing Lists 



Received on Fri Feb 08 2002 - 13:09:52 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US