Calculating the Size of an Unsorted Index

Determining the Size of the Database › Allocating Space for Indexes › Calculating the Size of the Index › Calculating the Size of an Unsorted Index

Calculating the Size of an Unsorted Index

Calculation	Formula/Instructions
Number of indexed entity occurrences	The requirements of your database will determine this value. You might want to use an inflated number to allow space for future growth.
Number of index levels	Unsorted indexes consist of only one level (level-0).
Number of entries per SR8	The number of SR8s should be three or more and less than the number of entity occurrences being indexed. Work out the formulas in the following steps with a number of your choice; bear in mind that you need to derive an SR8 that is less than 30 percent of the page size for the area. Recalculate the formulas as necessary until you reach the desired result.
Size of SR8s	Determine SR8 size (including line index space) by using the following formula: SR8-size = 40 + (#SR8-entries + 1) * 4 Round the value up to the next higher number divisible by 4.
Number of SR8s	Determine the number of SR8s that will be required for your index: Total-#SR8s = (#indexed-entity-occurrences + #SR8-entries - 1) ----------------------------------------------- #SR8-entries Note that the quotient is truncated, not rounded.
Number of bytes required	Calculate the total number of bytes of space you will need to accommodate the index: #Bytes-required-for-SR8s = #SR8s * SR8-size
Page size for the index area	Plan to store at least three SR8s on a page; use a page reserve of up to 29 percent of the page size. The page reserve factor actually increases the size of your database page so that additional SR8s can be accommodated without generating overflow. Use the following formulas to estimate page size: Page-size = (#SR8s-per-page) * (SR8-size) Total-page-size = page-size + page-reserve + page-header-footer-length The header-footer length is 32 bytes for an area. Compare the resulting page size with the result from the first table and select the next larger page size that's compatible with your DASD device: If the page size determined in this way is too large, the number of index levels will have to be increased until a satisfactory compromise between page size and number of index levels is reached. If the page size determined is much smaller than 4K, use a 4K page size instead; this allows more than three SR8s to be stored on each page.
Number of pages needed for the index	Determine the total number of pages needed for the index: Total-#pages-needed = (#SR8s + #SR8s-per-page - 1) ---------------------------- #SR8s-per-page Note that the quotient should be truncated, not rounded.

Sample index size calculation

The following diagram shows how space is allocated for storage of an index.

The SKILL-NAME index requires 18 database pages.

For a detailed explanation of the formula used to calculate space requirements for this index, see the previous table.

            # OF SKILL OCCURRENCES                    1680
            KEY LENGTH                                  12
            # OF INDEX LEVELS                            3
            # OF ENTRIES PER SR8                        12
            SIZE OF SR8                                300
            # OF SR8s                                  153
            # OF BYTES REQUIRED FOR INDEX            45900
            # OF SR8 DISPLACEMENT PAGES                  3

            TOTAL # OF PAGES IN SKILL-NAME-REGION AREA  18


     # SR8 ENTRIES = Cube-root-of-#skill-occurrences = 12 (rounded up)
 
     SR8 SIZE = 40 + (13 * 20) = 300 bytes

     LVL-0  =  (1680 + 11) / 12  = 140 (truncated)
 
     LVL-1  =  (140 + 11) / 12   =  12 (truncated)
 
     LVL-2  =  (12 + 11) / 12    =   1 (truncated)

(In this 3-level index, there are 140 displaced SR8s and 13 non-displaced SR8s; the total number of SR8s is 153.)

     # OF BYTES REQUIRED FOR INDEX =   153 * 300 = 45900

     SPACE REQUIRED FOR STORING 3 SR8s = 3 * 300

     PAGE-SIZE (INCLUDING PAGE RESERVE) = 900/.70  + 32  = 1318

(1318 bytes for page size is very small; therefore a 4K page size might be used instead. If a 4K page size is selected, the DBMS will be able to store approximately 10 SR8s on a page.)

     # OF SR8 DISPLACEMENT PAGES = (15 + 10 - 1) / 10 + 1 = 3

     TOTAL # OF PAGES IN AREA    = (140 + 10 - 1) / 10 + 3 = 18