Index Calculations › INDEX BLOCK CONTAINS

INDEX BLOCK CONTAINS

The following steps are used to calculate key-count for the INDEX BLOCK CONTAINS parameter of the CREATE INDEX statement:

1. Obtain the maximum number of index entries in an SR8 (this formula assumes 3 SR8s per page):
   • Compute the maximum size of the variable portion of an SR8:

     ( (Page-size - Page-reserve - 32) / 3 ) - 40 = SR8-variable-size
     

   • Compute the maximum number of index entries in an SR8:

     (SR8-variable-size / ( 8 + Key-length) ) - 2
     

   If the resulting number of SR8 entries is less than 3, set it to 3; if greater than 8180, set it to 8180.

2. Establish the number of rows to be indexed using the greater of;
   • The estimated number of rows for the table (ESTROWS column in SYSTEM.TABLE)
   • The actual number of rows in the table (NUMROWS column in SYSTEM.TABLE)

   If both values are 0, use 1000.

3. Estimate the number of entries per SR8 for a 3-level index by finding the first entry in the following table whose Number of Rows column is greater than or equal to the value established in Step 2:

   Number of Rows       Number of SR8 Entries
   
            1,000                          10
           15,625                          25
          125,000                          50
          512,000                          80
        1,000,000                         100
        2,000,376                         126
        3,375,000                         150
        5,359,375                         175
        8,000,000                         200
       15,625,000                         250
         -1                              8180
   

4. Determine the INDEX BLOCK CONTAINS value by using the lesser of the Number of SR8 Entries from the table and the value obtained in Step 1.

   Use this value in the INDEX BLOCK CONTAINS key-count parameter of the definition and for IBC-key-count in calculations for DISPLACEMENT page-count.