Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    NJ, USA
    Posts
    1

    Question Unanswered: Can I have updatable column as part of Index

    Hi My DBA suggests that I can have a column I update as a part of Index. I do not agree with this. I select one million rows from the table update column for each row I retieved. Can I have this column as a part of Index? My DBA says it is OK. But I do not agree with this. Can you please suggest what can I do!!

    Thanks
    Marredpally

  2. #2
    Join Date
    Sep 2002
    Posts
    456

    Re: Can I have updatable column as part of Index

    A column which is part of the index is updatable. There is no such restriction that it has to be read only or anything like that. Your DBA seems to be right.

    dollar

    Originally posted by marredpally
    Hi My DBA suggests that I can have a column I update as a part of Index. I do not agree with this. I select one million rows from the table update column for each row I retieved. Can I have this column as a part of Index? My DBA says it is OK. But I do not agree with this. Can you please suggest what can I do!!

    Thanks
    Marredpally

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you are updating an index column on 1 million rows, that is perfectly legal, but there may be performance implications.

    For each update, the existing index entry would be removed and a new one created at a different location in the index.

    There may also be an issue regarding clustering of the table, but I am unsure if it applies to updates (but does apply to inserts). If the index being updated was defined as the clustering index, then DB2 "might" move the row in the table to the new location to maintain the clustering sequence. But as I said, I am not sure if clustering applies to updates like it does apply to inserts.

    In any case, you might want to define a large amount of freespace for the index and/or table to handle the large update, and perform a reorg after the update is finished.

Posting Permissions

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