Re: Bitmap indexes cause slow load

"Mario" <mtechera_at_wpmc.com> wrote in message news:9dev2h$sqk$1_at_news.online.de...
> 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?

It's standard fare. Bitmap indexes are abhorred on any table subject to DML, since (effectively) the entire bitmap index has to be re-created at the first insert or update (how do we know that the new record contains one of the existing values for the column, and that you haven't cunningly inserted a brand new one?)

It's not a bug, it's perfectly standard procedure that is extremely well documented. Bitmaps and DML do not mix. Ever.

Regards
HJR
> 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 Sat May 12 2001 - 02:09:16 CDT