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?
> 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
>
>
>

This is definitely not a bug, and this disadvantage is very well documented, in at least any serious book on performance tuning. Did you ever consider using a B-tree index with an associated histogram? You must be using CBO already, as RBO doesn't support bitmap indexes at all. Usually when there are few values, and the distribution is skewed, the B-tree index will be used for the least occurring value, when a histogram is present.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu May 10 2001 - 17:39:23 CDT