Re: Bitmap indexes cause slow load

Comments embedded
"Mario" <mtechera_at_wpmc.com> wrote in message news:9df76s$uar$1_at_news.online.de...
> Sybrand,
>
> sorry, but I disagree with you. I have not found
> anywhere in the docs that Bitmap indexes greatly slowdown DML.

I was *not* discussing *docs*, I was discussing *tuning books*. Please try to read!

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

If you are *convinced* this is a bug, *submit a TAR* Why are you reluctant to do this? You don't have a support contract? This newsgroup can't replace OTS, whether you like it or not.

Sorry for the tone, you are just asking for it.

Regards,

Sybrand Bakker, Oracle DBA

[snip]

> "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 Fri May 11 2001 - 01:17:14 CDT