Unable to extend index problems

[julian]

Originally posted by Horace
The table has 5 million rows, and only 100 distinct dates, so is a prime candidate for a bitmap index.

IIf you do that, collect histograms for that column.

[ales]

Originally posted by Horace
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.

Yes, the final cardinality should be the same.
But in time of processing the update, row after row is updated. For the new key value, new bitmap is created and the bitmap with the old key value cannot be deleted until there are no rows with that key value.
The bitmap with unused key values is probably deleted but I have no clue about extent management for bitmap indexes.

I thought that bitmap indexes didn't have leafs ?

Bitmap indexes are organized as a B-tree indexes, with leaves, but in a leaf is compressed bitmap of ROWIDs instead of list of ROWID's, thus a bitmap index needs less space.

BTW, I think cardinality 100 is high for bitmap indexes, but have no experience with that. For our OLTP/DSS databases we use only B-tree indexes with good performance.

Ales

[Horace]

For the new key value, new bitmap is created and the bitmap with the old key value cannot be deleted until there are no rows with that key value.

Cheers Ales,

We had just come to the same conclusion here, not sure if the old value is deleted or kept though.

I think cardinality 100 is high for bitmap indexes, but have no experience with that.

Oracle recommends that bitmap indexes should be used where cardinality is less than 1% of the total number of rows in the table, so in this instance, we would improve performance by building bitmap indexes on columns with a cardinality less than (5,000,000/100) = 50,000.

In general, we have found that there are performance benefits where cardinality is less than 10%.

Julian - thanks for the comment, we already do histographical analysis on the columns.

Thanks All for the input.