need some info on DB2 indexes

raja1128 · New User Joined: 09 Jan 2007 Posts: 17 Location: india

Hello Forum,

I am trying to understand DB2 indexes. I saw below paragraph in a document and couldnt understand the numbers.

""""Most DB2 professionals know intuitively that index-only processing is a good thing. An example supports that intuition. A million-row table with 100-byte rows occupies about 28,572 pages (ceiling(1,000,000 / floor(3870/(100 byte row + 8 bytes))). An index on a 10-byte column occupies
about 4695 (ceiling(1,000,000 / floor (3634 / (10 byte column + 4 byte RID + 3 bytes)) leaf pages. Obviously, processing all leaf pages is more efficient than processing all data pages. Even if only a few values are required, avoiding I/O to the data pages results in savings.""""

can some one kindly explain the numbers and theory mentioned above.

thank you!
Raja

dick scherrer · Posted: Sat Jul 30, 2011 7:58 pm

Hello,

Suggest you ignore the numbers - they are only an example. As we say, your milage may vary.

The theory (and it is not only theory) is that the less i/o done, the better a process will perform. If the SELECT can be satisfied by using only index entries (rather than data entries) the overhead of reading the "data" is avoided.

This is something we should be aware of but not spend too much time on. To add columns to some index(ex) to take advantage of index-only processing is most often the wrong thing to do.

GuyC · Posted: Mon Aug 01, 2011 1:24 pm

dick scherrer · Posted: Mon Aug 01, 2011 8:09 pm

Hi Guy,

As with many things - probably because some asked for it or someone in the ivory tower thought it was a good idea at the time. Compared to the high-performance databases (which have become fewer and fewer<g>, db2 can use all the help it can get. . . As many VPs and Dirfectors have said, they really didn't want to go to db2 but did because of the risk of their preference "going away".

As i mentioned, this can occasionally help, but very few situations should call it a "requirement".