|select TableName, cast(sum(CurrentPerm)/(1024*1024*1024) as decimal(18,2)) as ActualSpace,|
|count(*)*cast((max(CurrentPerm)-avg(CurrentPerm))/(1024*1024*1024) as decimal(18,2)) as WastedSpace|
|from dbc.TableSize where DatabaseName='Database_name'|
|group by TableName order by WastedSpace desc;|
I used the above query and found that the wasted space is was around 1 TB for a Table.
Please let me know how can I use the wasted space.
The more of wastedSpace over actualSpace is an indication of the table's skew. For the tables with more wasted space, identify primaryindex,count(*) and you will see that the PI repeated values has a huge difference. Try changing the PI of those tables to make the rows evenly distributed across all the amps and you will see that the wasted space will reduce.
The above query is not accurate for a group of tables. It assumes that the skew for each table is on the same AMP which is often/typically not true.
Teradata does not allocate equal space to the table on each AMP, only the space that it needs on that AMP. So if table A is higher utilization on AMP 1 and Table B is higher utilization on AMP 2, they balance/cancel each other out.
No space is actually "wasted" on Teradata until such time as one AMP fills to its total allocation. If a database has one AMP filled to (Permspace/#AMPs) and other AMPs are not filled to that point, then that perspace quota on those other AMPs is not usable in that database without increasing total perm for the database. However the perm space not used on those AMPs can be used for spool or permanent storage for other databases/tables.
If one AMP is totally filled and other AMPs are not then the space on the other AMPs would not be usable and only in this case would that space be "wasted". Of course if one AMP is totally full, then you have other issues to worry about...