A bitmap index for a DATE column? Sounds very strange.
In a bitamp index the leaf stores a bitmap for EACH key value so if you add 24 months to a date you increase the cardinality of the column twice. That means the index is larger twice.
Bitmap index should be used for columns with very low cardinality which a DATE column probably isn't.
Updating a Bitmap-indexes causes waist of storage.
The organization of a bitmap index is based on RowID-calculations. So dating up a row with a key-columns forces the to load complete blocks and to reorganize/recalculate ( Size ) them again.
Under Orcale 7 we had a storage-increase about 600%
after heavy UPDATE-Rate ;
The system is a large DMS, not OLTP. The table has 5 million rows, and only 100 distinct dates, so is a prime candidate for a bitmap index. If I add 24 months to each of these 100 dates, the cardinality is still 100, so I'm not sure I follow your increase in cardinality to twice the amount.
Maybe my understanding is limited here, but I thought that bitmap indexes didn't have leafs ?
Orca, thanks for the reply
Not sure I understand your answer ...
"So dating up a row with a key-columns forces the to load complete blocks and to reorganize/recalculate ( Size ) them again. "
We're running 8.1.7, so surely we shouldn't be expecting a storage increase of 600% (if at all).