"Erik Hendrix" <hendrix_erikhotmail.com> wrote in message
news:a5b008e6d7232371ad5a306c3c381ce5free.teranew s.com...many> Small question, if my runstats are up to date, can I then determine howindex> pages a certain index is using by looking at the NLEAF value for thatwhat> in syscat.indexes?
> This is on UDB 7.2 if it makes a difference.
> If this is not the method on how to determine how many pages are used,NLEAF will be an approximate number of pages. But there are also non-leaf> is?
pages that are used to point to the leaf pages. This is how DB2 uses the
B-tree to find the exact index entry with the least number of I/O's.
In a three level index (probably most moderate to large tables have a 3
level index), there is the root page on the first level plus multiple
non-leaf pages on the second level (the leaf pages are on the 3rd level).
The total number of non-leaf pages obviously depends on the size of the
index, but I would estimate that there are usually about 10 or less non-leaf
pages on a an average 3 level index.
If the number of index levels (NLEVELS) is 4, the number of non-leaf pages
is probably 15 or more. If the number of levels is 2, there is only 1
non-leaf page (the root page).