Re: Bitmap indexes cause slow load

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

Think about it: I have a cardinality of 3 for a given column, values A, B and C. You insert a new record. How do I know that column contains A, B or C, and that you haven't created a record with a value of D? Answer: I must lock the entire table, scan all the rows, and re-determine the cardinality and the key values. For every insert operation. (That's a simplified view of things, for sure, but it highlights what the problem is for bitmaps, although [see below] Oracle does its best to help you by batching the updates to the index until the entire set of inserts is completed).

As for this not being in the "docs", it's most assuredly in the Oracle training material... Performance Tuning course notes state, and I quote, 'DML Statements [ie, updates, inserts and deletes] slow down performance: They [ie bitmap indexes] are not suited for OLTP' and from the DBA Course notes (book 1): "Bitmap indexes are most advantageous... when there is read-only or low update activity on the key columns" (and, in context, it is clear they don't mean "Updates", but updates in general, including inserts and deletes).

Also from the Performance Tuning course notes: "In a DSS environment, data is usually maintained by using bulk insers and updates. Index maintenance is deferred until the end of each DML operation. For example, if you insert 1,000 rows into a table that has a bitmapped index, the bitmap column information and the ROWID information from the inserted rows are placed into the reference to the sort buffer, and then the updates of all 1,000 index entries are batched. This is why SORT_AREA_SIZE must be set properly for good performance with inserts and updates on bitmap indexes. Thus, each bitmap segment is updated only once per DML operation, even if more than one row in that segment changes"

So... given it's not a bug, have you set SORT_AREA_SIZE correctly? And have you tried doing several smaller inserts instead of a single large one?

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