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

[hhbrazil]

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;

[Orca777]

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

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

[Orca777]

Hi,
soory i did not check it;
ia m for IOT too

and IOT can additionallay indexed

Orca

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

[julian]

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

[julian]

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?

[jmodic]

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.

[julian]

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

[jmodic]

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.