Growth of Local Partitioned Bitmap Indexes

HP-UX 11i running Oracle 9.2.0

I have a number of partitioned tables using local bitmap indexes. The indexes (and partitions) do make a significant difference (a good one that is) ito performance.

However, during the dynamic stage of production process (data loaded and processed, including a lot of bitmapped column value changes), these local bitmap indexes get huge.

And I mean huge. If I recall correctly, at one time last year November, over 4GB (total index size) for a single column local bitmap index on a 12GB partitioned table.

Re-create the index and the size drops by over 80%.

This growth seems to be linked to the amount of value changing being done to the bitmap indexed column. During the initial stages there are about 5+ distinct states/values in these columns. These change to 3 distinct states as the data "settles" down towards the end of the month.

The sizes of these bitmap indexes are causing space issues.. and is something that I've not seen when dealing with local bitmap indexes on largely static warehouse data (i.e. data volume increases but data itself is static).

With dynamically and continually changing OLTP data this is seems to be a problem.

Googling and looking at Metalink have not turned up anything interesting.

Any comments?

--
Billy