Bitmap Index. How use?? Is it worthwhile to use??
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Bitmap Index. How use?? Is it worthwhile to use??

  1. #1
    Join Date
    Aug 2000
    Location
    Sao Paulo
    Posts
    114

    Question

    Bitmap Index

    I have a table with 106 million lines (6gb) particionada in 6 fractions, the pk is composed by the first 6 columns (it has 7 columns to the whole) and also this particionada in 6 fractions.
    Would I like to know if I create 6 bitmap index (one for each column of PK), in a consultation (select) that uses 5 columns of the pk they will be used the bm idx??
    Ex.:
    select a,b,c,d,e from xpto where a between pdp and wdw order by a;

  2. #2
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    when Bitamp-indexes then :

    * They are one of the fastest access-path for big tables

    * sometimes u have to use the INDEX_COBINE-Hint

    * you will have slower Insert-Rate, more CPU is needed

    * The BM-index do not like heavy updates/ inserts

    * you will ahve tor reorg them

    * init.ora bm-params to set

    I would decide not to mix a prtitioned table wit bm-indexes (common statement )
    I would even divide the table in more partitions. 60 instead of 6 if its possible.

    But when you want to query the table flexible than i understand that you would like to have more single-col-bm-indexes. so mixing te two techniques probalby could work.

    So there is no clear no or yes even mor a fuzzy no/Yes

    Orca



  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Why don't you rather consider reorganizing your table to Index-Organized one (IOT) ?

    PK consisting of 6 columns out of 7 columns in a table -seems perfect candidate for IOT to me.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    Hi,
    soory i did not check it;
    ia m for IOT too

    and IOT can additionallay indexed

    Orca

  5. #5
    Join Date
    Apr 2001
    Posts
    219
    Oh yeah, bitmap indxes on a partitioned table with lots of inserts are bad! So, bad it locked up partitions and sometimes tables in 8.1.6. So, I had to remove them. They plagued me for a long time, becuase I thought it was something else.
    ______________________
    Applications come and go,
    but the data remains!

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by jmodic
    Why don't you rather consider reorganizing your table to Index-Organized one (IOT) ?

    PK consisting of 6 columns out of 7 columns in a table -seems perfect candidate for IOT to me.
    On the other hand Jurij, using IOTs before 9i has its disadvantages.

    Moreover, PK consisting of 6 columns doesn't sound good to me :-)
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by Zaggy
    Oh yeah, bitmap indxes on a partitioned table with lots of inserts are bad! So, bad it locked up partitions and sometimes tables in 8.1.6. So, I had to remove them. They plagued me for a long time, becuase I thought it was something else.
    I have noticed the same problem. I though it was due to my OPS enviroment. Did you get the locks in a single instance database?

    Did you have histograms on the bitmapped columns?
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by julian
    I have noticed the same problem. I though it was due to my OPS enviroment. Did you get the locks in a single instance database?
    This behavior is normal and expected, regardles if you are using OPS/RAC or single instance database. Locking with bitmap indexes is different than with normal B*Tree indexes. With B*Tree there the locking can be implemented on a row level (ie only the row that is being modified is locked), whereas with bitmap a range of rows that has the same bit set as the row that is being modified are lock. The number of rows in that bitmap range that must be locked is not fixed, it depends on the distribution of the bits in a particular bitmap.

    That's why there are warnings all over the docs saying that bitmap indexes are not suitable for tables that are being modified simultaneously by different sessions.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Thanks Jurij for the input. Have you noticed if proper histograms influence somehow on the locks in the positive way?

    BTW, it is often the case that tables are being modified simultaneously by different sessions.

    IMHO, using bimap indexes is a tricky business :-)
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    That's why they are ment primarily for datawarehouse environment, definitely not for OLTP. In DW typicaly only one session is modifying any one table simultaneously (during ELT process), so locking in DW is not realy an isue. All other sessions are only performing queries on DW, there is (almost) no DML in place by "normal" users during normal operation of datawarehouse.

    And I can't immagine how histograms would influence locks in any way. Histograms are only "metadata" for the optimizer to decide the optimal execution plan for queries. With DML Oracle has no choice about locking - rows that are undergoing changes (with bitmap indexes not only changing rows) must be locked with 100% predictable type of lock(s), and histograms or optimizer or execution path of how those changing rows are accessed has nothing to do with locking.

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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