Bitmap Indexes

travissolt · Post by **travissolt** » Fri Nov 02, 2007 7:52 am

The data architects at my company have decided that the BITMAP index is the best index to use for our DW and I am wondering if that is such a good idea considering we have updates rather frequently. I have read several articles on-line that state not to use bitmaps if you plan on updating excessively and I am wondering if anyone has any experience in DS that involved bitmaps and what they did during the load. Currently we disable an re-enable the bitmaps during the load but we have proven this is not always successful. I am wondering if DS can only handle bitmaps if the entire bitmap is dropped and then recreated vs disabled and renabled or if bitmaps and DS should even be used together. Perhaps a B-tree would be better. Let me know and thanks

ray.wurlod · Post by **ray.wurlod** » Fri Nov 02, 2007 10:12 am

It's not DataStage. DataStage just throws rows at the database server. Any restrictions exist there.

For example, it is perfectly possible to bulk load the Red Brick database even when there is a mix of B-tree, target and bitmap indexes on a table. But, then, Red Brick was designed from the ground up to support star schema implementation.

chulett · Post by **chulett** » Fri Nov 02, 2007 10:29 am

As noted, really something outside of anything DataStage. What database, by the way? That can change the answers given as, for example, Oracle is notorious for problems when loading with bitmap indexes in place.

travissolt · Post by **travissolt** » Sun Nov 04, 2007 10:33 am

Yes its an Oracle DB we are loading and your right its not really a DS issue other then the fact that unless you knew what to do with the indexes on the table you were loading your DS job would not work. For example if you try to load inserts or updates to a table and the bitmaps were left enabled then the job would just sit there. Really just seeing if anyone had any tips for the index process that DS works best with. I am sure benchmarks have been done on loading with a b-tree or bitmap index structure as well as if they were dropped or disabled during the load. Just looking for someone that started with the bitmaps and ended up switching to b-tree's for speed issues which is what we are leaning towards.

chulett · Post by **chulett** » Sun Nov 04, 2007 11:07 am

I haven't had that experience, but then it's been some time since I've had to worry about loading Oracle tables with bitmap indexes. We found that the jobs ran just fine, but more often than not the indexes would be corrupt post-load. We ended up doing the traditional 'drop all and rebuild post-load' thing as a work around.

Curious how they are being used in your database? Is it the traditional 'low cardinality column' index or are you actually using bitmapped join index or bitmap star transformations? I don't personally have any benchmarks, perhaps some Google-Fu would turn something up. For example, I found these rather easily - one on your Bitmap versus B-Tree subject and another three-part DBA Magazine article on them as well. Both were an interesting read.

saikir · Post by **saikir** » Sun Nov 04, 2007 10:37 pm

Hi,

Using of Bitmap indexes in warehouse is very common. Especially on fact tables you tend to have only bitmap indexes on every column corresponding to the dimension.

Oracle states that, you should use bitmap indexes only on low cardinality columns. However, in most projects including ours, we use bitmap indexes on fact tables as Oracle goes for a star transformation. Star transformation is the most efficient in terms of retrieving Data for reporting

In our project every time we load data, we always disable and re-build the bitmap indexes. This has been in place for couple of years.

Sai

saikir · Post by **saikir** » Sun Nov 04, 2007 10:39 pm

Hi,

Using of Bitmap indexes in warehouse is very common. Especially on fact tables you tend to have only bitmap indexes on every column corresponding to the dimension.

Oracle states that, you should use bitmap indexes only on low cardinality columns. However, in most projects including ours, we use bitmap indexes on fact tables as Oracle goes for a star transformation. Star transformation is the most efficient in terms of retrieving Data for reporting

In our project every time we load data, we always disable and re-build the bitmap indexes. This has been in place for couple of years.

Sai

travissolt · Post by **travissolt** » Tue Nov 06, 2007 10:23 am

We also have a star strucutre and for the most part the process seems to work aside from the amount of time it takes to rebuild all of the bitmaps at the end of the run. The main issue we have is that we try to update rows in the table for security purposes and we will get different results for the same update statment. Recently the DBA tested the same updates on a b-tree structure and it worked much better and was even faster. Based on the discussion it sounds like most people use the disable and re-enable process during the job. Any chance the re-enable does not truly represent the correct indexes? Our DBA is going to test a few more things thanks for the info.

saikir · Post by **saikir** » Tue Nov 06, 2007 10:06 pm

Hi,

One Important thing: I belive your fact table would be partitioned by time. And bit map indexes on a table are always local. In other words when you partition the table, all the bitmap indexes also will get partitioned. The key thing in disabling and re-building the indexes is that, you do that only for that time partition.

For example if your fact table is partitioned monthly, then disable and rebuild the indexes only for that month. You can get this information from the oracle metadata table user_ind_partitions

Sai