Kbase P106996: How much space needed on CREATE INDEX, for a none empty SQL92 table
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
GOAL:
How much space needed on CREATE INDEX, for a none empty SQL92 table
GOAL:
What value for -SQLTempDisk when running a CREATE INDEX on a populated table
FIX:
To create the index for the SQL92 table, the data values for all the index key components are dumped to a temp table in an external format with the dbkey added, the temp table is sorted, and the index is built from the sorted data.
The size of the Temp Table can be calculated with the following formulae:
width_column = SQL width for a column (sysprogress.syscolumns.width)
width_key = width_column for non numeric column, 2*width_column for numeric column
W = sum of width_key for all keys in the index
Record_Size= W + (Number_of_Keys_in_Index * 8) + 24
TempTable_Size= (Number_of_Records_in_SQL92_Table) * Record_Size
The total of space needed for sorting would be a multiple of TempTable_size. The factor will usually be 3, but it can vary up to 9.
So the "-SQLTempDisk" parameter value would be between TempTable_Size * 3 and TempTable_Size * 9