I was wondering if you could help me with a question that I'm researching. I have a table that utilizes multi-value compression and it is taking up about 100 units of Current Perm space (notionally). The table has a skew factor <1%. I have a spool space limitation on my user ID of 200 units, but when I try to select the table (select * from table ... no where clause), Teradata is throwing the 2646 error.
Now of course, I am going to petition for more spool space from the DBA. However, I am curious as to why this is happening? I am guessing it is because the data are being uncompressed in the spool and that the aggregate size of the uncompressed data exceeds my spool limit?
Thanks in advance
When you do a select, the result is formatted in it's final retrieval form in the final spool file. This means that dates, numbers, timestamps,... are all translated into character formatting, character strings are formatted into the requested fixed length, MVC and other compression is uncompressed,... The final result in spool will be significantly larger in general than the stored form because it has to be in external form.
The result spool is different than the intermediate spools where compression is preserved if appropriate and data is kept in the internal storage form.
A SELECT * is not a very good test of spooling size unless you are really exporting the whole table as part of your process.