When i am running a below query , i got the famous error " No More Spool Space".
Sel * from <Source table>
My Source table has a Nice unique PI. But my target table has a cloumn which would have only 2 different values as the PI. I am inserting one billion rows from my source into target table. I understand its skewed distribution. After correcting my PI i could insert the rows into the table. But my question here is that I expected an error " No more room in database" but instead i got no more spool space errror.
My understading on the Spool space is that it holds the intermediate resultset.As slect * is on the source table and it is evenly distributed, Why am i getting no more spool space error.
Thanks in advance for your comments and help
Is this simple select * you are using or there are some joins in the select statement?
The second thing is select insert is most optimal when both the source and target table have the same PI.
Also can you please tell that whether you are getting this error on simple select, or on INSERT SELECT?
Do you have proper spool space to handle 1 billion rows?
when you read the explain you will see there's a RETRIEVE step including "redistributed by hash code of PI":
The spool will have the target's primary index, i.e. all rows on tow AMPs and thus runs out of spool.
I dont know if its a good practice to deliberately address the skew problem by using as many columns as possible (to address skewing ) even if the query's wont use these columns. I understand that it'll redistribute but what if the architecture's really bad that column values arent that unique enough (ie hash codes) to be a PI candidate. What would you do in this situation?
I want to answer my question based from what I've experienced. It's not good to deliberately change the PI just to address the problem. My ImpactCPU went higher than expected. No Fun.