Re: Bitmap indexes cause slow load

On Fri, 11 May 2001 18:34:39 -0400, "Kerry Scott" <kerrysco_at_ameritech.net> wrote:

>Maybe the "docs" don't say it expressly, but they do say that bitmap indexes
>are usually not a good idea on OLTP systems (try the warehousing guide).
>Having worked on some very large systems (>250GB), I can assure you that you
>will have problems if you do heavy inserting with a bitmap index is in
>place. They get primarily used on large DSS/Warehouse systems where they can
>be dropped before batch loads.
>

I think the original poster mentioned something like this: data warehouse. His volumes didn't sound that large, though: 20 million rows is not into "infinity" realm in this day and age. After all, IBM-GSA designs OLTP databases with tables containing 10 times that (don't believe me? Well, I didn't believe my eyes either...).

Question here I think will be "why bitmapped"? Bitmap indexes make sense when more than one are used. They excell at solving the old problem of index merging. But to do a raw query on a bitmap index wil usually return heaps of rows and that will take a while.

They are not good for pin-pointing data, but for selecting large ranges, intersecting with other large ranges off other bitmap indexes, pulling out the few rows of that intersection and combining them with something else.

As such, I'd expect to see more than just one bitmap index in this case. One only disturbs me. Maybe a b-tree would be a better choice? Or didn't we get the whole story?

Definitely not good for bulk loads. By their design. Much better to drop/disable, do a bulk load using direct/nologging, then re-create or re-build using heavy parallel processing. A bitmap index on 20Mrows with 4 CPUs running shouldn't take more than 10 minutes to create. I can do B-tree index parallel creation with 30Mrows in an average HP box in about that time, so this wouldn't be excessive? Dunno, no info on what calibre of hardware we talking about here was provided.

That's assuming a single bitmap index really solves anything?

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Fri May 11 2001 - 18:31:07 CDT