Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

need some info on DB2 indexes

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
raja1128

New User


Joined: 09 Jan 2007
Posts: 17
Location: india

PostPosted: Sat Jul 30, 2011 6:25 pm    Post subject: need some info on DB2 indexes
Reply with quote

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
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Jul 30, 2011 7:58 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Aug 01, 2011 1:24 pm    Post subject:
Reply with quote

dick scherrer wrote:
To add columns to some index(ex) to take advantage of index-only processing is most often the wrong thing to do.
Then why is IBM adding INCLUDE columns in DB2 V10?
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Mon Aug 01, 2011 8:09 pm    Post subject:
Reply with quote

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".
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need info with DBRC recovery ashek15 IMS DB/DC 0 Mon Jan 23, 2017 6:43 am
No new posts IMS Database backup info ashek15 IMS DB/DC 14 Wed Nov 16, 2016 5:29 am
No new posts Utility to extract dsn info from conc... Lynne Schuler PL/I & Assembler 11 Tue Jan 26, 2016 6:36 am
No new posts print out the correct info in LOOP? jackzhang75 CLIST & REXX 7 Wed Dec 23, 2015 10:39 pm
No new posts Need info on SELECT statement subratarec DB2 7 Tue Jun 23, 2015 11:02 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us