Bitmap indexes - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: Bitmap indexes

  1. #11
    Join Date
    Apr 2000
    Location
    roma
    Posts
    131
    i found them good on select (or static tables) but disastereos on sqlloader (go often on direct load)

  2. #12
    Bitmap indexes are not good for OLTP.
    The exception is a table not frequently updated in an OLTP DB. (catalog)
    Ramon Caballero, DBA, rcaballe@yahoo.com

  3. #13
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    At the risk of muddying the waters, let me throw in my 2 cents' worth:

    Contrary to an earlier entry, a bitmap index is NOT a table - it's an index. Internally, it is structured like a B* Tree index (to maintain performance when searching for a key value), but the index entries are different.

    In a "regular" index, each entry is composed of a key value and a rowid.

    In a bitmap index each entry is composed of a key value, the first and last data blocks for each contiguous string of blocks containing the key value, and the bitmap that represents the rows within the indicated string of blocks. Furthermore, in order to conserve space, Oracle uses a compression algorithm.

    For example, if your table looked like this:

    Block 1:
    A
    B
    A
    A
    Block 2:
    C
    D
    D
    B
    Block 3:
    A
    B
    E
    E

    Then the entries in your bitmap index might look like this:

    A: Blk1: Blk1: 1x1 1x0 2x1
    A: Blk3: Blk3: 1x1 3x0
    B: Blk1: Blk3: 1x0 1x1 5x0 1x1 1x0 1x1 2x0
    C: Blk2: Blk2: 1x1 3x0
    D: Blk2: Blk2: 1x0 2x1 1x0
    E: Blk3: Blk3: 2x0 2x1

    So you DO pay a small performance penalty due to the compression/decompression of bitmaps. But this can be more than compensated for in the compactness of the index (notice that in our trivial example, we only needed 6 index entries to represent 12 rows - conceivably you could index a table with a million rows and 4 key values with only 4 index entries!) and the capability for fast boolean logic that has already been discussed.

    You pay a HUGE penalty if you are updating the values on which the bitmap index is based. For instance, if we
    update the table to change the value of the second row in Block 3 from 'B' to 'C', we have to find the proper index entry for 'B', decompress the bitmap, find the appropriate bit, change it from 1 to 0, then determine that the string of contiguous blocks containing 'B' has changed from Blk1:Blk3 to Blk1:Blk2, so we have to eliminate the bits representing Block 3, and finally recompress the bitmap. THEN we have to find the appropriate entry for C and decompress it. We then find that we now have a new contiguous block that this bitmap has to reflect, so the end block entry is changed from Blk2 to Blk3 and the bitmap for the 'C' entry goes from
    1x1 3x0
    to
    1x1 4x0 1x1 2x0
    and then we have to recompress it.

    This overhead is why Oracle recommends you NOT use bitmap indexes if you are going to do frequent DML on the key values or you have a relatively small table. Bitmap indexes were intended primarily for use in data warehouses, where tables are millions of rows long and DML is rare (except during periodic refreshes, which tend to take the form of inserts rather than updates).

  4. #14
    Join Date
    Sep 2000
    Posts
    128
    Carp - Good answer and really explains things!

    When we were having Snapshot too old problems the other day we found some code which contained commits accross fetches... Tut Tut we thought, made the rollback segments bigger and removed them.

    Next day - Out of index tablespace...? Weird.
    A bitmap index had grown from 8Mb to over 500Mb during the update of 300,000 records. So, back in the commits go since the app is coded to run pants without them (hence my cacheing question thread!).

    I thought I was going mad, and couldn't work out how an index had grown so much, but what you're saying is that it recompresses after updating... Would that be right?

  5. #15
    Very nice!
    Thank you!
    I have found that a rebuild of bitmap indx are very helpful is your case Terry
    Just a question:
    Why 2 As ? Why not like B?

    [Edited by rcaballe on 11-09-2000 at 12:57 PM]
    Ramon Caballero, DBA, rcaballe@yahoo.com

  6. #16
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    just wondering what is

    A: Blk1: Blk1: 1x1 1x0 2x1

    the 1x1 1x0 and 2x1 stand for?

  7. #17
    Join Date
    Nov 1999
    Location
    Elbert, Colorado, USA
    Posts
    81
    Sorry - that was the notation I was using to indicate a compressed entry.
    So
    1x1 3x0 2x1
    would indicate a bitmap that looks like
    100011
    when decompressed.

  8. #18
    Join Date
    Aug 2000
    Location
    Belgium
    Posts
    342
    I knew how the bitmap part worked,
    but not how it was stored in the datafiles.

    Now ik know both.

    Tanx Carp
    Gert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width