Re: Bitmap indexes cause slow load

You, Sir, are a genius (or at least a guru).

I thought I was good with test data: you take the buscuit! "Here is a bitmap index I prepared earlier... oh! it's only 20 million rows"! Ha!!!

Bring back the sticky-backed plastic.

Regards
HJR

--
The views expressed are my own only, and definitely NOT those of Oracle
Corporation
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:989659234.12300.0.nnrp-14.9e984b29_at_news.demon.co.uk...


>

>  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

> >

> >

> >

>

>