Unable to extend index problems

[Horace]

Hi Guys,

I'm adding 24 months to a date column on a large table.
This column has a bitmap index on it.
I've not dropped the index, so as the column updates, so does the index.

Here's the problem..................

The index runs out of space with an error message:-
unable to extend index OMNPLUS_DEV.BDX_DATE_LAST_MOVED by 64
in tablespace OMNPLUS_INDEX

Why does it run out of space ? I've not added any rows, I'm just updating the current rows, so, theoretically, the index size will remain the same.

Why does it need to allocate a new extent ?

Thanks in advance.....

[pando]

update wouldnt increase your index size? Really? where did you get that theory from :-?

[Horace]

Well, if I've got 20 rows , and I'm adding 24 months to a date column, then the rowsize will not increase, and I'll still have 20 rows after the update.

The index will still reference 20 rows, so why does it need more space ?

[pando]

index stores the row/key value
I dont get you "adding" 24 months in an update :-?

[Horace]

Okay......

Here's a table with 1 date column, the data is......

20-Mar-2000
25-Mar-2000

I update this table to add 24 months to the date column, the data now looks like......

20-Mar-2002
25-Mar-2002

The index was

row 1 20-Mar-2000
row 2 25-Mar-2000

The index after the update should be

row 1 20-Mar-2002
row 2 25-Mar-2002

I can't make it any simpler that this...........

[pando]

the values are changed so if the leaf which contains index entries for 2002 has to split it has to take more space in order to split?

[Horace]

It's a bitmap index, not a b-tree

[ales]

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.

Ales

[Orca777]

Hello;
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 ;

Hope it helps.

Orca

[Horace]

ales, thanks for the reply

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).