Example of sizing calculations for a SYSTEM-OWNED Index sorted on Symbolic key
Article ID: 56724
Updated On:
Products
Issue/Introduction
What are some example of sizing calculations for a SYSTEM-OWNED Index sorted on Symbolic key?
Resolution
Using the information in the Database Design Guide on "Allocating Space for Indexes", here is an example of sizing for a hypothetical System-Owned index that will at some point have up to 5,000,000 members and where the sortkey is a 16 byte field containing an ID or Serial number type of field.
The guidelines for sizing indexes presume the System-owned index will be in its own Area or its own isolated page range within an area. In order for the guidelines to work effectively to create the most efficient index structure, the index cannot share a page range with other indexes or other record types.
Realize also that many experienced IDMS indexing people have different opinions on what works best for performance.
The example here is based on the documented guidelines.
Typically we recommend a 3-level index if the projected number of members is under 500,000 and 4-level index if over.
If you can keep the Index Block Contains (IBC) under 50 you should. If a single SR8 contains a large number of entries it could conceivably cause more contention.
For this index we will choose IBC 49 which can handle up to 5,764,801 members in a 4-level index.
Next calculate the amount of space needed to store one full sized SR8.
SR8 size = (Keylength+8) * (IBC+1) + 32
(24 * 50) + 32 = 1232
We will also need 8 bytes for the Line index on the page so we need 1240 bytes to store one full size SR8.
We want to get 4 (or more, but not too many more) SR8s per page so we will choose Area Page size 5064. This will get us 4 SR8s per page with very little wasted space.
NOTE: Level 0 SR8s are variable in length based on the number of entries as well as any reduction in size related to compression of sortkeys or truncation of trailing blanks or zeros.
Upper level SR8s are always maintained at full size.
When checking to see if a new SR8 will fit on a page we always look for a page that will fit a full size SR8 even if storing a level-0 SR8. If storing a level-0 SR8 and no page in the index's page range can fit a full size SR8 we will look for space to store an SR8 that is only as large as needed.
Next determine the number of pages needed and DISPLACEMENT value.
The purpose of DISPLACEMENT is to specify the number of pages needed for the upper level SR8s which comprise the "tree" structure that is searched for a specific member key or to find the position for a new member key.
The DISPLACEMENT clause causes new level-0 SR8s to be targeted beginning that number of pages away from the owner.
This allows the searchable structure to be clustered as best as possible.
For 5,000,000 members with IBC 49 there will be
102,041 level-0 SR8s
2,083 level-1 SR8s
43 Level-2 SR8s
1 Level-3 SR8
Total number of SR8s = 104168
at 4 SR8s per page we will need 26,042 pages (always round up a bit so 26,100)
DISPLACEMENT Pages 532 will hold 2127 upper level SR8s (again round up)
DISPLACEMENT 550 pages.
There will be a separate article on techniques for providing for growth in an index so as to prevent splitting/fragmentation for some amount of time after Build or Rebuild.
Additional Information
Feedback
