Re: Bitmap indexes cause slow load

 One place (at least) where you will find this documented and explained, is in my book. I might not give a word-for-word statement of your problem, however.

Your '20 minutes to build the bitmap'
seems a bit extreme, though - I have just built a 20M row table with a worst-case
2-value column (i.e. alternating rows
have alternating values).

Time to build bitmap index 3 minutes 1.5 seconds; this was just one minute more than the time required to do the full tablescan of the table - a fairly typical result for this data distribution and scale - so a simple 'select /*+ full */ count(*)' should.give you an interesting baseline to target.

This is under HP-UX 11.00 with 160MHz CPU. I used the default value for create_bitmap_area_size (which is 8M) and 128K as the sort_area_size and sort_area_retained size. This tends to suggest that you have an I/O problem somewhere on your system that needs to be addressed.

As for the inserts - 8,000 per minute is pretty slow under any reasonable circumstances. This tends to suggest, combined with a note I saw in a later post from you, that you are doing single row inserts. This is particularly bad news for bitmap indexes, (a fact that is documented) because it eliminates the option for the bulk bitmap update (which is also documented).

Can you also confirm that you never delete any data from the table, thus allowing inserts to go to the middle of the table thus fragmenting middle sections of the index. Or are you guaranteeably inserting rows above the high water mark all the time.

When inserting rows above the high water mark, you have to remember that (in this case) two bitmap ranges have to be appended. Each range will cover a very small section of future growth. One will go at the end of the b-tree structure of the bitmap index, one will go into the middle. This is a lot more work than is needed for a single row insert into a normal b-tree structure.

So the answers to your questions are:
1) The manuals indicate that it will be a problem, even

    though they do not state categorically 'do not do     single row inserts into a bitmap indexed table because     the performance is significantly slower than for b-tree     indexes.

2) No

3) Drop and build seems to be the correct approach, but

    you seem to have an I/O problem across the system.     Array inserts - which you don't seem to be doing -     will reduce the impact of adding ranges to the bitmaps     if you insist on keeping the bitmaps through the update.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Mario wrote in message <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

>

>

>