Estimating Temporary Space for Index Builds

To estimate the amount of temporary space needed for an entire index build, perform the following steps:

1. Add up the total widths of the indexed columns or returned values from user-defined functions. This value is referred to as keysize.
2. Estimate the size of a typical item to sort with one of the following formulas, depending on whether the index is attached or not:
   • For a nonfragmented table or a fragmented table with an attached index, use the following formula:

     sizeof_sort_item = keysize + 5

   • For fragmented tables with the index explicitly fragmented, use the following formula:

     sizeof_sort_item = keysize + 9

3. Estimate the number of bytes needed to sort with the following formula:

   temp_bytes = 2 * (rows * sizeof_sort_item)

   This formula uses the factor 2 because everything is stored twice when intermediate sort runs use temporary space. Intermediate sort runs occur when not enough memory exists to perform the entire sort in memory.

   The value for rows is the total number of rows that you expect to be in the table.