Re: Bitmap indexes cause slow load

Sybrand,

sorry, but I disagree with you. I have not found anywhere in the docs that Bitmap indexes greatly slowdown DML. We only perform simple INSERT INTO ... VALUES(...) nothing else.

What you will find mentioned is that CONCURRENT DML is slowed down due to the number of rows locked because bitmap indexes are so compact and thus many more records are locked per block.

I have yet to find any book that mentions that inserting records into a table is slowed down tremendously because a bitmap index exists on a column. Sorry but my opinion is that that is hogwash and the phenonmenon in question is a bug. Why would a B*tree index on the same column NOT slow down loading times so dramatically. By the way, the commit frequency doesn't change anything.

Try it yourself: create a table without indexes. Load one million records into it.
create a B*Tree index on any column and reload the million records. Then drop the index and recreate it as a bitmap index and reload the million records. Compare the times you will see what I mean.

As for your suggestion, I have definitely tried it. Bitmap indexes still outperform normal B*Trees w/histograms by an order of magnitude.

In the meantime I was able to find in the DEJAnews archives mention of other people who have had the same problem identically. Someone mentioned that there is a bug report in MetaLink.

Still my question is if increasing the sort_area_size will improve the performace of inserts.

Anyways, thanks for your answer.
Sorry for the tone but I am a little upset because this behaviour of bitmap indexes makes their use very limited with large volume of records.

Ciao,
Mario

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:tfm60qptcbtoec_at_beta-news.demon.nl...
>
> "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 - 18:13:31 CDT