Re: Bitmap indexes cause slow load

Hi Folks,

thanks for all the replies!

Some more about the problem:
Hardware: Sun, 2CPUs, 2 GB mem.

The problem table: over 70 columns with 2 B*Tree indexes (including the PK) and 10 bitmap indexes. Over 20 mil records and counting. Almost 3 GB in the tablespace is used.

Loading is taking place as some of you suggested: one INSERT at a time from a SAS client connecting through Net8 (terrible, but that's life when dealing with a legacy system and with different departments playing politics to protect their turf).

One of my question still holds:
What range of values should be used
for SORT_AREA_SIZE or BITMAP_MERGE_AREA_SIZE? Do they help at all when performing INSERTS into the table with bitmap indexes?

Testing with such volumes is very difficult. I have played with a scaled down version (only 2 mil records) where I have used an INSERT....SELECT from a temporary table (i.e. SAS first loads to an empty table and then the INSERT...SELECT from the temporary table to the final table). In the tests this is about a factor 10 faster than the single inserts but I don't have commit control and this will be a problem with the production volumes and the rollback segments. I am experimenting with bulk loads using FORALL but I have been told that I will need plenty of memory.

If you are inetrested I will keep you informed.

Regards from Bavaria.
Mario

"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
>
>
>
Received on Mon May 14 2001 - 17:30:02 CDT