Redistribution Spol Space

[Liam1]

Hello,

Does anyone know if there is a way to estimate the spoolspace required for a redistribution?
i.e. If I have a 1Gb table joining to another 1Gb table, and one needs redistributing, will I use 1Gb extra spoolspace to redistribute one of the tables. We have noticed we actually need more.

Basically is there a %age over-head on redistribution?

If so, is it affected whether you are doing an INSERT vs. a SELECT

Thanks,

Liam1

 

[NSAP]

I believe that spool size also depends number of rows distributed on AMPs. If there is more skewing, then the joining with other table wud require more space which wud increase the actual spool required. The spool requirement can be tried through explain which estimates the number of rows for different steps, which can be summed up. The exact formula is not known to me.

Thx

 

[bigscott]

Liam
The only way to "calculate" this is to understand what the join condition that is forcing a redistribution. For example, I join customer transactions to customer, redistribution is fine. But if I join customer transactions to customer region (North, West, South, East) then redistribution may be on 4 amps, causing a spool. If I do a subquery pulling region in with customer (denomalize first) and then join to the transaction table, I have a much better chance of not causing an out of spool error.