Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Bitmap indexes cause slow load

Re: Bitmap indexes cause slow load

From: Kerry Scott <kerrysco_at_ameritech.net>
Date: Fri, 11 May 2001 18:34:39 -0400
Message-ID: <sKZK6.226$Yf7.21239@nntp0.chicago.il.ameritech.net>

Maybe the "docs" don't say it expressly, but they do say that bitmap indexes are usually not a good idea on OLTP systems (try the warehousing guide). Having worked on some very large systems (>250GB), I can assure you that you will have problems if you do heavy inserting with a bitmap index is in place. They get primarily used on large DSS/Warehouse systems where they can be dropped before batch loads.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:9dg01a$hcb8k$1_at_ID-62141.news.dfncis.de...
> 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 - 17:34:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US