Re: any reasons why updating a bitmap clolumn would be 20-50x slower then with out it

Yes, there is a very good reason for this behaviour -though it's nice to see the actual figures for a change, instead of just taking the theory on trust!

Bitmap indexes are horrendous when DML is performed on the key column. Basically, the entire table has to be locked at the time of the DML, the entire table scanned, and the list of possible key values regenerated from scratch. Then the index is effectively re-built. For each update, insert or delete.

The reasoning behind this is simple enough: consider a column 'manager' which has values A and B. A nice two-leaf bitmap index can be built. If you change the manager for one row from A to B, it's relatively simple to flip the leaf node bits around. But suppose you change a row from A to C? What bits is Oracle supposed to flip, since there is no leaf node (yet) for C-values at all.

Net result: bitmap indexes on columns subject to DML are an extremely bad idea. They are designed for stable tables undergoing few if any updates, inserts or deletes. If the loads that you are describing are a regular occurrence, then either don't index that column at all, or script something that drops the index, performs the load and then re-builds the index from scratch.

Regards
HJR "Harold Pearson" <haroldPer_at_hotmail.com> wrote in message news:3a36a687$0$92409$45beb828_at_newscene.com...
> we have 90 million row table with cloumn with 50 different values
> we create index and did analyze with histogram
>
> we then loaded rows got 7 rows per sec update speed
> before we created bitmap it was 400 rows per sec for updating speed
>
> any reason why such difference
>
> we took off bitmap and it went back to 400 rows per sec
Received on Tue Dec 12 2000 - 17:33:13 CST