Problem with bitmap partitioned index
My order table (3.6 M rows) has got 4 partitions (one per year). I've defined 24 LOCAL BITMAP INDEXES on the data (DSS environment); every index is built on one column.
The cardinality of these columns is quite various (from 10% to 80%) - I know in some case the use of bitmap index is not OK -.
The table is manipulated in this way:
- Nightly it has about 5K new rows inserted.
- All of this rows have update after insertion (only some of this update are referred to indexed column).
- Early in the morning the partition table referred to actual data, is analyzed with COMPUTE STATISTICS option.
- During the day the data are queried thousand times.
The problem is:
1. Before table/index partitioning all the indexes of the table were BITMAP too, and their size were acceptable; these size did not grow up to much during the time. We use to rebuild index once at 2-3 months.
2. After table/index partitioning some of the bitmap indexes related to the partition that contain actual data, get poor in few days. Their size grow up very quickly (about 16mb at day) and they can allocate 100 times more space than before partitioning. So, I must rebuild these indexes / partitions once at 2-3 days. I have checked the distinct values of poor indexes column, and there is not correlation between cardinality and growth (I mean, this problem happens on index with low cardinality as well as high cardinality).
3. I have the same order table in another schema with the same indexes and similar number of rows (1.7 M). Any of these indexes have strange growth. (I.E.: size of index on AGENT column is normally 2Mb; the poor index is about 200Mb after 4 work days).
We work with 8.1.7.2 on Windows 2000.
Does anyone have any idea about my problem?
Thanks in advance L.