I am new to teradata and not very clear of how the backend of teradata works. I am really appreciate if you can help me with me question.
I was trying to generate a multiset table in my database, which is of 100G size, however I got an error 2644 saying that no more room in my database. It's strange since my database has 100G empty space.
Then I changed multiset table to a multiset volatile table, the same query executes fast and no problem occured.
I don't understand what's happening here, why no space for a multiset table, but once I changed to volatile table it works?
Thanks a lot for your help!
Volatile Table uses spool space and not the PERM space thats why its not giving the problem.
I suspect about your PERM space availibility. Please recheck. Also there is possibility of highly skewed data across the amps .
So, please check for skewness.
Thanks a lot for your answer, actually the skewness is also the problem of my concern. The skewness is high for this table, but I don't know what to make it smaller. Could you provide some suggestions?
I am also wondering whether the low speed of execution of the query is resulted from the high skewness.
Thanks a lot!
You Can check skewness by below query to see which AMP is getting how many records :
SEL HASHAMP(HASHBUCKET(HASHROW(<PI COLUMN>) AS AMP_No,COUNT(*) AS Number_of_Records
GROUP BY 1;
Choose PI columns wisely. If you want even distribution of data, please use above query to check the distribution of data and accordingly you can choose your PI.
no doubt the long time is directly caused by a high skew,
an odd distribution among AMPs is synonym of poor performance and inserts may feed up an AMP when others stay unused.
how many AMPs on your system ?