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 the total widths of the indexed columns or returned values from user-defined functions. This value is referred to as colsize.
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:
   1. For a nonfragmented table and a fragmented table with an index created without an explicit fragmentation strategy, use the following formula:

      sizeof_sort_item = keysize + 4

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

      sizeof_sort_item = 

      keysize + 8

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.