Bitmap indexes cause slow load

I am working on a data warehouse that is updated daily with new data. One of the main tables contains over 20 million records, to which 200,000 are added daily through a batch job. Otherwise, there is no transaction level processing going on from other users. The data is used purely for reporting purposes.

The time window we have for loading and calculating is 3 hours because the data is used across time zones.

One of the columns was indexed with a bitmap index to improve query performance on the above mentioned table. It has only two possible values, This means a B*tree index is not very effective. The performance gains are dramatic on queries but the loading speed has decreased from 8000 rows/minute to less than 500 rows per minute.

If I drop the bitmap index the load speed increases again. The load speed also increases when I use a traditional B*Tree index instead of the bitmap index.

Dropping and recreating the index is not an alternative because creating the index takes about 20 minutes and we have about 10 columns that need the query performance gains of a bitmap.

In my opinion this is clearly a bug in O8.1.7: Bitmap indexes are perfectly suited for data warehouse environments from the query performance gains but cannot be used because load times become unbearable.

Now my questions:
1) Has anybody else documented this behaviour? 2) Is there any way to improve INSERT performance on bitmapo indexed tables by increasing the values of SORT_AREA_SIZE or BITMAP_MERGE_AREA_SIZE ? 3) Any other workarounds?

Regards to all!

Mario Received on Thu May 10 2001 - 15:54:40 CDT