Unable to extend index problems
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Unable to extend index problems

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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.....


  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    update wouldnt increase your index size? Really? where did you get that theory from :-?

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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 ?

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    index stores the row/key value
    I dont get you "adding" 24 months in an update :-?

  5. #5
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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...........

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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?

  7. #7
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    It's a bitmap index, not a b-tree

  8. #8
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    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

  9. #9
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    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

  10. #10
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    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).


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width