Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2011
    Posts
    29

    Unanswered: DB2 V9 Unix (AIX) Index Maintenance ???

    I'm having a bit of an issue understanding why Indexes would need to be reorg'd?

    From what I understand, in index will always be in order regardless of how many insert, updates and deletes you perform on the underlying table.

    For example.

    Table A
    (Cust Id int) <--Indexed

    Insert: 1 5 3 7 2 4

    The index will be in order of: 1 2 3 4 5 7

    If I happen to insert into Table A, Cust Id 6 and delete 3, the index will automatically be in the correct order: 1 2 4 5 6 7

    Why would there be any need to perform any maintenance/reorg on the index itself?

    Thank you in advanced.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The needs of reorg/maitenance are mainly to recover/pevent performance degradation and spaces in pages caused by index page splitting.

    Here is a typical scenario of index page splitting
    which I described in the thread http://www.dbforums.com/db2/1666646-...ert-query.html
    Once index splitting occured, it is highly possible that index splitting would continue.

    Consider a case with PCTFREE 0.
    If roandom keys were inserted, most keys would be inserted into different leaf pages.
    So, almost every inserts would cause index splitting.
    Then after enough keys inserted, almost every index pages would be splittied,
    The splitted pages have enough spaces for new keys to be inserted.
    Later inserts wouldn't cause index splitting.

    But, if more keys were inserted, splittied pages would be gradualy filled up.
    Then repeat again first scenario.

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    In addition to what Tonkuma said, there are other things that reorganization of indexes fixes, e.g. the removal of pseudo-deleted keys, compression, etc. Please take a look: Index reorganization - IBM DB2 9.7 for Linux, UNIX, and Windows

  4. #4
    Join Date
    Jul 2011
    Posts
    29
    Thank you both for your responses.

    Am I correct however, when I say that the values in an index will always be in order regardless of the number of inserts/updates/deletes?

    If I understand you correctly, the only reason to run a reorg against indexes is when index page splitting occurs? Meaning, index pages are not being 100% utilized in terms of available page space?

    Can anyone provide further links in regards to how inserts/update/deletes affect indexes?

    Thanks again,

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by sanchez786 View Post

    Am I correct however, when I say that the values in an index will always be in order regardless of the number of inserts/updates/deletes?
    Not exactly. Values _on an index page_ will be in order, however, pages themselves may not be.

    Quote Originally Posted by sanchez786 View Post
    If I understand you correctly, the only reason to run a reorg against indexes is when index page splitting occurs?
    I think it's exactly the opposite of what I said. Did you have a chance to look at the link?

  6. #6
    Join Date
    Jul 2011
    Posts
    29
    Quote Originally Posted by n_i View Post
    Not exactly. Values _on an index page_ will be in order, however, pages themselves may not be.



    I think it's exactly the opposite of what I said. Did you have a chance to look at the link?
    Yes, I did read the link.

    "The physical index page order no longer matches the sequence of keys on those pages, resulting in a badly clustered index. When leaf pages are badly clustered, sequential prefetching is inefficient and the number of I/O waits increases."

    Am I to interpret this as although the keys will always be maintained in desc order in the index page, the index pages (leaf pages) themselves may be not in order (as in not side by side)???

    I'm still unsure as to how an update/insert/delete allows for the index pages not be in order? Also, does index splitting affect the index clusterratio?

    From what I understand, it does not as the clusterratio has strictly do with the relationship between the index and table. If the rows for a table within a tablespace page are highly fragmented then clusterratio is low where as if the rows are in order closely matching the keys in an index, then the clusterratio is high.

    So again, index splitting has nothing with the index clusterratio?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Index leaf pages may not be in key sequence physically, but they are linked with key sequence.
    So, DB2 can scan leaf pages by key sequence without seeing higher level pages.

  8. #8
    Join Date
    Jul 2011
    Posts
    29
    Quote Originally Posted by tonkuma View Post
    Index leaf pages may not be in key sequence physically, but they are linked with key sequence.
    So, DB2 can scan leaf pages by key sequence without seeing higher level pages.
    What you mean to say is, and index leaf page may have keys not in order?
    I was under the assumption (everywhere I've read) that keys on individual leaf pages will always be in sequence (order).

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I wrote
    Index leaf pages may not be in key sequence physically, ...
    I didn't mentioned inside a leaf page.

    I'm sorry, if my poor English caused your misunderstanding.

    By the way,
    I want to ask about very basic of English grammar.(I have no grammar book in my hand now)
    Which is right "... may not be ..." or "... may be not ..."?
    Last edited by tonkuma; 07-12-11 at 15:53.

  10. #10
    Join Date
    Jul 2011
    Posts
    29
    Quote Originally Posted by tonkuma View Post
    I wrote

    I didn't mentioned inside a leaf page.

    I'm sorry, if my poor English caused your misunderstanding.

    By the way,
    I want to ask about very basic of English grammar.(I have no grammar book in my hand now)
    Which is right "... may not be ..." or "... may be not ..."?
    You were correct the first time "may not be".

    Do you have any other examples/web links that can give me more information on how indexes are affected by Inserts/Updates/Deletes?

    Thanks,

Posting Permissions

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