What is the best way to calculate the tablespace manually.
I know that we can get the space occupied by a table using the following sql :
SELECT sum(CurrentPerm) FROM DBC.TableSize WHERE DatabaseName = 'dbname' and tablename = 'tname';
What is the best way, if we have to calculate the table space manually?
I guess the following is how we calculate it :
TableSize = (RowSize + tdOverHead) * RowCount;
RowSize = sum of the sizes of all the columns in the table
I think there is an overhead for every teradata table row right. I believe it is 13 bytes. How do I effectively get the row size, since some columns in the table may take variable length. example : varchar.
Re: What is the best way to calculate the tablespace manually.
The following will calculate the average rowsize on the existing table. It will take EVERYTHING into account - row header, secondary indices, compression, varchar and unused space in the blocks.
Select Tsz.CPerm / TCnt.NumRows As AvgRow From (Select Sum(CurrentPerm) As CPerm From DBC.Tablesize WHERE DatabaseName = 'dbname' and tablename = 'tname') Tsz , (Select Count(*) As NumRows From dbname.tname) TCnt ;