Re: Bitmap indexes cause slow load

From: Mark Townsend <markbtownsend_at_home.com>
Date: Fri, 11 May 2001 03:56:06 GMT
Message-ID: <B720B0E5.AA1A%markbtownsend@home.com>

Mario - have you considered partitioning ? Load the new data into it's own table, with it's own bitmap index, and then switch the new table and index into the overall partitioned table/index.

in article 9df76s$uar$1_at_news.online.de, Mario at mtechera_at_wpmc.com wrote on 5/10/01 4:13 PM:

> 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 - 22:56:06 CDT