How to estimate sratchdisk space for sorting

jiegao · Post by **jiegao** » Tue Nov 08, 2011 11:20 am

I have to sort data with volume of 900 million records. The sort is on two keys with 26 bytes in total. The sort is not stable sort. If I caluclate 900 million multiple by 26, it should be about 23 G of data. But it consumes more than 200G of scratch disk space. What does the other data come from? Does it come from the buffer overflow? We do not specify buffer resource in the configure file. Thanks

PaulVL · Post by **PaulVL** » Tue Nov 08, 2011 3:20 pm

What is the total length of your row? Not just the keys.

ray.wurlod · Post by **ray.wurlod** » Tue Nov 08, 2011 3:44 pm

As Paul noted, the entire Data Set has to be sorted, not just the keys. Estimate not less than two times the entire volume of the Data Set as the requirement for scratch space when sorting. More is always better.

jiegao · Post by **jiegao** » Tue Nov 08, 2011 7:24 pm

PaulVL wrote:What is the total length of your row? Not just the keys.

The total length of the row is 50 bytes

jiegao · Post by **jiegao** » Tue Nov 08, 2011 7:31 pm

Hi Ray, it looks like ways more than double of the entire volume of the data set. It requries 4 or 5 times more than the entire dataset. I need to justify the space requested. Thanks

ray.wurlod · Post by **ray.wurlod** » Tue Nov 08, 2011 7:48 pm

Yes, I was being simplistic. For example are your VarChar data types bounded (have a maximum length) or unbounded? There are also other overheads associated with storage in a Data Set - approximately 80 bits per row last time I looked (version 7.x).

jiegao · Post by **jiegao** » Tue Nov 08, 2011 9:28 pm

Thanks. 50 Bytes is the max length of the row