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.

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 Confusingly slow simple insert query

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

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,

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?

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

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,