What is the best way to calculate the tablespace manually.

Database
Enthusiast

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.
2 REPLIES
Enthusiast

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
;
Enthusiast

Re: What is the best way to calculate the tablespace manually.

But, I also need to know the size occupied by each column. How do i get that?