Teradata No More Room in Database Error and Table Skewness
No more room in database error
In Teradata, error 'no more room in database' can be common especially when data is not evenly distributed (i.e. high skewness). Sometimes when this error occurs, it might be just because of one or a few AMPs are full.
For example, assuming a database with permanent space allocated as 100GB and there are 100 AMPs. In ideal scenario, each AMP will store 1GB of database for the database. However, in real world, most likely there will be skewness. In the past, I also see scenarios people forgot to specify primary index when creating table and the first column was picked up as PI. If the values are largely same across the whole table, high skewness will occur and also database can run out of space soon.
Check table data distribution on AMPs
As mentioned in article Useful DBC (Data Base Computer) System Views in Teradata, DBC views can be used to check the data distribution of each table.
SELECT * FROM DBC.TableSizeV WHERE DatabaseName='TestDb' AND TableName='test_table';
The above code snippet returns the data distribution of table test_table in TestDb database.
The following is one example output:
Calculate skewness factor
The following query can be used to calculate table skewness:
SELECT TableName,SUM(CurrentPerm) AS CurrentPerm,SUM(PeakPerm) AS PeakPerm,(100 - (AVG(CurrentPerm)/NULLIFZERO(MAX(CurrentPerm))*100)) AS SkewFactor FROM Dbc.TableSizeV WHERE DatabaseName='TestDb' AND TableName='test_table' GROUP BY 1
TableName CurrentPerm PeakPerm SkewFactor 1 test_table 3,072 3,072 0.00
Resolve no more room error
There are two common approaches to address this error:
- Choose a better PI to reduce skewness factor.
- Allocate more permanent space from parent container if the data is already distributed well (i.e. no better PI candidate).