Unable to extend index problems

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

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

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 ?

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

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

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?

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

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

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

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

Quote:

---

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.

Quote:

---

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.

Quote:

---

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

Quote:

---

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.

Quote:

---

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.